Tag: Azure SQL

Getting Started With Azure SQL

In this post I will cover getting started with Azure SQL. Creating your database in the Azure Portal is easy enough, you choose a name for your database, a name for your server or use an existing one, select the collation and specify if you want a blank database or restore from an existing Azure SQL backup.

Before you connect to your SQL instance you have to white list the client IP addresses that will connect to it from outside Azure and/or enable all Azure services to connect to it for access inside the Azure data centre. In the old portal you go to SQL Databases and click on the servers tab and configure. In the preview portal there is a separate SQL Servers item in the main menu and you click on firewall to add your IP.

Logins work a little bit different. In Azure SQL all logins are created in your master database, master will be the default database for the logins and you can’t change it. The initial admin user created for you will have access to master and will work from SSMS without any action on your side. For other users you create you have to select the database to connect to in SSMS by clicking on the options button in the connection screen. If the user really needs access to master, create a user along with the login in master.

To add more users you can use SQL scripts or the Azure User Management Console if you prefer a gui. I used SQL scripts, first you create a login in the master database and optionally a user in the master database and then you create the user on the actual database you want to access.

On master

CREATE LOGIN newlogin WITH password='yourpassword'
GO

--Optional--
CREATE USER newuser FROM LOGIN newlogin WITH DEFAULT_SCHEMA=dbo
GO

On your database

CREATE USER newuser FROM LOGIN newlogin WITH DEFAULT_SCHEMA=dbo
GO

--Roles work the same as boxed SQL --
EXEC sp_addrolemember 'db_owner', 'newuser';
GO

Which service tier to use?
To determine the DTUs you will need doesn’t look like an exact science at this point and I can understand that since it is a synthetic benchmark. If you are migrating a boxed SQL database you can use this very handy tool to get a starting point. I recommend you setup an alert rule so you will know if your database is approaching the upper limit of the DTUs in the service tier.

Getting Started With Azure SQL

You can even use a webhook to notify you when this alert is triggered and kick off a script for instance to scale up your database automatically.

How to get my data into Azure?
Depending on the size of your database, the easiest method I find is to deploy your existing database from SSMS.

Getting Started With Azure SQL

I didn’t try this method with a very large database yet. I would rather recommend the import database option. First you have to export your boxed SQL database as a bacpac file into your Azure storage account.

Getting Started With Azure SQL

Select the Save to Windows Azure radio button and specify your storage account details. This will upload the bacpac file as a blob to your storage account.

Getting Started With Azure SQL

In the preview portal the Import button is on the SQL Servers menu after you select a server. You point it to your storage account and bacpac file and it will import it into a new database.

If you have a very busy SQL database that can’t be offline for long periods or will be way out of sync by the time you finished the import you can do the initial export and import using bacpac. Then use Azure SQL Data Sync to propagate changes from your boxed SQL server to the Azure SQL database until you can cut over. Currently it is only in preview so be warned.

Transient Errors
Although you can lift and shift your boxed SQL database into Azure SQL without making changes to your app from a SQL compatibility perspective, you usually have to make some small enhancements to cater for transient errors. Transient errors are part of the cloud experience, it is naive not to cater specifically for this scenario. Internet connections can dip, in a multi tenant environment the server could be under strain, the data centre could be migrating you to a different host etc. Luckily there are already frameworks to handle this for you and tools like EF even have it built-in. Here is a very good intro to the subject.

Francois Delport
Latest posts by Francois Delport (see all)

Azure SQL Introduction

Moving your MS SQL databases to Azure SQL might look daunting at first, especially the concerns around compatibility with your applications and security and to a greater extend the fear of anything new or unknown. To help you get started I’m going to give a quick overview of Azure SQL and share some of my experience using it thus far.

NOTE: Not all your applications will be able to run on SQL Azure it is not 100% compatible with on-site/boxed SQL server so you have to determine which of the incompatible features will be a blocker for your applications. That said the applications I moved to the cloud so far didn’t have any problems. The information in this post is from my own experience. There are so many factors impacting your specific database and application requirements that you’ll have to do a thorough analysis of azure SQL for your own applications and run some tests first, especially for very complex, data sensitive applications. For the remainder of the post, Azure SQL refers to Azure SQL Database V12 which is very close to SQL 2014 compatibility. Here is a link that lists the features that are not supported. Azure SQL is only the SQL database engine not SSRS, Analytics and other tools, you can still use SSRS from a boxed SQL instance and target your Azure database and there are other Azure products that like Data Factory to fill the gaps.

Firstly, why would you move to the cloud

  • In most cases it is cheaper to use Azure SQL rather than running a VM with SQL server, take a look at the pricing.
  • High availability, the SLA is 99,99% up time, if you feel brave go find out what you have to go through to get this with boxed SQL server. Azure keeps 3 copies of your data.
  • Disaster recovery is much easier with standard or active geo-replication.
  • It is easy to scale up, you just move to a better service tier and you can still scale out/shard using elastic scale, currently in preview only.

Service Tiers
Azure SQL comes in 3 service tiers Basic, Standard and Premium, the differences are in the transactions per second (DTU), backups and geo-replication options you get. To differentiate the performance levels the transactions per second allowed is expressed in DTUs. It is a synthetic benchmark taking into account disk I/O, CPU load etc and here is a tool to calculate the number of DTUs you need based on your current database load. When you reach your maximum DTUs you won’t get exceptions back from SQL but your queries will take longer and longer until they start to timeout.

  • Basic tier is for very small apps < 2 GB, 5 DTUs (1 level) and daily retained backups for a week.
  • Standard tier is for small/medium apps < 250GB, 10~100 DTUs (4 different levels) and point in time backups retained for 14 days.
  • Premium tier is for apps < 500GB but there is one Premium tier for 1TB databases, 125~1750 DTUs (5 levels) and point in time backups retained for 35 days.

The point in time backups you get with all the tiers allow you to restore the database to any point in time covered by the backups. I didn’t experience it myself but I’ve seen some posts about very long restore times, so test first. If you are concerned about availability and the time it will take to recover rather use geo-replication instead of relying on backups alone.

  • Basic tier does not offer geo-replication.
  • Standard tier offers standard geo-replication, you have only one secondary database in a different datacentre and it is non-readable, charged at a discount to the primary since it is off-line. You have to initiate failover manually in the portal or via a script.
  • Premium tier offers active geo-replication, you can have up to four readable secondaries which means you can run queries against them and use them as a means to scale our your read operations. You have to initiate failover manually in the portal or via a script.

Compatibility 

  • Azure SQL is binary compatible over the wire, you can connect to it from SQL Server Management Studio or your application just like you do for boxed SQL.
  • You can use Visual Studio Database tools and Data Projects to connect to Azure SQL. This is handy to do a schema compare to make sure your database in Azure is identical to the local SQL Server one.
  • You can export your Azure database as a DACPAC and import it into a local SQL Server and vice versa. Using SQL 2014 SSMS you can migrate your local database to Azure SQL.

In my next post I’ll show a quick demo on creating and connecting to an Azure SQL database and highlight some of the differences or surprises you might run into.

Francois Delport
Latest posts by Francois Delport (see all)