Different offers from Microsoft Azure for SQL Server

SQLServerF1

Cloud solutions has been gaining increased support and many customers moving their data on to cloud technologies or planning to move in future. The reason for increase in popularity of the cloud technologies include that it reduces the operational and maintenance cost of hosting the own hardware and its day to day maintenance. Many operation tasks such as backups, patching, etc can also be taken care by the cloud solutions depending on the kind of offering selected. Microsoft and other companies like Amazon are investing a lot on the cloud technologies and trying to provide features that match the on-premise servers and applications or more than that.

Microsoft is offering its cloud services in different types depending on the services offered. These include Infrastructure as a Service (IaaS), Platform as a Service (PaaS), and Software as a Service (SaaS). These offerings are generic and is applicable for different applications which include SQL Server too. Coming specifically to SQL Server, Microsoft is offering two services SQL Server on Azure VMs (IaaS) and SQL Azure Database (PaaS).
SQL Server on Azure VMs (IaaS) – This is similar to on-premise SQL Server running on a virtual machine which is running on a host system whose hardware is maintained in Microsoft’s data center. In on-premise servers, DBAs or management discuss and decide on the hardware required like CPU, Memory and storage and the Operating System required, on top of which DBAs install SQL Server instances and create or restore databases. in SQL Server on Azure VMs (IaaS), Microsoft provides required hardware(CPU, Memory, Storage) and provides a VM with required Operating System too. DBAs can install SQL Server instance on this VM, configure and manage and administer it just like an on-premise SQL Server instance. If any support is required related to hardware, then DBAs are required to contact the Microsoft support. This is best suited for applications which does not need much changes after moving to cloud technology and where management want more control on the SQL Server.

SQL Azure Database (PaaS) – SQL Azure on the other hand is a Database as a Service offering of SQL Server. This SQL Server runs on a VM which is maintained by Microsoft and this server will be hosted on Microsoft Data Center. In this offering DBAs does not need to install, or manage things like patches, backups, upgrades, etc, as these things are taken care by Microsoft team either through automated scheduled tools to by manual maintenance depending on the type of the task. There are many things which are offloaded from DBA like high availability, disaster recovery, patch maintenance, etc. If high availability is required, we need to choose the right offering and license which provides the high availability. Here we can choose which server needs which features and based on the features used and the usage the price will be decided. DBAs or managers get access to Azure portal where we can setup new SQL Azure instances, configure them, choose required features, and find the connection strings to be used in the application or to use in SSMS to connect to this instance locally from out laptop or other devices. From SSMS or other applications, we can create databases, tables, users, etc and use the databases to store and retrieve the data. This is best suited for new applications which just need access to data and does not want to spend time and efforts on maintaining SQL Server like backups, patches, high availability, etc. One of the major limitation in this is that the existing applications are required to be rewritten to able to work on SQL Azure.

During initial stages when the SQL Azure service was introduced, there were lot of limitations, so was not used much, but as in 2015, the offerings have increased a lot and can support many applications to move their data to SQL Azure and use it without much issues. However there are still many limitations, when compared to on-premise SQL Server instances, which almost does not have any limitations.

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

Thanks,
SQLServerF1 Team
In-Depth Blogs on SQL Server, Information about SQL Server Conferences and Events, Frequently asked questions, SQL Server Trainings

 

Memory Counters to Check on Virtual Machine Running SQL Server

SQLServerF1

These days it is common to see SQL Server instances running on virtual machine. There would be multiple virtual machine guest systems running on one host server. To have more clarity, lets look at some terms.
Host – This is the server which runs on top of the hardware and manages and allocates all server hardware to all virtual machines.
Guest – This is the virtual machine which has been allocated few hardware resources(CPU, Memory, Storage) from the available pool of resources on the server.

