SQL SERVER – A Question on SSMS Prompt about Connect to Server

Today is Sunday, so letting us have some fun thing to discuss and think about. If you have not read my earlier blog post SQL SERVER – Connecting to Azure Storage with SSMS. I have discussed about how to connect to the SQL Azure storage via SSMS. Here is a follow up question I have on the same subject.

Question: When we connect to object explorer via the main toolbar menu, we get a prompt where we can connect to Azure Storage but when we open first object explorer why do we see the option to Azure Storage.

To better explain my question, please see the image attached herewith.

Do you have any answer for the same? If yes, please share.

Reference: Pinal Dave (http://blog.sqlauthority.com)

About these ads

SQL SERVER – Connecting to Azure Storage with SSMS

SQL Server Management Studio can now connect to Azure Storage.

It is very simple to connect to Azure Storage. First open the Object Explorer in SQL Server Management Studio (SSMS). Now click on Connect. Upon clicking on the connect it will bring up five options as displayed in the image below.

Click on “Azure Storage” and it will bring up another screen asking for storage account and account key.

Now go to your Azure account and get the name of the storage account, and account Key.

It will bring up following screen.

Enter the name of the account and account key at the prompt in SSMS.

Once you enter the details, it will immediately connect you with your Azure Storage. Next, expand the node and you will notice your containers.

The same containers will be visible in your Azure account online.

Reference: Pinal Dave (http://blog.sqlauthority.com)

SQL SERVER – Backup to Azure Blob using SQL Server 2014 Management Studio

Recently, while working on a demo about backup, I realized that in management studio, now there is a new option in the backup screen called a URL.  This feature is called as “backup to URL”.

I checked earlier version of SSMS and found that this was not there in the SQL 2012 management studio. There the options were “disk” and “tape” in SSMS 2012.

As soon as the URL is selected, it would ask for various options about backup destination in Azure.

Let’s go step by step and take a database backup to Microsoft Azure Blob storage.

To use this feature, you need to have is a Microsoft Azure account. If you do not have a Windows Azure account, visit Windows Azure 3-Month free trial. After logging it to the portal, I have created a “storage” called sqlauthority.

Under sqlauthority, I have created a container called “backup”.  This container was created as “Public Blob”.

If we closely look at URL it is http://<StorageAccount>.blob.core.windows.net/<ContainerName> Going back to SSMS, this is what we have to provide. But wait, how would SQL Server connect to Azure Blob storage? Well, that’s where the credential comes into the picture. Note that authentication to the storage account is required for SQL Server backup and restore even if we choose to create a public container. There are multiple ways to connect to the storage – publishing profile, certificate or SQL Credential. To use SQL credential, we need to get the secret key from the portal using below the screen. The screen would appear once we click on “Manage Access Key” in the third image of this blog.

Once we have the secret key, we can go to SSMS and right click on “Credential” under “Logins” to choose “New Credential”.

Once this is done, we are all set. Go back to UI and choose “backup-cred” in the drop down. Here is the final screen.

Once the backup is complete, we should see the backup on the portal. Notice URL http://sqlauthority.blob.core.windows.net/backup/Sales_backup_2014_06_04_101547.bak

Once the backup is available, we can also restore it. We can use management studio as well as T-SQL to restore the backup taken on the URL. While choosing the device, we need to use “URL” option, browse to storage container and choose correct file.

Go ahead and play with this feature.Let me know what you think of the same.

Reference: Pinal Dave (http://blog.sqlauthority.com)

SQL Authority News – Microsoft Whitepaper – SQL Server 2014 and Windows Azure Blob Storage Service: Better Together

Microsoft has released SQL Server 2014 earlier this month and now have released very long but interesting white paper on Windows Azure Blog Storage Services. SQL Server Data Files in Windows Azure feature is available in all editions of SQL Server 2014. This feature is enabled by default and at free of cost. This feature makes SQL Server 2014 hybrid cloud database as it provides native support for database files stored as Windows Azure Blobs. If you notice this is a big leap forward where data stored locally as well as on a cloud are accessed by SQL Server 2014 at the same time. You can also host SQL Server Instance in Azure cloud and it can also use the Windows Azure Blobs just like on-premises SQL Server.

Let me give you another example, if you have a situation that you have stored your database files in the cloud as well as backup on Windows Azure Cloud. Now if you want to restore your backup you do not have to bring that back to on-premises before restoring it. You can just restore your data directly from Windows Azure Storage to your SQL Server 2014 instance in a Virtual Machine. It will remove the necessity to do lots of data movement between cloud and on-premises servers.

This white paper discusses exactly the same feature very much in detailed. This white paper has over 116 pages and the size of the word file is 2.5 MB. However, after a long time I have found a Whitepaper which explains concepts from the beginning and walks users step by step with the examples. The appendix file to this Whitepaper contains all the necessary code as well as very interesting reference material. I think I am going to spend my entire weekend reading and learning from this Whitepaper.

Image included in this blog post is courtesy to the same white paper and it explains the story of entire white paper in just one image. Brilliant.

Download the white paper SQL Server 2014 and Windows Azure Blob Storage Service: Better Together.

Reference: Pinal Dave (http://blog.SQLAuthority.com)