In a recent webinar, I discussed the fundamentals of Azure SQL Database including how to use it, how to monitor cost, and most importantly, why you should use it in your particular environment or situation. After the presentation, there were more questions than I had time to cover, so I have consolidated your questions and have provided my answers in this blog.
Answer: No. You are given the latest and greatest version of Azure SQL Database at the time of creation, which as of the writing of this post is V12.
Question: Is it possible to tweak Azure SQL Server configuration settings?
Answer: When you use Azure SQL Database, the instance of SQL Server is managed by Microsoft, taking all the hassle of managing this out of your hands. You can set the collation during provisioning of your Azure SQL Database, as well as setting up your server firewall rules, but if you require more control of your SQL Server instance, then you would want to consider using SQL Server on an Azure Virtual Machine (VM).
Question: Is there a difference between the versions of SQL Server and Azure SQL DB?
Answer: Yes. They share the same code base, and at the Database level they support most of the same features. I would suggest checking the features list for what is available in each as some features are On-Prem only. I have provided a link here to the features list for you to review: https://docs.microsoft.com/en-us/azure/sql-database/sql-database-features. Azure SQL Database, as of this post, has a compatibility level of 140.
Question: How many databases can I put on a Database Server? Is there a limit based on the pricing tier?
Answer: The maximum number of databases on a single database server is 5,000. This applies to all pricing tiers.
Question: Do I still have access to the system tables? Temp DBs?
Answer: Yes, you still have access to the system tables as well as the Temp DBs, however, for the Temp DBs there are restrictions on the number of data files and max size. Refer to this link for more information: https://docs.microsoft.com/en-us/sql/relational-databases/databases/tempdb-database
Question: Can you export the steps/settings you used when provisioning the Azure SQL Database?
Answer: Yes, you can go to the specific deployment (or a resource group) and view this template. These are referred to as Azure Resource Manager (ARM) templates and can be saved into your templates library in Azure, or if you wanted, you could download these as well. This allows you to set parameters or use the same settings for quick, easy, and consistent deployment either using the Azure Portal, Cloud Shell, or even PowerShell from your local desktop if you wish.
Question: How do I check the performance of my current Databases to determine the amount of DTUs I need?
Answer: The Database Transaction Unit (DTU) Calculator can be used to help you analyze and capture the needed performance metrics from your current server and workload. The following link provides more details and instructions https://dtucalc.azurewebsites.net/.
Question: Why didn't you pick the Basic version? Is a Standard that much faster?
Answer: The Standard Tier starts at 10 DTUs, while Basic is 5 DTUs, and if we went simply by DTUs then the Standard Tier has twice the computing power than Basic does. Computing power includes memory and CPU, so the quick answer is, “Yes, Standard is faster.” However, Basic limits your Database size to a maximum of 2 GB while Standard has 250GB of storage included. One last note to consider when selecting your service tier would be the price. Basic starts at roughly $5 per month, while Standard starts at approximately $15 per month.
Question: How did you get the data added to the database?
Answer: As part of the provisioning process, the Select source step, I selected the sample database (AdventureWorksLT) from the drop-down menu, as shown below:
In my next session, I will be going over how to migrate your data to Azure SQL Database, which you may be interested in.
Question: Is there an in-browser development workbench? Or, do you need to use SSMS?
Answer: As far as in-browser, you are limited within the Azure portal to more administrative and settings-related items. There is a query option available through a new preview feature called “Query editor,” but I typically see the use of that feature limited to doing quick checks on data. You do not need to use SSMS if you would rather use one of the other common SQL Server tools. For example, you could use Visual Studio or one of the open source tools such as cheetah, VS Code, etc.
Question: Can I turn the Server off when I am not using it?
Answer: No, however, if you are running SQL Server on a Virtual Machine (VM) in Azure, you can stop it and effectively “turn off” that Server when it’s not in use. When you want to use it again, you can start it just as easily.
Question: Can you step through the firewall configuration?
Answer: Absolutely, just follow the four steps listed below.
You should get a “Success” message once the Firewall rules have been updated.
Question: So Azure SQL no longer allows Synonyms nor Linked Servers, correct?
Answer: Synonyms are supported, but Linked Servers are not. Azure SQL Database instead uses what is referred to as an “Elastic query.” I have provided a link to more on that topic here: https://docs.microsoft.com/en-us/azure/sql-database/sql-database-elastic-query-overview.
Question: When scaling Azure SQL DB, is it required that there will be no active connection during that time?
Answer: When changing performance tiers, there is a brief moment (generally under 4 seconds) when the connections to the database are disabled. No data is lost, however some transactions that were in flight at that time may be rolled back. I would refer to the SLA should any downtime occur, even as little as 1 minute, but obviously this is one huge benefit in moving to the Database as a service model.
My webinar was one of Pragmatic Works' Free "Training on the T's" webinars, which happens every Tuesday at 11:00 EST. Visit our website to see upcoming topics and to register. You can also sign up for a free trial of our On-Demand Training platform with over 30 courses that cover a variety of topics including Business Intelligence, Business Analytics, Big Data, SQL Server Optimization and more.