August SQLBangalore UG Meet: It was a cloudy day

imageI am always amazed by the turnout on Saturdays for the SQLBanglaore UG Meetings. This time around it was an all Azure day where we had four sessions talking about different features that Azure offers.

The first session as about Machine Learning by Govind. He showed us how Machine Learning can make the machines smarter. It doesn’t mean that the machines are taking over but they can become your assistants with a bit of training. He went on to demo what Azure Machine Learning (in Preview) has to offer in this space.

This was followed by Angshuman’s session on Azure Redis Cache (in Preview). It gives you access to a secure, dedicated Redis cache, managed by Microsoft. A cache created using Azure Redis Cache is accessible from any application within Microsoft Azure.

The next session by Pranab was on the Smart Backup Feature in SQL Server 2014 which allows you to backup your SQL Server databases directly to Windows Azure Storage accounts. This enables you to pull down those backups for restore from anywhere in the world!

I had the last session and I was the one who was standing between attendees and their lunch! Not an enviable position to be in on a Saturday afternoon! This time around I had attempted to do things a bit differently. I started with the demo and moved onto my presentation. I used a video from PowToon to create a story line for provisioning a SQL Server virtual machine in a short span of time! That is available below. Lately, I have been using animation videos for a few of my presentations. And the view below is the one that I used for this session.

The presentation that I had used for my session is available below:

If you want the PowerShell script which performed the magic after creating the Azure SQL VM, you can from the files section of the SQLBangalore Facebook Group. Look forward to further posts on this blog detailing additional possibilities for that script.

Once again, a big THANK YOU to all the attendees as without them, these sessions will never be a success!

If you want to get notified about future posts, then you can follow me on one of these channels: Facebook | Twitter or simply subscribe to this blog (Available in the side-bar on the left).

Remapping the temporary drive on an Azure VM

There might be explicit requirements from an application standpoint which requires the D: drive to be available. While re-mapping your application to use another drive may be a very simplistic suggestion, it might not be viable in a certain scenarios. If you have used an Azure Virtual Machine, it is made clear in various articles that the temporary drive i.e. the D: drive should not be used. In this blog post, I shall show you how to re-claim that the letter D from your Azure Virtual Machine and assign that to another drive that might be craving for this particular letter of the English alphabet.

Azure VM Disk Management The first thing that you will need to do is assign a new data disk or an existing data disk to your Azure VM if you do not have a spare disk. This can be accomplished easily from following the steps mentioned here.

Once you have initialized the disks, your disk management view should be something similar to what you see in Screenshot 1. The temporary disk shows that it hosts the Page File. Remapping this first requires you to move the page file to a new disk or some other data disk that was already present on the server. You will have to reboot the machine for the  changes to take effect.

Once the machine is back up, change the drive letter mapping from Disk Management.

Change the page file settings to use the temporary storage but this time the drive letter would be a different one. Once you reconfigure the page file settings, you will need to reboot the virtual machine again.

When the virtual machine is finally online again, you will have your desired drive letter mapping. As you can see in Screenshot 2, the page file and my temporary storage is now the Z: drive where as the D: drive is assigned to a data drive.

Azure VM Page File Changed

Terminating an Azure SQL Database Replication

In my last post, I talked about setting up geo-replication for Azure SQL databases. There might be situations where you need to terminate your replication between your replicas. This could be a need for various reasons. You want to move your replica to a different region or you want to remove replication temporarily or you want to bring your secondary replica online and allow DML operations on it etc.

To remove replication, Azure provides two options: planned and forced termination. Again, if you have worked with on-premise database mirroring or availability groups, then this will seem familiar to you. Planned termination incurs ZERO data loss for the replica. The forced termination has chances of data loss.

Planned Termination is intended for use in planned operations where data loss is unacceptable. Termination can only be performed on the primary database, after the active secondary has been seeded.

Forced termination is intended for when the primary database or one of its active secondary databases is lost or is inaccessible. A forced termination can be performed on either the primary database or the active secondary. Every forced termination results in the irreversible loss of connectivity between the primary database and at least one active secondary. In addition, forced termination on an active secondary causes the loss of any transactions that have not been replicated from the primary database. If the primary database has only one continuous copy relationship, after termination, updates to the primary database will no longer be protected.

You will have to setup the replication again in case you want a synchronized copy of the database.

image

The steps to accomplish this is mentioned below.

