New Details regarding SQL Server on Linux and SQL Server 2016 Released on June 1st

SQLServerF1

Microsoft has officially released SQL Server 2016 on June 1st 2016. We can download an evaluation edition from here. This trial edition expires after 180 days, after which we will need to upgrade it to a licensed edition or download free express edition if the features used are minimal. Microsoft has introduced many new features with SQL Server 2016 in different areas. Some of the popular features include, Stretch database, Always Encrypt, Data Masking, also promises of significant improvement in the performance, much closer integration with SQL Azure, temporal tables, query store, Row level security, changes to upgrade advisor, etc. Initially Microsoft has released release candidate versions RC0, RC1, RC2 and RC3, which was downloaded and tested by many users in the DBA and Developer community and by some organizations and enough feedback was provided to improve the product. There were many bugs identified with the initial RC versions on various features, which were subsequently fixed in later RC versions.

Now SQL Server 2016 official RTM version has been launch on June 1st 2016. Once you install the new SQL Server 2016 RTM version, then you will see the build number as 13.0.1601.5. Some insights from Microsoft release documentation are “SQL Server 2016 is here! It is the biggest leap forward in Microsoft’s data platform history with faster transactions and queries, deeper insights on any device, advanced analytics, new security technology, and new hybrid cloud scenarios. SQL Server 2016 delivers breakthrough mission-critical capabilities with in-memory performance and operational analytics built-in. Comprehensive security features like new Always Encrypted technology helps protect your data at rest and in motion, and a world class high availability and disaster recovery solution adds new enhancements to AlwaysOn technology.You can also gain the benefits of hyper-scale cloud with new hybrid scenarios enabled by new Stretch Database technology that lets you dynamically stretch your warm and cold transactional data to Microsoft Azure in a secured way so your data is always at hand for queries, no matter the size. In addition, SQL Server 2016 delivers a complete database platform for hybrid cloud, enabling you to easily build, deploy and manage solutions that span on-premises and cloud.”

Another exciting news from Microsoft team is the announcement of further details regarding SQL Server support on Linux Operating System. So far SQL Server was supported only on Windows environment, but not there is lot of work in progress to get SQL Server work on linux environment too, just like it works on a Windows environment. SQL Server version which works on linux has not yet been released with SQL Server 2016, instead it may be released as a sub version later or as a new SQL Server version, but looks mostly it will be a sub version of SQL Server 2016 R2 or as part of a service pack. If you want to get hands on with SQL Server 2017 linux version, then at this point all we can do is sign up for Microsoft SQL Server on Linux and wait for further updates. You can register here for Microsoft SQL Server on Linux preview. Also, there has been a video released with small demos on installing and running SQL Server on linux environment. You can watch the video here. Important thing to note is that only database engine is so far been created and tested, but other services like Integration services, Reporting services, Analysis Services are still going to take some time to get on to the linux environment.

Hope this was helpful.

This is applicable for below versions of SQL Server

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

 

Azure Pricing for of Stretch Database in SQL Server 2016

SQLServerF1

New feature which was introduced with SQL Server 2016 is Stretch Database which migrates our historical data transparently and securely to the Microsoft Azure SQL cloud. Stretch Database provides some benefits to the users, but also has its own limitations which make it less likely to be used as of now, unless Microsoft comes up with significant improvements. Stretch Database in a SQL Server instance requires at least one table. It then silently begins to migrate the historical data to Azure SQL Database. If we are storing historical data in a separate table, then we can migrate the entire table. If our table contains both historical and current data, then we can specify a filter predicate to select the rows which need to be moved to Azure SQL database. Also, importantly, Stretch Database ensures that no data is lost if a failure occurs during migration. There is also retry logic to handle intermittent connection issues that may occur during migration.

Stretch Database lets us choose retention times of our choice even for large amounts of data without breaking the bank. Depending on our performance requirements, we can choose a performance level, and then scale up or down as needed. Stretch Database charges for Compute and Storage are charged separately, so we choose to only pay for what we use. Compute usage is represented as Database Stretch Unit (DSU) and customers can scale up and down the level of performance/DSUs that we need at any time. We have options for pricing based on different locations based on the currency. If we consider USD, below are the sample pricing options for usage of computing resources,
PERFORMANCE LEVEL(DSU) PRICE
100 $1.25/hr (~$930/mo)
200 $2.50/hr (~$1,860/mo)
300 $3.75/hr (~$2,790/mo)
400 $5/hr (~$3,720/mo)
500 $6.25/hr (~$4,650/mo)
600 $7.50/hr (~$5,580/mo)
1000 $12.50/hr (~$9,300/mo)
1200 $15/hr (~$11,160/mo)
1500 $18.75/hr (~$13,950/mo)
2000 $25/hr (~$18,600/mo)

