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.
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.
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.
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.
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.
- How To Convert A Linux Disk Or Image File To VMDK File - July 8, 2020
- Read SMART Attributes Using PowerShell And Smartmontools - February 10, 2020
- How To Install OMS Agent On Server Core - June 25, 2018
