One of the sessions I give at PASS events is about configuring your on-premises SQL instance. I have also been working with Database-as-a-Service, DBaaS, for a while now and thought it would be helpful to do a session on configuring DBaaS for beginners. The purpose of this blog post is to list some of the things that I believe are crucial for first time deployment
First and foremost, I am going to assume you know how to spin up a DBaaS instance through the Azure portal. The MOST important steps in that deployment are: SA Username and SA password.
Now, there are a few things with the username space, one being that you cannot use “SA” as your user. I know, I was super sad by this too (just kidding). One of the first things I do after the install of my on-prem instances is disable SA once I have my SA account created. Instead of disabling the SA account for your DBaaS instance, Azure just makes you create a unique one for your instance. I try and make sure the one I create is something more than just “instanceadmin.” (Yes, I know my example user is: MyTestUserThatIsSuperSecure. I would not actually name my user this, but the idea is to not make it something super easy.)
The next part is the password. As always, please make sure that your password is secure. I try to keep mine at least 32 characters long (My team HATES me, by the way). The environment is being configured by me, and if anything would get compromised, I would be the first person questioned. So, it gets my standards when configuring. You should follow the same practice. There are other security features we will cover later, but it starts here.
After your server is created, the real fun starts! The next part that I normally configure is my firewall. This is set up according to the needs of your application. One of the important settings here is the “Allow access to Azure services” setting. The important thing to remember with this is that it allows access from any Azure services (not just the ones in the subscription that the DBaaS instance is configured in).
Since this DBaaS instance will need to be accessed from App Services, I need to set this to “ON.”
The next thing you will notice in the firewall settings is “Client IP Address.” Remember how I mentioned earlier that more security would be coming? One of the biggest security features is IP based access. Do you want to connect to this instance from your house? You have to add your IP Address here to allow it. Another way to do this is by connecting via SSMS (Only if you are an administrator for your subscription). When connecting via SSMS, you will be prompted for the username and password you use for Azure Portal access. By logging in that way, you add your IP address automatically from SSMS. One of the IP addresses I always remember to add is my company’s IP range. That way, if I ever have to access this instance remotely, I do not have to add my home IP address and clutter up the screen. As long as I connect to my corporate VPN first, I can access it.
Since we are still working with security, the next piece of this would be Auditing & Threat Detection. Just to be clear, there are fees for these services. Also, Threat Detection requires Auditing to be enabled. As of this post, table storage for Auditing is deprecated. If you are setting up your instance today, make sure you use blob storage.
Now that you have some things configured, you want to make sure you do not lose your hard work, right? Did you know that Azure has resource locks? Another important thing to mention is to not put the “Read-Only” lock on your DBaaS instance (trust me, bad things will happen). The lock you DO want to use is “Delete.” I have mine set up this way:
If anybody tries to delete your instance, they get a nice error prohibiting them from doing so!
The last part for this blog post will be for after you add some databases to your instance. This is the “Automatic Tuning” section, and it is using Query Store to add or remove indexes automatically for you (amazing, right!?). Some people prefer to add indexes themselves, and I agree with both sides. If you are interested in knowing more about the process, check this out: Query Store Link.
Thanks for reading, and please comment below on what you think and/or suggestions that you have. I’d love to have a community discussion around this!