Another pricing part which we need to pay separately for is storage. Storage rates are based on standard RA-GRS Page Blob rates. Storage transactions are not billed; customers only pay for data stored, not storage transactions.
Here Data Transfers refer to data moving in and out of Azure data centers other than those explicitly covered by the Content Delivery Network or ExpressRoute pricing.
LRS GRS RA-GRS
COOL HOT COOL HOT COOL HOT
First 100 TB / Month $0.01 $0.024 $0.02 $0.048 $0.025 $0.061
Next 900 TB / Month $0.01 $0.0232 $0.02 $0.0463 $0.025 $0.0589
Next 4,000 TB / Month $0.01 $0.0223 $0.02 $0.0446 $0.025 $0.0567

LOCALLY REDUNDANT STORAGE (LRS) – Makes multiple synchronous copies of your data within a single datacenter.
ZONE REDUNDANT STORAGE (ZRS) – Stores three copies of data across multiple datacenters within or across regions. For block blobs only.
GEOGRAPHICALLY REDUNDANT STORAGE (GRS) – Same as LRS, plus multiple asynchronous copies to a second datacenter hundreds of miles away.
READ-ACCESS GEOGRAPHICALLY REDUNDANT STORAGE (RA-GRS) – Same as GRS, plus read access to the secondary datacenter

OUTBOUND DATA TRANSFERS ZONE 1* ZONE 2* ZONE 3*
First 5 GB/Month Free Free Free
5 GB – 10.0 TB $0.087 per GB $0.138 per GB $0.181 per GB
Next 40 TB
(10-50 TB)/month $0.083 per GB $0.135 per GB $0.175 per GB
Next 100 TB
(50-150 TB)/month $0.07 per GB $0.13 per GB $0.17 per GB
Next 350 TB
(150-500 TB)/month $0.05 per GB $0.12 per GB $0.16 per GB

Outbound data transfers are charged at regular data transfer rates. A sub-region is the lowest level geo-location that you may select to deploy your applications and associated data. For data transfers (except CDN), the following regions correspond to Zone 1, Zone 2 and Zone 3.

Zone 1: US West, US East, US North Central, US South Central, US East 2, US Central, Europe West, Europe North
Zone 2: Asia Pacific East, Asia Pacific Southeast, Japan East, Japan West, Australia East, Australia Southeast
Zone 3: Brazil South.

There can be discounts to these prices based on region, number of servers, amount of compute and storage brought.

Hope this was helpful.

This is applicable for below versions of SQL Server

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

 

New Revamped SQL Server 2016 Upgrade Advisor

SQLServerF1

Most of the DBAs must be familiar with SQL Server upgrade advisor, which is used to run during planning phase of upgrading to higher SQL Server versions inorder to identify any objects or features that will get affected during or after the instance or database upgrade. With every new release of SQL Server, some old features may be deprecated or retired and behavior of some features might change in new versions. So, running upgrade advisor during planning phase on a test server where the production database is restored, will give us good idea on what objects are going to break during or after upgrading to higher version of SQL Server. In previous releases of SQL Server, the Upgrade Advisor tool was made available only few days before the release of the new SQL Server version, but with SQL Server 2016, the upgrade advisor has been released way early giving options to test the instances or databases and then to proceed with testing on RC releases of the SQL Server. Although, we may be very familiar with using the upgrade advisors for SQL Server 2005, 2008 R2, 2012 and 2014, but there have been significant changes to SQL Server 2016 Upgrade Advisor in look and feel and its usage.

We should be able to download the latest version of SQL Server 2016 Upgrade advisor from official Microsoft link here.
As per Microsoft documentation “SQL Server 2016 Upgrade Advisor Preview is a standalone tool that enables users of prior versions to run a set of upgrade rules against their SQL Server database to pinpoint breaking and behavior changes and deprecated features as well as providing help with the adoption of new features such as Stretch Database.
Once we download the SQL Server 2016 Upgrade Advisor, we will find SqlAdvisor.msi in the downloaded location. Run this msi installer by choosing run as administrator and you will need to have local administrator permission on the server where you are installing this tool. It is always recommended to install Upgrade Advisor on a test server and restore a copy of production database on the test server for analysis.

