SQL Server and Docker

Learn how to use SQL Server to access the databases in your Docker container

This post is for you if you want to be able to access the SQL Server database inside your Docker container, without having to write the query.

For one of my projects, I needed to be able to see the apps that were uninstalled but still had their schema in the tenant database. That information is in the $ndo$navappuninstalledapp table, and with SQL Server Management Studio (SSMS) it is super easy to look at table data. In my container I assumed that I would have to figure out a way to write the actual query (something I am not very good at). As it turns out, I was wrong. In this post I will explain two very easy ways to access the SQL Server database inside your Docker container.

SQL Server Management Studio

The first, most obvious, option is to do a complete install of SQL Server in whatever edition you have access to. If you want to keep things lean though, you can also install a standalone SSMS. You can download SQL Server Management Studio here.

Connecting to a Docker container could not be easier. In the connection dialog, simply enter the name of your container as the server name, and SSMS will connect to it for you. I always use NavUserPassword authentication in my containers, and by default your container password will also work as the sa password inside the container.

My container is called ‘densterdev’, and you can see the default app and tenant databases inside the container.

All I needed to do was look at some table data, and that works just fine. I did not try to do anything more advanced than that.

SQL Server Extension in VSCode

The second option is even easier than installing SSMS, because you are already using VSCode to do your AL development. Microsoft has created a ‘SQL Server’ extension, which works very similar to SSMS. In the extensions search box in VSCode, type ‘SQL’ and select the one made by Microsoft.

After installing you may need to reload VSCode to enable the extension. You will see a new tab on the left navigation pane that will show you the tooltip ‘SQL Server’ when you hover over it. When you click this tab, you will see a heading that says ‘Connections’ at the top, with a + sign next to it. Click this + and follow the prompts. Just like SSMS, you enter the container name as the server name, and it should connect to it with no problem.

The same app and tenant databases are shown inside VSCode

I still did not need to do anything more complex than looking at some data, so I really can’t say what features are available beyond that. My guess is that it is less capable than SSMS, so this may not be an option if you need more advanced capabilities.

SQL Setup Essentials for NAV

The way that SQL Server is set up can greatly affect the performance of your NAV database. People often ask me about how to properly set up SQL Server for NAV databases, and it is a common topic in the online forums. I’ve posted a clip on YouTube in which I cover the essential elements of how to properly set up SQL Server for NAV.

First published April 18, 2012

Performance on NAV 2013

The first performance impressions are in and it looks like NAV 2013 is the first version on SQL Server that is going to outperform the native database server.

When talking about whether to upgrade to NAV 2013, one of the things that my customers are interested in most is whether it will perform at acceptable levels. I’ve been wanting to do something elaborate and use the new testability capabilities in NAV 2013 to do a benchmark test for performance specifically. As often happens, plans don’t always come together, and by the time I started forming some ideas, other people have gone and done a much better job than I could have. So, what I thought I’d do is compile a number of links to performance related content that I’ve found so far.

First off, my fellow MVP Jörg Stryk wrote a very technical article about his impressions of NAV 2013 from a SQL Server point of view. He shares his sources, he’s done some comparisons between his own T-SQL and the queries that are generated by NAV 2013. Very interesting to read his train of thought, and, if you can keep up with him, you will learn a thing or two about SQL Server. Some of the key things that I picked up on are the modified locking mechanism, the promise of the query object, and the annoying fact that individual users cannot be identified from the SQL Profiler anymore.

Next, my coworker Faithie writes about some changes that were made to how Dimensions are handled in NAV 2013. This one has a huge potential to help improve performance. I work with customers that have millions of ledger entries, with hundreds of millions of ledger entry dimensions, and the prospect of eliminating pretty much all of those is a very compelling one.

Next up, another fellow MVP, Vjekoslav Babić, who lists his top 5 SQL Server improvements. With NAV 2013 being the first version on SQL Server only, we can finally start letting go of certain “SQL improvements” from previous versions, which were really just things to make NAV on SQL behave the same way as the native database server. If I were to list what I take away from this article, I would list all 5, so I’ll spare you and just refer you to the article itself.

Where it gets interesting is when mergetool.com‘s Per Mogensen puts together a couple of videos (here and here) to demonstrate how NAV 2013 stacks up against NAV 2009. These videos then inspired Vjeko to take it a couple of steps further, and put together his own test, and he even shares his test objects. This is turning into a real community effort!

With NAV 2013, it looks at though the potential is definitely there to get performance up to native database server expectations, which was always the argument against using SQL Server. What I am very much excited about is that finally the NAV team can let go of artificially making NAV on SQL Server behave the same way as the native database server and let SQL Server do what it is good at. I personally think that they have only just scratched the surface, and a ton of further improvements are possible. I think the query object will prove one of the most potent weapons against performance problems, and, hopefully, we’ll see a tighter integration of SQL Server capabilities from C/SIDE.

First published June 25, 2012

Essential SQL Maintenance for NAV

Lack of proper maintenance on SQL Server can lead to severe performance problems. When I am called in to troubleshoot a client’s performance issues, one of the first things that I look at is whether there are any maintenance tasks on their SQL Server for the NAV database. Many times I find that there is no maintenance at all. Putting the right maintenance in place is the easiest thing you can do to help fight performance issues. In this YouTube clip I cover the essential maintenance that should be present for every single NAV database on SQL Server.

Sorry for the bad audio, I didn’t realize that it was set to my computer’s mic instead of my headset.

First published April 18, 2012

NAV on SQL – SQL Backup and Restore

Creating a NAV database on SQL Server is very easy to do when you have a SQL Server backup, and it is much faster then restoring a NAV backup. In this YouTube clip I show you how to use SQL Server Management Studio to create a full database backup, and how to create a new NAV database with that backup file.

First published April 22, 2012

NAV on SQL – NAV Backup and Restore

It is very easy to create a new NAV database on SQL Server, using the proprietary NAV backup Mechanism. While it is preferred to use SQL Server to back up and restore your databases, the NAV backup can also be used. n additional benefit of the NAV backup is that you can back up individual companies. I’ve created a YouTube clip that shows you how to create a NAV backup and how to use that backup to create a new NAV database on SQL Server.

First published April 22, 2012