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. For part 1 of questions answered from this webinar, click here. I've also included a download link for my slide deck, which you can easily access here: https://file.ac/EstZWn_07Bs/.
Answer: That is correct, Transparent Data Encryption (TDE) is on by default for your Azure SQL Database and the database encryption key (a symmetric key) is managed by Microsoft. If you want more control and manage your own keys, then you need to enable the preview feature at the Server level called, Bring Your Own Key. This uses an asymmetric key which is stored in an Azure Key Vault. Here is a link to more information on TDE for Azure SQL Database.
Question: If I would like to encrypt a few columns in the table and not the complete table, how can we do that?
Answer: Transparent Data Encryption (TDE) is done at the Database level, and currently there is nothing in the Azure Portal that would allow you to select specific columns for encryption. However, you can use T-SQL to encrypt a column of data. I also found a blog post on MSDN that covers recommendations for using Cell Level Encryption in Azure SQL Database that discusses this in more detail.
Question: What is the best practice for running stored procedures on an Azure SQL database?
Answer: Stored procedures are supported in Azure SQL Database and I would follow the same best practices you adhere to for On-Prem versions of SQL Server.
Question: Are all Azure SQL database environments treated the same for pricing? (Dev, Test, QA, Production)
Answer: No, and I have provided some links that discuss Azure Dev/Test Pricing below:
Question: Can you scale from Elastic to Non-Elastic and vice/versa?
Answer: You can add or remove existing Azure SQL Databases to an Elastic Pool, but they must be on the same logical server. Elastic Pools need to be configured and managed, and these allow for scaling just like a Single Azure SQL Database, for example, you can change performance settings like the Pool eDTUs and Pool Storage. Screen shot below.
Question: Does Azure SQL Database have SQL Agent Jobs?
Answer: No, the SQL Server Agent is not available in Azure SQL Database. Instead, we have Azure Automation which uses runbooks to define, manage, and schedule jobs. There are quite a few blog posts and Microsoft documentation on this topic, but I would start here if you want to know more about Azure Automation, https://azure.microsoft.com/en-us/services/automation/.
Question: What about running SSIS packages in Azure SQL Database?
Answer: The quick answer is No, you cannot deploy and execute SSIS packages on Azure SQL Database, as this feature is not available. However, you can use Azure Data Factory (ADF) v2.0 (which is a cloud data integration service) to run SSIS packages in Azure. If you are interested, PW recently was involved in compiling some hands-on labs that help walk you through this process, and I have included that link below.
https://azure.microsoft.com/en-us/blog/new-azure-data-factory-self-paced-hands-on-lab-for-ui/
Question: How do I move data between Azure and On-Prem?
Answer: This has earned my first classic consulting response, “it depends”. In my next session, I’ll cover migration from On-Prem to Azure SQL Database, but this question I will limit it to SSIS as that seems to be where I get the most questions/interest. You can use your current SSIS packages running on your On-Prem servers to connect to and move data to/from an Azure SQL Database as you would with any other database. I will discuss cost in question 23 below.
Question: How does cost for data usage (import/export) work?
Answer: As mentioned above, you can use your current SSIS packages to move data to/from, as you would with any other database, however, there are fees associated with this that Microsoft refers to as “Bandwidth Pricing”.
Inbound data transfers - (i.e. data going into Azure data centers): Free
Outbound data transfers - (i.e. data going out of Azure data centers): will cost you based on the number of GBs you are transferring.
Here is a link to pricing details >>> https://azure.microsoft.com/en-us/pricing/details/bandwidth/
Question: Can you restore to a local server or must you restore to an Azure database instance?
Answer: You are limited to restoring a copy of the Azure SQL Database to the same Azure SQL Server where the original database lived.
Question: How often does SQL Azure take backups? It looked like you could type any time you wanted in the restore you performed.
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 I just restore one table instead of the whole database?
Answer: No, there is not an option to restore a single table. To accomplish this, you would restore the Database and then go and grab the specific objects/data that you were targeting.
Question: What is the retention policy for backups?
Answer: This is broken down based on what Service Tier you have selected. Basic includes a 7-day retention period, while Standard and Premium bump this up to 35 days. There is a preview feature that allows you to extend this retention period up to 10 years, and it is referred to as, Long-term backup retention. Please note that this requires some additional configuration and an additional cost, so be mindful of this if you would like to enable this feature.
Question: Is there a problem or a cost involved if I pick a different data center for the database and the server?
Answer: Problem – No. Cost – Yes. The server and the database are 2 different Azure components which are both needed for the database to function. (Reminder: You also need a resource group, which is the container for your Azure resources.) To avoid getting charged for migrating data between data centers, all should be in the same data center.
Question: What would be a rough monthly cost at an IaaS level?
Answer: Microsoft has a neat Pricing Calculator where you can adjust settings to get estimate price points. I used the same example from our slide deck.
Azure SQL Database
Standard Tier (S3), 100 DTUs, 1 TB Storage >> Cost Estimate: $212.97
Virtual Machine
SQL Server Standard, 1 Core, 0.75 GB RAM >> Cost Estimate: $306.60
This doesn’t include Storage, so we add another $20+ dollars per month (cheapest Blob storage I could find for 1 TB). It certainly starts to add up. I would advise you to use the pricing tool yourself and try out various options. https://azure.microsoft.com/en-us/pricing/
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.