Once the installation of SQL Server 2016 upgrade advisor is completed successfully, launch the program and you will first see a What’s New screen which has some information regarding What’s new in SQL Server 2016 Upgrade Advisor. After that we will find a title bar with SQL Server 2016 Upgrade Advisor and next to it, you will see two main options “Scenarios” and “Active”. Apart from these two options, there are Notifications, Send Feedback and Settings which allows us to see new notifications for the product and to send any feedback to Microsoft for any bugs or new feature requests. Settings option has a gear icon which is used to choose any settings, as of now there is not much you can do here.

Inorder to start the upgrade analysis process, we need to choose “Scenarios” menu -> Run Database Upgrade Advisor -> Select databases to analyze -> Select Instance -> Select the instance from the drop down list if any available or create a new connection -> If we need to specify port number as well inorder to connect to the instance, then we can specify the port number by clicking on advanced options -> Once we successfully made connection to the SQL Server instance, next we will see list of databases to choose for the upgrade analysis -> Select one or more databases based on your requirement -> Run the upgrade analysis by clicking on Run button. It will take a while for the analysis to be performed. Once analysis is complete, you will be presented with a summary page. We will not only see the analysis for SQL Server 2016 instance, but we will find it for other versions as well based on what is the SQL Server version where upgrade advisor was run against, like if you run it against SQL Server 2008 R2 instance, then you will see the analysis for SQL Server 2012, 2014 and 2016. We can drill down for each of the versions and see the affected objects and the details about the impact and recommendation.

Hope this was helpful.

This is applicable for below versions of SQL Server

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

 

SQL Server 2016 Can Be Installed on Linux

As a SQL Server DBA, we never would have expected that Microsoft will someday support installing and running SQL Server instance on a linux machine, but it has come true as a surprise, as Microsoft confirmed in their official website about the same. Refer here for more information. Since the inception of SQL Server, it was never supported or possible to install SQL Server on a linux Operating System. Although, some baby steps on this was started, when SQL Server was supported to run on Windows Core. Since many years Microsoft had a firm stand on not supporting its applications on different Operating System platforms, but with changing world, Microsoft had to change its view as well. There has been mixed response from the SQL Server community regarding this move.

One of the main reasons sighted by Microsoft regarding this move was taking into consideration clients who preferred to use Linux operating System, but wanted to use SQL Server. As SQL Server was not supported to run on Linux, many organizations have moved away from SQL Server as they do not want to get stuck with Microsoft suite, which benefited oracle and other open source technologies like MySQL. With this move, now SQL Server will be picked up by many organizations which are running linux or other open source applications. Although this news has been received positively across the SQL Server and other platforms community, still there are many questions about the stability and performance of SQL Server running in linux operating system. Once we have more details on this and people start installing the SQL Server on linux, we get more details about the problems that arise while installing SQL Server in linux and trouble administering the SQL Server on linux. Also, it would be interesting to see how the performance would differ, while SQL Server running on Windows and linux systems with same hardware.

This will be a benefit for the SQL Server DBAs as they will now get an opportunity or even forcefully have to learn working with linux operating system, which mostly operates through commands better. Most SQL Server DBA’s get stuck with knowing only windows operating system, which makes it difficult for them to lean other RDBMS products like Oracle or MySQL as they can run on both Windows as well as linux operating systems. Oracle/MySQL DBA’s had more feasibility to learn SQL Server, than SQL Server DBA’s leaning Oracle or MySQL, now this will change. Also, this will change the mindset of the SQL Server DBA’s to understand the importance of managing or administering SQL Server and operating system though commands, rather than GUI. There is still a long way to go, as it is expected that there are many challenges on the way for the SQL Server to work seamlessly on linux operating as it works on Windows operating System. This will bring more customer adopt SQL Server product, thus opening more DBA jobs. Also DBA’s who like challenging tasks would love this move, as it is completely new and no or very little documentation will be ever on this, which makes their job more challenging and interesting.

Stay tuned for more information.

Hope this was helpful.

This is applicable for below versions of SQL Server

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

 

Comparing Installation Options Available for SQL Server on Azure VM

SQLServerF1

In the below mentioned previous post, we have discussed about different installation options available for SQL Server on Azure VM
Different Installation Options Available for SQL Server on Azure VM
It is important to understand the differences in the three option mentioned in the above post.
– Create a SysPreppedImage of the SQL Server version of our choice on an hyper-v VM on out local environment and then Upload it to Azure.
– Create a virtual machine running Windows from the Azure portal and then install SQL Server on it.
– Provision a SQL Server virtual machine in Azure from the Azure portal.

