In this post I want to cover some applications to install locally to manage SQL Server databases in Azure.
Aim of this post is to make everybody aware of various applications that are available to manage SQL Server databases in Azure.
This post is the follow up from the last post where I discussed starting with Azure. You can read that post in detail here.
I know a lot of people dive straight into various in-depth posts. However, I wanted to do this post and the previous post to make sure people got a good starting point.
This post contains my own recommendations to help people know what options there are. From experience I know it’s important to know what applications you can use locally with Azure to manage SQL Server solutions. So you have the right tools for the job.
For instance, I was talking with some people at a client’s site the other day about deciding what application to use to future proof themselves.
In this post I will cover applications for use with Windows, MacOS and Linux distributions. Below is a list of what I will cover if you wish to jump straight to one.
- SQL Server Management Studio (SSMS)
- Azure Data Studio
- Visual Studio (versions)
- SSDT
- Version Control client
- Database Migration Assistant
- Command Line tools
- Docker
SQL Server Management Studio (SSMS)
First I will start with the application which is probably the most familiar. SSMS was first introduced with SQL Server 2005 and can be installed on most Windows Operating Systems.
It can used to connect to SQL Server databases on-site or in Azure Virtual machine’s. In addition, it can also be used to connect to databases in most SQL Server related PaaS offerings. Something I talked about in a previous post here.
I do recommend using the latest version that you can install. This will allow you to connect using the newer authentication methods like multi-factor authentication.
You can download the latest version of SQL Server Management Studio and find out more about it in detail here.
Azure Data Studio
SSMS is certainly well established on Windows Operating systems. However, there is an alternative. Azure Data Studio can be installed on MacOS, Linux or various Linux distributions.
For example, I installed it on CentOS 7 which has the same binaries on Red Hat Linux.
However, it’s still a maturing product so it still has some issues. For example, some of the functionality on Linux is still not working as it should yet. They are intending to resolve issues like this so provide feedback to help.
Just to prove how resilient this tool really is, I’ve managed to get it to work on Windows Server 2019 core. It’s fine to open it from the command line if as long as you don’t try to open or save a file once it has started.
You can find more information about it in detail here.
Visual Studio (versions)
Now Visual Studio is probably the most essential tool you will require if you are looking to do more complex things in Azure. You can use it to manage SQL Server databases as well as doing more advanced tasks. For example, creating ARM templates.
Furthermore , you can use it to work with other Data Platform services. For example, the increasing popular CosmosDB service.
Now there are three main versions of Visual Studio people tend to use. I think it’s important to discuss these here because it mostly depends on your budget and what functionality you require.
You can download all three of the below versions and other related downloads by following the link here.
Professional
Visual Studio Professional is truly the most established version of Visual Studio. It has been around for some time now and is the most popular version to purchase.
Because Microsoft develop both this and Azure, functionality for Azure is heavily integrated into this product. In fact, I used it a lot when I was studying for my MCSD Azure Architect certification.
This version is available on both Windows Operating Systems and MacOS.
Community Edition
Visual Studio Community Edition is the free edition of Visual Studio Professional that you can install. It’s got a lot of the same features that Visual Studio Professional contains.
However, you can only use it if you meet certain guidelines. For example, if your company earns less then one million US dollars profit. You can read these guidelines in detail here.
Code
Visual Studio Code is the most recent offering from Microsoft and is becoming increasingly popular. It’s a multi-platform and open-source version that has being developed by Microsoft.
It can be installed on Windows, MacOS and Linux. In fact, I’ve installed it myself on both Windows and the CentOS Linux distribution.
You install it and then can add whatever functionality you desire. For instance, you can add SQL support by installing an add-on. These add-ons are also called a snippet.
You can find out more about Visual Studio Code in detail here.
SQL Server Data Tools (SSDT)
Now if you are looking to implement a form of DevOps for SQL Server databases in Azure then you will probably want to install SSDT. Within this you can then create Database projects.
Afterwards you can look to use ‘package.exe’ that comes with SSDT to implement changes. You can also use it to migrate your on-site databases into Azure SQl databases.
In addition, you can install SSDT as part of Visual Studio which I talked about previously in this post. However, you can also install it without Visual Studio already installed. Doing this will install SSDT with a minimal version of Visual Studio.
Like SSMS this application is available on Windows only. You can review your install options for SSDT in detail here.
Version Control client
Another thing you will want to look at if you wish to implement DevOps for your databases is a Version Control system. I won’t delve too much into this right now but the most popular one to use now is called GIT.
Usually you install a version of the application that works with the Version Control system locally. Afterwards, you use application to manage a local copy of whatever code you are working with.
You then make your changes on the local copy. Once you are happy with it you can then upload the updated version to a central location. This is also known as a repository.
You can use this application with Visual Studio, which I discussed earlier and have done myself.
You can install a version of the GIT client application developed by various sources on either Windows, MacOS or Linux. One of the more popular versions can be downloaded here.
Database Migration Assistant
If you are looking to migrate your on-site databases to an Azure Platform as a Service solution you will want to use this application.
For instance, you can use it to check if your databases are ready to migrate to Azure SQL Database or Azure SQL Database Managed Instance OK. If they are not it will tell you what the issues are with the database.
To be honest, you should be using this anyway if you are migrating databases from older versions of SQL Server to newer versions. I know I have, and it has helped me a lot.
You can download Database Migration Assistant and find out more about it in detail here.
Command Line tools
Now I do recommend using at least one command line tool when using Azure. In fact, in certain situations you will certainly need to use one. Now you have two main choices of what to install. These are as follows.
Powershell/Powershell core
Firstly, Powershell which comes with most modern Windows Operating Systems. It can be used to manage a lot of Data Platform related tasks in Azure. For example, I’ve used it a lot to deploy Azure SQL Databases.
However, Powershell core is a multi-platform and works also on MacOS and Linux. Note, it can also be thought of as Powershell 6. If you want to do more complex tasks in Azure it is useful to install it locally.
I did talk in detail about some of the more useful Powershell modules in a previous post here.
Azure CLI
Secondly, the command line tool which has being developed by Microsoft for use with Azure. It does a lot of the tasks in Azure and has a popular following.
You can download it and find out more about it in detail in the following guide here.
Docker
Finally, I will give Docker an honourable mention in this post.
Although you don’t use it to manage Data Platforms directly, containers are really popular at the moment. Especially with the recent announcements relating to SQL Server 2019, which I discussed in a previous post here.
So, with this in mind you might want to install Docker locally and experiment with SQL Server containers.
Docker can be installed on Windows, MacOS and Linux. You can follow the link to install Docker and find out more about it in detail here.
Final word
Well there’s my recommended applications to manage SQL Server databases in Azure.
What about yourself, are there any other applications that you would recommend to manage SQL Server databases in Azure?
If so, then feel free to share as a comment so we can let everybody know what their options are and make the community stronger.
Be First to Comment