Introduction to SQLAzure for SQL Server DBAs
Microsoft has been aggressively focussing on promoting cloud technology. As a SQL Server DBA, one might wonder what is all about this cloud and how this works and how this impacts their role as a DBA. Although the terminology may be different, but a lot about cloud may already known to you or worked on, but may be not able to correlate to what you already know. For SQL Server Microsoft is offering two cloud platforms, one of which is Infrastructure as a service and the other one is database as a service.
Infrastructure as a service – This is similar like a hyper-v or VMWare VM, where you have a VM machine on which you install and manage SQL Server instances, databases and jobs, etc. The only difference is the VMWARE or Hyper-V management is taken care by your client company or your company, where in case of Microsoft Azure cloud the infrastructure is taken care by Microsoft and as a DBA, we need to take care of installing SQL Server, patching, maintenance, administration similar to what we used to do, but just on a Microsoft data centre.
Database as a service – This might be something different and which many DBAs may not be familiar about. In this the OS, Network, Storage and also SQL Server installation, patching, Backups, etc are taken care by Microsoft and we as DBAs are not responsible for such tasks, instead we can focus on other tasks such as migrating data from on-premise SQL Server to SQL Azure cloud and checking and working on making the SQL Server database resilient for performance. Database as a service is not a regular SQL Server instance which we used to manage on-premise, instead there are lot of limitations on what we can do and what changes we can make, for example, we cannot change many instance settings directly, we cannot alter storage configuration, or tempdb configuration, etc, instead we focus more at the database level and at data level.
If you are wondering what are the advantages for choosing SQLZure cloud solution is mainly reduction in cost of maintaining hardware and SQL Server maintenance of patches/backups, etc. Depending on the criticality of our database server and application, we can choose different kind of licensing based on our requirement of performance, data recovery and Disaster recovery features. So, for small test/dev servers you can choose servers with basic or minimal cost configuration and for productions servers depending on the size and usage of the database we can choose appropriate license. Different types of licensing includes, Basic, Standard and Premium and these are sub divided into S0-S1-S2-S3 for Standard and P1-P2-P3 for Premium.
As our data is stored on cloud on third party vendor place, it is important to understand how quickly we can recover our data and how much of data loss can occur in case there is a disaster, for which there are different types of Disaster recovery strategies to choose from, which include Geo-Restore, Standard Geo-Replication, Active Geo-Replication. In Geo-Restore, a copy of database can be restored on another region, but this will have data older than 24 hours, thus may not be good option for production data, instead can be used for dev. In Standard Geo-Replication, we can recover data up to 30 minutes before crash and can take up to 2 hours for the restore to complete and database to be available, but this will increase the cost. Finally, Active Geo-Replication, the data loss could be reduced to 5 minutes and amount of time to restore would about about an hour, this further increases the cost. Depending on our requirement, we can choose the best solution that suits.
in SQLZure, there is something known as DTU(Database Throughput Unit), which is used to measure the performance we get out of our SQLZure instance. DTU is a combined measure of CPU, Memory and IO of a database on a server and we can use this to compare the performance of database between different servers.
Hope this was helpful.
This is applicable for below versions of SQL Server
SQL Server 2008 R2
SQL Server 2012
SQL Server 2014
SQL Server 2016
In-Depth Blogs on SQL Server, Information about SQL Server Conferences and Events, Frequently asked questions, SQL Server Trainings