As one virtual machine host system can create and host multiple guest virtual machine systems, all these guests will share the available CPU, Memory and Storage on the server. As a DBA, one of the responsibility is to ensure that the SQL Server instances running the virtual machine guest does not face any performance problems, but due to many configuration settings at the host and guest level, it is possible that all hardware resources allocated the the virtual machine guest system cannot be fully utilized by the SQL Server, instead the host may apply some further limits and does not allow the SQL Server to use the resources allocated to it. Sometimes, when the other applications on another guest system consumes more resources, it is possible that remaining guest systems on the same host may be impacted.

Memory is one of the important resource to ensure high performance, as reading data from memory is faster compared to reading from disk drives. Out guest system may be allocated certain amount of memory and DBAs may have set certain max server memory cap for SQL Server instance, but it is possible that SQL Server could never be allowed to use the allocated memory or is forced to release the memory allocated to it, so it is important to monitor if any memory is being asked to be released from the SQL Server. In general, DBAs monitor general memory counters like Server: Available Memory, Buffer Manager: Buffer cache hit ratio, Page Life Expectancy, Memory grants pending, Memory grants outstanding, free pages, total server memory, target server memory, etc. However there are also some memory counters to be monitored on virtual machine which include below.

Memory Limit(MB) – Amount of memory allocated to the guest.
Memory Reservation(MB) – This is the lowest amount of memory the guest will have. Although this is 100% of the guest’s memory, but that’s not always true. For SQL Server, generally the reservations is expected at 75% of the guest memory allocated.
Memory Ballooned(MB) – Memory released from the guest by Balloon driver.
Memory Swapped(MB) – Current amount of guest physical memory swapped out to the virtual machine’s swap file by the VMkernel. Swapped memory stays on disk until the virtual machine needs it.

Hope this was helpful.

This is applicable for below versions of SQL Server

SQL Server 2005
SQL Server 2008 R2
SQL Server 2012
SQL Server 2014
SQL Server 2016

Thanks,
SQLServerF1 Team
In-Depth Blogs on SQL Server, Information about SQL Server Conferences and Events, Frequently asked questions, SQL Server Trainings

 

Considerations for SQL Server Instances and Databases for Virtualization

SQLServerF1

Virtualization has growing demand and implementation after many success stories. Previously SQL Server instances were installed on servers with their own physical hardware, but that also introduced challenges of effective usage of the available hardware. Over a period of time, the hardware has dramatic changes which made available high configurations for cheaper price and smaller sizes and servers have become more powerful and can support a lot of CPUs, high amount of memory in terabytes, support for high storage, local, SAN and SSDs. So, virtualizaiton helps in using these powerful tools in an effective manner by allowing to create multiple virtual machine guests on one host server, thus making the hardware usage effectively.

With latest virtualization technologies have their own virtualization operating system which is installed on top of hardware even before the installation of the Windows Server Operating Systems. Once the Virtualization OS is installed, now it allows creation of multiple virtual machines each with certain set of CPU, Memory and Storage. Each VM acts as independent server and any operating system can be installed and any applications can be installed, this helps segregate multiple applications running on server server to running each application on its own virtual machine. When it comes to SQL Server, where the SQL Server instances are spreads cross many different servers need to be consolidated and moved into their own virtual machine or combine multiple databases and host them on their own VM and SQL Server instance.

To consolidate multiple SQL Server instances on different physical servers into their own VM ot on to a single SQL Server instance, we will need to follow a proper approach or consider various options to choose what suits the requirement better. Below are some of the options to consider inorder to choose which databases, instances and servers can be consolidated.

Version upgrade paths – Need to understand if we can group the databases which does not have issues upgrading to higher versions as soon as one is available. If the databases cannot be upgraded and requires lot of planning and testing before hand then such databases are better of to have their own dedicated VMs.
Maintenance Windows – Need to group databases which can have maintenance performed at certain time, such can be hosted on same VM and which have different maintenance times, can be moved to their dedicated VMs.
Security – Databases or applications which require high security are better of hosted on their own VMs.
Performance – Critical servers which require high performance can be left running on its own physical hardware or on a server which is shared by non-critical applications to avoid other servers using high amount of resources from the host server.
High Availability – Databases or applications which are very critical and require high availability needs their own dedicated VMs.
Licensing – If licensing cost needs to be reduced, then need to try and group databases with similar characteristics on one SQL Server instance or few SQL Server instances on different VMS.
Backup and Restore – Databases or applications which require very good planning for recovering data and time taken to recover the data is very small, such are to be hosted on own servers.