Select the Geo-Replication tab for the database. This tab is only enabled for databases in subscriptions that are enrolled in the Premium preview program. Active Geo-Replication is currently only supported for Premium databases. You should see that the Replication Role for the database is displayed as source.

  1. Select the desired active secondary from the REPLICAS list.
  2. To terminate the continuous copy relationship, click Stop Replica. This launches the Stop Active Geo-Replication dialog which allows you to select the type of termination you want to perform.
  3. The Stop Active Geo-Replication dialog box presents two options when launched from the primary database:Stop replication after synchronization completes: This option ensures that the termination happens after the committed transactions on the primary have been replicated to the active secondary.Stop replication immediately: This option terminates the continuous copy relationship between the primary and the selected active secondary immediately. You should expect some data loss for the active secondary in this scenario.

    Select the Stop replication after synchronization completes option and click to confirm.

Reference:

Terminate a Continuous Copy Relationship
http://msdn.microsoft.com/en-us/library/azure/dn741323.aspx

Geo Replication meets Azure SQL Database

Active Geo-Replication enables continuous copy of your Premium database across geographic regions worldwide or within the same region. This is akin to setting up an availability group for SQL Server 2012 and above. For this you will need the following:

  • At least two Azure SQL Database Servers will available Premium database quota. The quota available is visible under the SERVERS tab in the Azure Management Portal for SQL Databases. You can alternatively retrieve the available quota by querying the server_quotas view (also in PREVIEW).
  • One or more Azure SQL Database configured on the Premium tier.

When downgrading from a Premium service tier, you must first terminate all Active-Geo Replication relationships. You can follow the steps described in the Terminate a Continuous Copy Relationship topic to stop the replication process between the primary and the active secondary databases.

The performance level of the secondary replica should be the same or higher than the primary replica.

Setting it up

In screenshot 1, you will see that the GEO-REPLICATION tab shown for the database optimus (Yes, another transformer character!) This will be available only if you have signed up for the PREVIEW feature for the new database tiers offered for Azure SQL Database. And if you have a Premium database configured, then you can setup a replica using the ADD REPLICA button which will be available at the bottom of the page.

image

On clicking the ADD REPLICA button, you will be provided with an ADD ACTIVE GEO-REPLICA screen. This allows you to pick another target server which will host the replica copy of your Azure SQL Database. See Screenshot 2.

image

Select the target server where you want your active geo-replica created. Only servers enabled for geo-replication in the same subscription as the source with available Premium database quota are shown. Additionally, only servers enabled for the current reservation size of the source database are shown. Replicas are created with the same database name, edition, maximum size, and reservation size as the source.

In the above screenshot, you can see that I have asked Azure to create a replica of optimus in the North Central US region and I have the primary copy hosted in the South Central US region. Once you proceed with your selection, you will be asked to accept terms and conditions as Premium database hosting attracts a cost!

Once the replication is configured, the Geo Replication tab will show you the status and the configured replicas as shown in Screenshot 3.

image

Reference:

SQL Database Pricing Details
http://azure.microsoft.com/en-us/pricing/details/sql-database/#service-premium

Changing Database Service Tiers and Performance Levels
http://msdn.microsoft.com/library/azure/dn369872.aspx

Active Geo-Replication for Azure SQL Database
http://msdn.microsoft.com/en-us/library/azure/dn741339.aspx

Restore a deleted Azure SQL Database

There could be a need where you have to restore a database that was deleted accidentally due to malicious intent or a plain old mistake (people end up writing resumes for such mistakes) or for some other inexplicable need! This is where backups come into the picture! For the Web and Business editions, this is not an option unless you have exported the database or you had made a copy of the existing database. Note that Business and Web service tiers will be retired in 12 months from April 24, 2014.

If you have just entered the world of Azure SQL Database, then this will seem even more difficult because you do not have the traditional scheduled backup job that you can trigger. So what is possible? Read on to find out…

Windows Azure SQL Database automatically creates backups of every active database using the following schedule: Full database backup once a week, differential database backups once a day, and transaction log backups every 5 minutes. The full and differential backups are replicated across regions to ensure availability of the backups in the event of a disaster. In addition, premium database offers the ability to create secondary databases in different regions to protect against datacenter disasters.

If you have played around with the new Azure SQL Database tiers (Basic, Standard and Premium), you will notice that there is a new tab available for Deleted Databases. This tab shows you the option of restoring a database (Basic, Standard and Premium tiers only) which was recently dropped. See screenshot 1.

image