SysPreppedImage of the SQL Server on Hyper-V VM and Upload to Azure is preferred when you want to use your own licenses for Windows Operating System and SQL Server, so that you only need to pay for Azure compute and storage costs incurred for hosting your VM with SQL Server on Azure. Since SQL Server 2008 R2, has introduced of performing a SysPrep image, and the steps are simple. In this DBAs can choose and install required SQL Server versions and patches and required Operating System versions and patches instead of depending up on the versions provided by Microsoft Azure. However this is the most time consuming task of the three methods as this involves buinding hyper-v VM and preparing SQL Server SysPrep image and then uploading the VHD files to the Azure and then use it to create the VM. This is preferred when you want to use your own licenses which you are have, to avoid using Microsoft licensing available for Windows OS and SQL Server from Microsoft on per-minute usage basis.

Create a virtual machine running Windows is preferred you want to use your own license of SQL Server, but use the license of Windows Operating System provided by Microsoft, however the licensing of the Windows OS usage, compute and storage usage of Azure VM are calculated on the per-minute basis. SO, In this case, we pay only for the per-minute for the Azure Compute, Storage, and Windows license but not for the SQL Server license. In this DBAs can choose and install required SQL Server versions and patches. This involves additional work of installing SQL Server, its patches.

Provision a SQL Server virtual machine in Azure is preferred when you do not want to use any of your own licenses, instead only want to dependent or use the Microsoft licenses, but this usage is mostly calculated on per-minute basis. In this we need to pay per-minute for a SQL Server license along with an Azure Compute, Storage, and Windows license. This allows us to install SQL Server at desired version and service pack level, thus reducing the time taken for SQL Server installation along with VM setup and these things can be done from Azure portal with simple clicks and providing the options. This is best suited for applications which are required for short time for testing and later can be shutdown, thus brings down the cost.

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

 

Different Installation Options Available for SQL Server on Azure VM

SQLServerF1

Cloud solutions has been gaining increased support and many customers moving their data on to cloud technologies or planning to move in future. Once management decides on moving the SQL Server on to Azure, there are two options either to choose Infrastructure as Service (IaaS) or SQL Azure database as service (PaaS). If the management decides to have more control on the SQL Server and decides to use the Infrastructure as service option(IaaS), then next step would be is to get the SQL Server installed and running on the Microsoft Azure VM. There are different ways in which we can get SQL Server installed and running on the Azure VM which depend on factors mainly like Licensing. Depending on the type of licensing we choose for, we can have below options for getting SQL Server on the Microsoft Azure VM.

– Create a SysPreppedImage of the SQL Server version of our choice on an hyper-v VM on out local environment and then Upload it to Azure.
– Create a virtual machine running Windows from the Azure portal and then install SQL Server on it.
– Provision a SQL Server virtual machine in Azure from the Azure portal.
SysPreppedImage of the SQL Server on Hyper-V VM and Upload to Azure – In this method, we can use the SQL Server SysPrep install which creates a SQL Server image, which can be used to complete and create a full SQL Server instance on any other servers. Once the SQL Server SysPrep image has been created on a hyper-v VM, next step is to upload the VHD file of the hyper-v VM to Azure Blob storage. Now we can use the uploaded VHD file and create an image from Azure Management portal.

Create a virtual machine running Windows – In this method, we can create a windows virtual machine from the Azure portal. There is an option available on Azure portal to provision a Windows Server image. Once the Windows VM is created, next step is to copy SQL Server installation media on to the newly created VM and install the SQL Server on our own.

Provision a SQL Server virtual machine in Azure – In this method, we can install SQL Server directly on a windows VM from Azure portal. This method is an easy way to get SQL Server installed on the new windows Azure VM on Microsoft Azure.

All the three methods mentioned above have their own advantages and disadvantages interms of licensing, cost, etc. Depending on the requirement, DBAs and management can choose the appropriate method best suited for their environment.

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

 

Choosing Where to Host SQL Server Database

SQLServerF1

Cloud solutions has been gaining increased support and many customers moving their data on to cloud technologies or planning to move in future. When it comes to running SQL Server or hosting a SQL Server database, there are several options for DBAs or management which include On-premise physical server, On-premise Virtual server, Infrastructure as service(IaaS) or SQL Azure database as service(PaaS). There are various factors which can can consider about choosing which applications are better run on which of the above mentioned environment and which features impact on where the SQL Server instance and databases are better hosted in one of the above environments. Below are list of items to consider for choosing where to host SQL Server database from On-premise physical server, On-premise Virtual server, Infrastructure as service(IaaS) or SQL Azure database as service(PaaS).

