So, one of the sessions I give at PASS events is on configuring your on-prem SQL instance. I have been working with DBaaS for a while now and decided I wanted to do a session on configuring DBaaS for beginners. So, the purpose of this blog post is to list some of the things that I believe are crucial for the first time deployment. But, I also ask of you, if there are other things that I may have overlooked, please, PLEASE, put them in the comments below. I’d love to have a community discussion around this!
So, 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, 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. Well, instead of disabling the SA account for your DBaaS instance, Azure just makes you create a unique one for your instance(I’m sure they have their own to it, 😉 ). I try and make sure the one I create is something more than just “instanceadmin”(Yeah, I’ve seen that before). I know the example is a bit blurry above, but my user is: MyTestUserThatIsSuperSecure. Obviously, I would not actually name my user this, but the idea is, do 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 my at LEAST 32 characters long(My team/developers HATE me) but I don’t care. That 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.
So, after your server is created, the real fun starts! Getting excited? Great! The next part that I normally configure is my firewall. Now, this is setup according to the needs of your application. One of the important settings here is the “Allow acess to Azure services”. The important thing to remember with this is, it allows access from ANY Azure services(not just the ones in the subscription the DBaaS instance is configured in).
Allow access to Azure services section
Since this DBaaS instance will need to be accessed from App Services, I need to set this to “ON”.
Now, the next thing you will notice in the firewall settings is “Client IP Address”. That’s right, remember how I mentioned earlier about more security coming? One of the biggest security features is IP based access. So, do you want to connect to this instance from your house? You have to add your IP Address here to allow it. Another way of doing this is when you try to connect via SSMS, it will prompt you 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 our company’s IP range. This way, when I am working 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(This is something that was suggested by Joey D’Antoni when he was reviewing our infrastructure. Thanks for that tip Joey! T/B).
Client IP Address section
So, since we are still working with security, the next piece of this would be Auditing & Threat Detection. Now, just to make it clear, these services are NOT FREE! There are fees for these services. Also, Threat Detection requires Auditing to be enabled. As of this posting, Table storage for Auditing is deprecated, so if you are setting up your instance today, make sure you use Blob storage.
Auditing & Threat Detection section
Now that we have some things configured, I want to make sure I do not lose my hard work, right? Did you know that Azure has resource locks? Another important thing to know about these are, DO 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 setup this way:
Now, 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. This is using Query Store to to add or remove indexes automatically for you(Amazing right!?). Some people do not want this to happen and 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
There are several other things to configure as well, but I think this is enough for now. Another blog post will be on creating a maintenance plan for your DBaaS instance. As always, thanks for reading and please comment below on what you think, and/or suggestions that you have!