I had dropped the bumblebee database which was a Basic tier database on 30th June. I can choose to restore this using the RESTORE button available at the bottom of the page. In the restore settings page popup page, you will have to specify the following:

1. Database name – This cannot be same as the original database

2. The time to which you want to restore available through a slider and text boxes. This is similar to the Timeline feature which was introduced in SQL Server Management Studio.

image

Note that the target server name cannot be changed. You will have to restore the deleted database backup to the same server. You cannot restore a database backup across Azure SQL Database Servers using this Wizard. Cross server restores are not currently supported.

While the database is restoring, you will see the database in the Databases tab but the status will show up as “RESTORING”. This is currently a PREVIEW feature. So you will need to sign up for the new database service tier preview to explore this feature.

Note that the deleted database backup is not retained indefinitely. As of July 1st, the following retention policy applies for the backups as shown in Screenshot 3:

image

More about Azure SQL Databases in a future post!

Reference:

Changing Database Service Tiers and Performance Levels
http://msdn.microsoft.com/library/azure/dn369872.aspx

Azure SQL Database Backup and Restore
http://msdn.microsoft.com/en-us/library/azure/jj650016.aspx

Management Portal for Azure SQL Database

The Azure SQL Database provides multiple options to connect to the database. The Management Portal being one of them. In my last post, I had talked about what options the Object Explorer offers you for an Azure SQL Database.

Connecting using the Management Portal

The simplest way to connect to an Azure SQL database is to use the management portal. The management portal link will be https://&lt;servername>.database.windows.net/?langid=en-us#$database=<database name> for any Azure SQL Database. When you log onto the management portal, you will need to provide the details shown below in Screenshot 1. If you have multiple Azure SQL databases hosted on the same server, then it is recommended that you provide the database name as the USE command is prohibited for switching database connections.

image

Continue reading

Object Explorer: Say Hello to Azure SQL Database

In my last post, I had talked about tackling the great Azure firewall and allowing your connections through to the Azure SQL Database. Now let’s talk about what you can actually do with SQL Server Management Studio and the Azure SQL Database. SQL Server Management Studio is pretty much the tool that regular users of SQL Server are familiar with. It would make sense to be able to manage your Azure SQL Database using Management Studio!

Since there are feature limitations in the Azure SQL Database, you will not get the full range of functionality when you connect to an Azure SQL Database. Before you attempt to connect to an Azure SQL Database, ensure that you have allowed access to the server.

Connecting to the Server

imageIn the Connect to Server dialog box (Screenshot 1), you will need to provide the server name and the SQL Authenticated user name. Remember to switch to the Connection Properties tab and add your database name. If you have more than one database hosted on the server, it is imperative that you provide a database name. The USE statement is not supported on an Azure SQL Database for switching connections.

Note that an Azure SQL Database only accepts TCP connections.

What will you see

imageIn the Management Studio Object Explorer, you will get a stripped down view of the server as compared to an on-premise SQL Server instance. Screenshot 2 shows view that you will get in Object Explorer. I see the master database and the database that I connected to (megatron) … Yes I am fan of the Transformers franchise! Interestingly, you will also see an Extended Events node within the database tree.

If you have enabled Federation, then you will see the federation information for your Azure SQL Database under the Federation folder. You can launch a new query window by selecting the database to execute your queries. This part is exactly similar to how you would run queries against an on-premise database from a Management Studio Query Window.

The objects that you see in Screenshot 2 are the only ones that you are allowed to create in an Azure SQL Database. You would have noticed that a SQL Agent is missing. If you want a SQL Agent in Azure, then you could leverage a SQL Server installation on an Azure Virtual Machine or an on-premise SQL Agent which connects to an Azure SQL Database or use Azure automation.

You do have the option of performing a right-click on any of the folders like Tables, Views etc. and selecting the NEW option to get a template script for creating a table, view etc. for the Azure SQL Database.

If you are using the Premium database feature (currently in PREVIEW), then you have the option of querying the server_quotas view (currently in PREVIEW) to understand the premium database quota available on this server.

More about the Azure SQL Database in future posts!

Reference:

Azure SQL Database General Guidelines and Limitations
http://msdn.microsoft.com/en-us/library/azure/ee336245.aspx

Azure SQL Database Tools and Utilities Support
http://msdn.microsoft.com/en-us/library/azure/ee621784.aspx#ssms

SQL Server Feature Limitations (Azure SQL Database)
http://msdn.microsoft.com/en-us/library/azure/ff394115.aspx