SQL Server Version Support – If you are looking for flexibility and control over which versions of SQL Server instance and which patches to be applied then any one from On-premise physical server, On-premise Virtual server, Infrastructure as service(IaaS) can be your choice as all these support any version of SQL Server and patches to be applied based on DBA/Developer team recommendations. Where as SQL Azure database as service(PaaS) does not offer this flexibility and allows us to only choose from one the existing versions available and any new patches may be forced at times.
Security – For very critical applications, On-premise physical server, On-premise Virtual server are preferred as these are maintained at our own data centers and brings more control over security. Infrastructure as service(IaaS) can be used for sensitive applications which have high security requirements, but not too very highly critical.

Storage – In On-premise physical server, On-premise Virtual server, the storage support, configuration and its performance is purely determined by the storage teams as per the requirement from the DBAs, Developers and Management. For Infrastructure as service(IaaS) or SQL Azure database as service(PaaS), the storage and its speeds are to be chosen from available options and has different cost for different storage sizes and performance, so based on our requirement we can choose the require storage.

Backups – For On-premise physical server, On-premise Virtual server backups are to be taken care by DBA or backup team and can choose to use native maintenance plans, custom scripts or third party backup tools and can perform backups locally or to backup share or tape. In Infrastructure as service(IaaS) backups can be configured to store locally which is Azure storage. In SQL Azure database as service(PaaS) backups are taken care by Microsoft and we only have to choose how many days the backups are to be stored which decides RTO or RPO. Depending on the settings chosen the cost would be impacted.

Cost – On-premise physical server involves high cost for maintaining hardware, OS, Network, Storage, SQL Server, etc. For On-premise Virtual server the cost of hardware less compare to On-premise physical server as we can use one server host to host multiple guest systems, this reduces the hardware maintenance cost, but this will bring additional cost of administering virtualization, rest all costs remain same for Storage, SQL Server, etc. For Infrastructure as service(IaaS) the cost is further lower as this reduces the hardware maintenance cost as it is taken care by Microsoft, but other costs of Storage, SQL Server, etc will remain same. SQL Azure database as service(PaaS) is the lowest price option available as hardware, and many SQL Server operational costs are reduced as these are taken care by Microsoft.

Suited Applications – On-premise physical server, On-premise Virtual server are better suited for applications which are hosted on our own data center which avoids network latency between applications and databases. Mission critical, data sensitive and high performance required applications are better to be run on On-premise physical server, On-premise Virtual server where we want more control. Infrastructure as service(IaaS) is best for Dev, Test type servers or servers which are not mission critical, but still are important and expected high performance with varied performance at different times. SQL Azure database as service(PaaS) is best suited for new applications developer keeping in mind cloud technologies to take advantage of cloud features.

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

 

Pros and Cons of Running SQL Server On-premise vs Azure Cloud

SQLServerF1

Cloud solutions has been growing in popularity of recent with new offerings and more features added in different offerings and reduce or eliminating many traditional and operational overheads of maintaining hardware, servers, OS, Network, SQL Servers, patching, backups, High availability, Disaster recovery, etc. Depending on the type of offering choose, the cost will be impacted and features available. At present, SQL Server instances or databases can be hosted on different environments which include running SQL Server on physical hardware on-premise in client(our own) data center or running SQL Server on virtual machine guest system running on-premise or running SQL Server on Azure VMs(IaaS) or SQL Azure database as service (Paas).

There are many advantages as well as disadvantages of running SQL Server on above mentioned environments, with each one with their own pros and cons which are listed below.
Pros of running SQL Server on Azure
– Running SQL Server on Azure VMs will reduce the cost of hardware maintenance as it will be taken care by Microsoft, thus reduces the load on the data centers and reduces overall maintenance and costs.
– Depending on the type of Azure offering chooses, different administrative or operational costs are reduced. For example, using SQL Azure database as service PaaS will reduce the administrative tasks like Setting up and maintenance of hardware, Installation of SQL Server, maintenance tasks like patching, backups, High availability, etc are taken care by Microsoft. In Infrastructure as service offering IaaS, hardware costs are reduced.
– One of the important advantages of Azure VM and SQL Azure database as service are the flexibility of scaling up or down based on the requirement. For example, it is a common requirement for a test server with similar configuration of production specifications, which can be easily setup on Azure and once testing is complete, we can release or shutdown the Azure database, thus reduces the cost of maintaining the server outside of testing time, which can be significant. Another example, where we may need to scale up the performance of the server like shopping portal during thanks giving day or during festivals when there is high demand for the portal, Azure allows to quickly upscale the performance.

