Configuring and Administering Windows Azure SQL Database– Part 2

Author: Basit A. Farooq

Working with Windows Azure SQL Database

We can use SQL Server Management Studio to perform administration, maintenance and development tasks for Windows Azure SQL Database server. SQL Server Management Studio provides us with a great user interface for the management of SQL Server. However, these management and maintenance features are not available via a graphical user interface for Windows Azure SQL Database server. Instead, we can perform these tasks by writing Transact-SQL code, which we can then execute against Windows Azure SQL Database server. For example, the following is the Transact-SQL syntax to create database, table and view on Windows Azure SQL Database server:

Unlike on-premise SQL Server, Windows Azure SQL Database server does not support USE statement to switch between databases. Using the USE statement will result in following error:

So to run the code against different database, you must change connection to the database where you would like to run the code.

Security Administration of Windows Azure SQL Database

The Windows Azure SQL Database server uses the same SQL Server core technologies. Therefore, the security administration and authorization model of Windows Azure SQL Database server is similar to on-premise SQL Server instance. For example, like on-premise instance of SQL Server, we can connect to Windows Azure SQL Database server, and then use CREATE, ALTER or DROP statements to create, alter and drop users or logins from Windows Azure SQL Database server. We can also associate users in each database to server logins. There is also a difference between Windows Azure SQL Database server and on-premise instance of SQL Server, that is, the way we manage the server-level security in both environments, since they both use a different strategy for server-level permission. For example, we use server-wide roles to manage on-premise instance of SQL Server, but those server-wide roles do not exist in Windows Azure SQL Database server. Instead, we have different server-wide roles for Windows Azure SQL Database server environment, such as loginmanager to create logins and dbmanager to create a database. That’s because the logical administration of Windows Azure SQL Database server is different from on-premise SQL Server. For more information about the security administration differences, see Managing Databases and Logins in Windows Azure SQL Database.

Creating Logins and Users on Windows Azure SQL Database
The Windows Azure SQL Database server provides same set of security principals that are available in normal on-premise SQL Server authentication. The purpose of these principals is to authorise and secure the data hosted on Windows Azure SQL Database server environment. For example, similar to on-premise instance of SQL Server, in Windows Azure SQL Database server, logins are used to authenticate access at the server level, database users are used to grant access to database at the database level and database roles are used to group users and grant access at the database level.

Creating a New Login

The login creation process for Windows Azure SQL Database server is similar to on-premise instance of SQL Server except that we cannot create login based on Windows credentials. This means that all access to Windows Azure SQL Database server is via SQL Server Authentication. The following steps details the process of creating new login for access to Windows Azure SQL Database platform:

Launch SQL Server 2014 Management Studio, and then connect to Windows Azure SQL Database server as Administrator. Next, click on New Query and then type and execute the following Transact-SQL script, to create new login called “CloudDev01” on your Windows Azure SQL Database server:

After executing the above statement, expand the Logins folder under Security folder in Object Explorer, to verify the creation of login:

You can also query sys.sql_logins view to list all logins created on server.

Creating a New User

Once the login is created, we can then create the database user account for that login in a specific database, to give that login access to the database. For example, enter and execute the following Transact-SQL code, to create a database user called CloudDev01 for login CloudDev01 inside MyCloudDB_01 database.

Assigning Access Rights

To assign server-wide permissions to login on Windows Azure SQL Database server is similar to assigning server-wide permissions to logins on on-premise instance of SQL Server. For example, you can use the following Transact-SQL code snippet to assign create databases and create logins rights to CloudDev01 login.

Remember: The members of loginmanager database role in the master database are able to create and manage logins whereas the members of dbmanager role will be able to create and manage databases hosted on Windows Azure SQL Database.

Now that we have assigned server-wide permissions, let’s assign some database level permissions. Execute the following script, to assign db_owner rights to CloudDev01 database user on MyCloudDB_01 database:

Migrating Databases to Windows Azure SQL Database

We can use SQL Server Management Studio to migrate our existing SQL Server database to Windows Azure SQL Database server. To do that, launch SQL Server 2014 Management Studio and connect to SQL Server instance. Right-click the database which you want to migrate, then choose Tasks and then choose Deploy Database to Windows Azure SQL Database option.

This launches Deploy Database Wizard, Introduction Page.

Click on the Next button to launch Deployment Settings page. Click on the Connect button, to connect Windows Azure SQL Database server.

Specify other settings and then click on the Next button to proceed to Summary page. Here, verify your selections and then click on the Finish button to start the export of your database to Windows Azure SQL Database server. This process may take a while, depending on the size of the exported database and your network bandwidth.

Click on the Close button once the export is successfully completed.

Now to verify the export, in Object Explorer, connect to Windows Azure SQL Database server, where the database has been exported. Refresh Databases folder and you will see your migrated database. For example, in my case its a database called BankHolidays, which I exported to SQL databases server.

We can also use SQL Server 2008 or above Integration Service or BCP utility to transfer data to Windows Azure SQL Database. For more information, see Migrating Databases to Windows Azure SQL Database (formerly SQL Azure).

Backups and Restores with Windows Azure SQL Database

Currently, Windows Azure SQL Database server does not support normal SQL Server database backups. Instead, it supports a new feature called Database copies that can be used to perform transnationally consistent copies of your primary database into another Windows Azure SQL Database server. This option can be used as a backup and restore option for Windows Azure SQL Database server. The syntax for the command is as follows:

The following code example demonstrates, how to create a database copy of MyCloudDB_01 database using the Transact-SQL CREATE DATABASE statement:

After the database copy begins, we can execute the following query that is written using sys.databases and sys.dm_database_copies system views of the master database to monitor database copying progress.

Once the database is copied, you can see that in Object Explorer.

Note: As per Windows Azure documentation, we can create a copy database either on same Windows Azure Windows Azure SQL Database server or different Windows Azure Windows Azure SQL Database server as long as it’s hosted in the same data center. Also, dbmanager permission is required to execute database copy command.

Restoring Database for Database Copy

We can use ALTER DATABASE statement, to restore our database from copy database. This is simple because we will just rename the original database to something else and then rename the copy database to the original database name. Both database and copy of the database must be hosted on the same server. For example, in the following code, I’m first renaming the MyCloudDB_01 database, which is our original database to MyCloudDB_01_OLD, and then I’m renaming the MyCloudDB_01_Copy database to MyCloudDB_01.

Replication

The legacy replication feature of on-premise SQL Server is not required for Windows Azure SQL Database server. That’s because of data sync services feature of Windows Azure SQL Database server. Data Sync services provides easy to use, wizard-driven interface, which we can use to “replicate” the database from one database to another. This interface provides multi-direction data synchronisation between two Windows Azure SQL Database databases or between two Windows Azure SQL Database servers and an on-premise SQL Server database. Windows Azure SQL Database services maintain three copies of any database, which includes the primary database and two replicas of the primary database. These database and replicas are automatically maintained.