Hope this was helpful.

This is applicable for below versions of SQL Server

SQL Server 2005
SQL Server 2008 R2
SQL Server 2012
SQL Server 2014
SQL Server 2016

Thanks,
SQLServerF1 Team
In-Depth Blogs on SQL Server, Information about SQL Server Conferences and Events, Frequently asked questions, SQL Server Trainings

 

Tips to Improving SQL Server Backup Performance

SQLServerF1

Backups are one of the most important and core DBA task which every DBA needs to expertise. If there are any short comings or issues with backup planning, implementation, monitoring and testing, then it can result in data loss or can increase the time taken to recover the data with data loss. SQL Server supports multiple databases on SQL Server instances with each database size can be small of few MBs to very large in 100s of GBs to Tera Bytes TBs of late. There is additional care to be taken to plan and implement back strategy for large databases as the time taken to perform the backup increases significantly, so we need to diligently use the different backup methods available to backup the large databases like performing weekly full backups, daily differential backup and every 10-15 minutes log backups. The backups are important even when there are High availability and disaster recovery solutions implemented.

As a DBA some times we may need to look into possibilities of speeding up the backup process to save time taken for the backups of critical databases which are used 24×7. Performing backup can slowdown the databases to an extent due to IO load put by the backup on reading from database files and writing to backup file. There are different ways which we can consider to speed up the SQL Server database backup process to avoid problems for critical servers. Some of the methods or things which we can consider are below. It is important to note that some of the below mentioned options may be available only in latest versions of SQL Server instances, like backup compression available only starting with SQL Server 2008.

– Starting with SQL Server 2008, Microsoft introduced compressed backup. Using the backup compression not only reduces the size of the backup files, but also reduces the amount of IO performed to write to backup files, thus reducing the amount of time taken to perform the backups.

– SQL Server backups allow us to perform backup of one database into multiple backup files, thus increases the use of parallelism which speeds up the backup process. If we point the multiple backup files to different disk drives, then this will further spread out the IO load and makes the backup even faster. We can split database backup to be performed to many backup files, but we need to find the best number of files, as if we use too many files can impact negatively too.

– Some environments, it is common to perform backup directly to the network share or tape drives, which will cause the backup to take more time because the data has to be moved across network. If the backup of large databases is taking long time, then we can consider performing backup locally first to dedicated backup drive and then move the backup file from local backup drive to backup share or tape drive.

– We need to ensure that file system level backups and SQL Server backup timings does not overlap, as both running together will slowdown the system and causes the backup to take more time.

– For large databases we can choose alternative backup plans like performing weekly full backups, daily differential backup and every 10-15 minutes log backup, this we can restrict long time of full backups to weekly once. Differential backups are often fast compared to full backups. It is important to move all full, differential and log backups to backup share or tape so that they can be used in case the server or drive where backups are performed is lost or corrupted.

– We can also use some of the backup parameters like BUFFERCOUNT, BLOCKSIZE, MAXTRANSFERSIZE to improve the backup performance. There is no good or bad values for these parameters, it is trial and error approach, where we need to test different values and come to conclusion on what works best for your environment.

Hope this was helpful.

This is applicable for below versions of SQL Server

SQL Server 2005
SQL Server 2008 R2
SQL Server 2012
SQL Server 2014
SQL Server 2016

Thanks,
SQLServerF1 Team
In-Depth Blogs on SQL Server, Information about SQL Server Conferences and Events, Frequently asked questions, SQL Server Trainings