Cons of running SQL Server in Azure
– Although Azure provides cost effective cloud solution, but has its own disadvantages too. On SQL Azure database as service, there is not much control on the features or operations like patching, backups, etc.
– Also, another major concern about running SQL Server on Azure is security. Applications which store critical client data which comes under PII or finance related data, many organizations are not comfortable storing them on third party data centers where they do not have much control.
– The SQL Server instance databases main purpose is to store the data and allow it to be retrieved when required, so there is lot of dependency on the application and other services. So, it is important to consider where other dependent or related applications are running. For example, if applications and other services are running on-premise and SQL database on azure can introduce additional latency and any network issues can cause frustration to users, so any critical applications which are running on-premise may take advantage of SQL Server running on-premise too.
– Also, it is possible that some times the performance of SQL Server on azure is not predictable or consistent, because the SQL Server will be sharing the same hardware resources with other SQL Server instances or other applications, so heavy load from all servers or applications can lead to some resource saturation, thus causing bottlenecks at times, so performance is not always predictable on the azure.

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

 

Differences Between SQL Azure Database and SQL Server in Azure VM

SQLServerF1

Microsoft is making huge investments in cloud technology and is betting big for future growth in this area. Microsoft has started providing cloud services for various products, applications, Operating System or hardware support. Based on the type of service, Microsoft has made available different offerings which include Infrastructure as a Service (IaaS), Platform as a Service (PaaS), and Software as a Service (SaaS). Microsoft cloud support many applications or products out of which SQL Server is one of the Microsoft product which is supported on cloud by Microsoft. There are two different kind of offerings provided by Microsoft for SQL Server, which are Infrastructure as a Service (IaaS), Platform as a Service (PaaS).

Among these two services Infrastructure as a Service (IaaS), Platform as a Service (PaaS) offered by Microsoft, we a DBA or management, we need to understand what best suits our requirement and based on that we can choose the offering. It is important to understand the differences between these two offerings available for SQL Server, so that we can make an informed decision. Each offering for SQL Server from Microsoft has its own advantages and disadvantages interms if features, cost, High Availability, Disaster recovery, which operations tasks are taken care by Microsoft, etc. Below are some of the differences between SQL Azure SQL Database (PaaS) and SQL Server in Azure VM (IaaS).

SQL Azure database as service is best suited for new applications designed or optimized for cloud solutions or which depend on other cloud technologies, so that they can be used together. Developers building software-as-a-service (SaaS) applications can use Azure SQL Database to provide flexibility to support both explosive growth and profitable business models. On the other hand with Infrastructure as a Service (IaaS) is for applications which are already existing ones and cannot be changed easily and need to be migrated on to cloud without much changes to be made. Applications which are dependent on other on-premise resources are best suited for Infrastructure as a Service (IaaS) compared to SQL Azure database as service.

SQL Azure database as service does not support all features available in SQL Server and has many limitations for each feature, many of the features cannot be controlled by DBAs or developers and there is not much control for DBAs for operational tasks like patching, backups, HA or DR. Infrastructure as a Service (IaaS) is just like on-premise SQL Server instance on which DBAss or developers can make configuration changes, performs backups/restores, use required technologies for HA and DR purposes, etc. There are some limitations with SQL Azure database as service like max database size supported as of 2015 is only 500 GB, but this limitation does not apply for Infrastructure as a Service (IaaS). Another limitation include, SQL Azure database as service does not allow resources to be accessed from Azure to on-premise. Infrastructure as a Service (IaaS) does not have any such limitations.

In Infrastructure as a Service (IaaS), hardware, its maintenance costs are eliminated as it is taken care by Microsoft. SQL Azure database as service also eliminates the costs mentioned before, in addition to that, in SQL Azure database as service also eliminates maintenance and administration efforts and costs of patching, backups, HA, DR, etc. in SQL Azure database as service, there are different services available to choose for High Availability and disaster recovery where Point in Time Restore, Geo-Restore, and Geo-Replication to increase can be chosen to increase business continuity. In Infrastructure as a Service (IaaS), the High availability and disaster recovery is to be taken care by DBAs and we can choose any technology of our choice and we are responsible for administering, monitoring and fixing any problems.

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

 
1 2 3