Feeds:
Posts
Comments

Archive for the ‘Microsoft Azure’ Category

SQL Server Management Studio is a handy tool that gives us the control to easily manage SQL Server. However, in Azure SQL Server, you can use its own query windows, where you can pass query against database. In today’s article, I will explain how to connect Azure SQL Server using your (on premises) SQL Server Management Studio (SSMS).

Pre-Requisite :

Given below is a step by step approach, demonstrating how to CONNECT Azure SQL using SSMS in simple steps.

Step 1 : (Create SQL Server in Azure)
First of all, you need to create/ setup a Server in Azure SQL Server using pre-requisite article.

Note : If you already have Setup Azure SQL Server, skip this step.

Step 2 : (Configure IP Address)
Once you have setup the Azure SQL Server in the above step, you just need to select that particular Server then further select Configure to add your local Server IP address from where you want to connect Azure using SSMS. This step is most important step because if you bypass this step and unable to add IP address of your local Server here, Azure will not allow your SSMS to connect Azure SQL Server.

Connect Azure SQL using SSMS.1.1

Connect Azure SQL using SSMS.1.2

Note : If you already configured the IP address of your local SQL Server machine, skip this step.

Step 3 : (Open SSMS)
Lets open SQL Server Management Studio and try to connect Azure SQL Server. Given below is the info that you must pass at the time of connectivity as shown below and Press Connect Button. Make sure your caps lock key should be turned on/ off accordingly ;).

Server Name : Azure SQL Server Name.database.windows.net (gx8icm0cm.database.windows.net)
Login : The login name which we created in pre-requisite article step 3.
Password : The password which we created in pre-requisite article step 3.

Connect Azure SQL using SSMS.1.3

Step 4 : (Azure SQL Server Connected in SSMS)
Now, you have connected Azure SQL Server using SQL Server Management Studio as shown below.

Connect Azure SQL using SSMS.1.4

Advertisements

Read Full Post »

I presume this will be your first step towards Azure SQL Server and I am sure you will find this article as a guideline to proceed and create SQL Server & database in Azure.
Given below is a step by step approach, demonstrating how to create them using simple steps.

Step 1 : (Create Azure Account)
First of all, you need to create a Microsoft Azure account (it is free for one month ONLY, however it asks the credit card details for verification ONLY).

Free tial account of Azure.1.1

Note : If you already have Azure account, skip this step.

Step 2 : (Select SQL Databases)
Once you create the account and Sign in to Microsoft Azure, you will be in the Microsoft Azure portal as shown below. Here you can see a lot of different exciting features, however, you need to select SQL DATABASES from the left pane. Once selected, you will see sql databases dashboard on the right hand pane. In order to create a NEW SQL SERVER, you need to select SERVERS from top and press + button at the bottom as shown below.

Azure Create SQL Server & Database 1.1

Note : If you already have SQL Server, skip this step.

Step 3 : (Provide SQL Server Info)
Now you are in the SQL database server settings screen. Here you need to select the SUBSCRIPTION (if you have ONLY one then it will be selected by default), LOGIN NAME, PASSWORD and REGION (always select the nearest region which gives you the best performance) and press the CHECK MARK shown below.

Azure Create SQL Server & Database.1.2

Note : If you already have SQL Server, skip this step.

Step 4 : (SQL Server Creation)
Now, your server has been started creating and it generally takes 1-2 minutes.

Azure Create SQL Server & Database.1.3

Your server has been created as shown below.

Azure Create SQL Server & Database.1.4

Note : If you already have SQL Server in Azure account, skip this step.

Step 5 : (Select the Server)
Now, you need to select the SQL Server you just created in the above step. (If you already have a server, you can select it). Once you selected the server, it will take you to the SQL Server dashboard. Here you need to select the Create Database as shown below.

Azure Create SQL Server & Database.1.5

Step 6 : (Create Database)
You are now in the database creation screen, here, you need to specify database settings. You need to enter database name and choose the Service tiers, performance level, collation depending upon your requirement. Once you specify all the settings, you need to press CHECK MARK button as shown below.

Azure Create SQL Server & Database 1.6

Now, your database has been created as shown below.

Azure Create SQL Server & Database 1.7

Azure Create SQL Server & Database 1.8

Let me know if you have created your first SQL Server & database in Azure and your feedback about it.

Read Full Post »

In my earlier articles, I explained, how to RESTORE the backup from URL using T-SQL, SQL Server Management Studio (SSMS)  from windows Azure storage and it was quite simple & easy to understand. In this article, I will explain how to RESTORE the backup from URL using PowerShell.

Pre-Requisite :

  1. Microsoft Azure – How to create storage and container
  2. SQL SERVER 2014 – How to take backup to URL (Windows Azure Storage) – PowerShell

Let me now demonstrate in few easy steps how to RESTORE backup from URL using PowerShell.

Step 1 – Create Account, storage & container :
First of all, you need to create a windows Azure account, a storage and a container as explained in this article.

Step 2 – RESTORE backup to URL :
Once you created the the Azure account, a storage and container and made sure that your backup is available in the windows Azure storage, you can RESTORE the backup from URL using given below script. In this script, first of all I declared the variables , then created credentials. After that RESTORED the backup as shown below. Rest of the script is self explanatory. Make sure that you have downloaded PowerShell Extensions for SQL Server in case you are using Windows PowerShell ISE instead of Windows PowerShell.

#Import sql module
Import-Module sqlps

#Create & set variables
$serverPath = "SQLSERVER:\SQL\WIN-KK48BQM9IS0\DEFAULT"
$storageAccount = "raresql"
$storageKey = "/ByNUTZqJ6EcJR/VQcNmNj+zSu++iCfbcxlyWye6Ok9uhsd5jsd62sjhsd7ksdh7sksdsdlhsd4bsdhsd52ksd=="
$secureString = convertto-securestring $storageKey -asplaintext -force
$credentialName = "My_Credential"

# Create Credential
New-sqlcredential -Name $credentialName -Identity $storageAccount -Secret $secureString

# Set SQL Server instance Path
CD $serverPath 

# Set the backup file name
$backupFile = "https://raresql.blob.core.windows.net/sql-backup/AdventureWorks2012_25_Dec_2014.bak"

# Restore Database and move files
$newDataFilePath = New-Object Microsoft.SqlServer.Management.Smo.RelocateFile ("AdventureWorks2012_Data","C:\Program Files\Microsoft SQL Server\MSSQL12.MSSQLSERVER\MSSQL\DATA\AdventureWorks2012_Data.mdf")
$newLogFilePath = New-Object Microsoft.SqlServer.Management.Smo.RelocateFile("AdventureWorks2012_Log","C:\Program Files\Microsoft SQL Server\MSSQL12.MSSQLSERVER\MSSQL\DATA\AdventureWorks2012_log.ldf")
Restore-SqlDatabase -Database "AdventureWorks2012" -SqlCredential $credentialName -BackupFile $backupFile -RelocateFile @($newDataFilePath,$newLogFilePath) 

How to RESTORE backup from URL (Windows Azure Storage) - PowerShell.1.1

How to RESTORE backup from URL (Windows Azure Storage) - PowerShell1.2

How to RESTORE backup from URL (Windows Azure Storage) - PowerShell1.3

As you can see, the messages show that your backup has been restored successfully.

Read Full Post »

In my earlier article, I explained, how to RESTORE the backup from URL using T-SQL and it was quite simple & easy to understand. In this article, I will explain how to how to RESTORE the backup from URL using SQL Server Management Studio (SSMS). It would be the same configuration but with the different screens & steps.

Pre-Requisite :

  1. Microsoft Azure – How to create storage and container
  2. SQL SERVER 2014 – How to take backup to URL (Windows Azure Storage) – SSMS

Let me now demonstrate in few easy steps how to RESTORE backup from URL.

Step 1 – Create Account, storage & container :
First of all you need to create a windows Azure account, a storage and a container as explained in this article.

Step 2 – Create Credential :
Once you have the details of Azure account, you need to create the credential using given below script. But if you notice, this script requires two parameters namely IDENTITY & SECRET. So how will I get them? Actually IDENTITY is the storage name that we created in the Pre-Requisite article -1 (Step 4) namely ‘raresql‘ and SECRET is the primary access key that we also picked up in the Pre-Requisite article -1 (Step 5). Lets create a credential.

Note: Please skip this step if you have already created the credentials.

USE master
GO
CREATE CREDENTIAL [My_Credential]
     WITH IDENTITY ='raresql'
    ,SECRET = '/ByNUTZqJ6EcJR/VQcNmNj+zSu++iCfbcxlyWye6Ok9uY3L5nw3XkndmAnDjiKn'
GO

Step 3 – Open Windows Azure Storage dialogue box :
Once you created the credentials, the next step is to right click on the particular database you want to restore the back up and select tasks, further you need to select Restore and Database … as shown below.

How to take backup to URL (Windows Azure Storage) - SSMS.1.1

Step 4 – Connect to Windows Azure Storage :
Now, SSMS opened the Connect Windows Azure Storage dialogue box, here you need to enter storage account (Pre-Requisite article -1 (Step 4)), account key (Pre-Requisite article -1 (Step 5)) & select / create the credentials. As we have already created the credentials in the above step, we will just select the credentials and click on Connect button as shown below.

How to take backup to URL (Windows Azure Storage) - SSMS.1.2

Step 5 – RESTORE the backup from URL :
Once you click on the Connect button, it will authenticate your account with Azure, once it will be authenticated, it will open the restore dialogue box and shows your back up. Now, you can select the back and restore it as shown below.

How to take backup to URL (Windows Azure Storage) - SSMS.1.3

How to take backup to URL (Windows Azure Storage) - SSMS.1.4

How to take backup to URL (Windows Azure Storage) - SSMS.1.5

As you can see, the messages show that your backup has been restored successfully.

Read Full Post »

In my earlier articles, I explained, how to take SQL Server backup to URL using T-SQL, SQL Server Management Studio (SSMS) & PowerShell. In this article, I will explain how to RESTORE the backup from URL using T-SQL.

Pre-Requisite :

  1. Microsoft Azure – How to create storage and container
  2. SQL SERVER 2014 – How to take backup to URL (Windows Azure Storage) – TSQL

Let me now demonstrate in few easy steps how to RESTORE backup from URL.

Step 1 – Create Account, storage & container :
First of all you need to create a windows Azure account, a storage and a container as explained in this article.

Step 2 – Create Credential :
Once you have the details, you need to create the credential using given below script. But if you notice, this script requires two parameters namely IDENTITY & SECRET. So how will I get them? Actually IDENTITY is the storage name that we created in the Pre-Requisite article -1 (Step 4) namely ‘raresql‘ and SECRET is the primary access key that we also picked up in the Pre-Requisite article -1 (Step 5). Lets create a credential.

Note: Please skip this step if you have already created the credentials.

USE master
GO
CREATE CREDENTIAL [My_Credential]
     WITH IDENTITY ='raresql'
    ,SECRET = '/ByNUTZqJ6EcJR/VQcNmNj+zSu++iCfbcxlyWye6Ok9uY3L5nw3XkndmAnDjiKn'
GO

Step 3 – RESTORE the backup from URL :
Once you created the credential, lets RESTORE the backup on URL using given below script, which requires two parameters, one is credential that we created in the above step and the second is the URL of the backup that we created in the Pre-Requisite article -2 (Step 3).

Note: Before proceeding with the Backup restore, kindly log in to your Azure account and verify the existence of the backup which URL you will mention in the below script.

USE [master]
ALTER DATABASE [AdventureWorks2012] SET SINGLE_USER WITH ROLLBACK IMMEDIATE
RESTORE DATABASE [AdventureWorks2012] FROM  URL = N'https://raresql.blob.core.windows.net/sql-backup/AdventureWorks2012_25_Dec_2014.bak'
WITH  CREDENTIAL = N'My_Credential'
,  FILE = 1,  NOUNLOAD,  STATS = 5
ALTER DATABASE [AdventureWorks2012] SET MULTI_USER
GO
--OUTPUT

How to RESTORE backup from URL (Windows Azure Storage) - TSQL

As you can see, the messages show that your backup has been restored successfully.

Read Full Post »

SQL Server backup to URL is a new feature shipped with SQL Server 2014. I explained how to take backup via SSMS (SQL Server Management Studio) in my earlier article. Today,  I will demonstrate how to take SQL Server backup to URL via PowerShell.

Pre-Requisite :

Let me now demonstrate in few easy steps how to take backup to URL via PowerShell.

Step 1 – Create Account, storage & container :
First of all, you need to create a windows Azure account, a storage and a container as explained in this article.

Step 2 – Take backup to URL :
Once you created the the Azure account, a storage and container, you can take a backup on URL using given below script. In this script, first of all I declared the variables , then created credentials. After that take the backup as shown below. Rest of the script is self explanatory. Make sure that you have downloaded PowerShell Extensions for SQL Server in case you are using Windows PowerShell ISE instead of Windows PowerShell.


#Import sql module
Import-Module sqlps

#Create & set variables
$serverPath = "SQLSERVER:\SQL\WIN-KK48BQM9IS0\DEFAULT"
$storageAccount = "raresql"
$storageKey = "/ByNUTZqJ6EcJR/VQcNmNj+zSu++iCfbcxlyWye6Ok9uhsd5jsd62sjhsd7ksdh7sksdsdlhsd4bsdhsd52ksd=="
$secureString = convertto-securestring $storageKey -asplaintext -force
$credentialName = "My_Credential"

# Create Credentials
New-sqlcredential -Name $credentialName -Identity $storageAccount -Secret $secureString

# Set SQL Server instance Path
CD $serverPath 

# Set the backup file name
$backupFile = "https://raresql.blob.core.windows.net/sql-backup/AdventureWorks2012_25_Dec_2014.bak"

# Take Backup Database to URL
Backup-SqlDatabase -Database "AdventureWorks2012" -backupFile $backupFile -SqlCredential $credentialName -CompressionOption On

How to take backup to URL (Windows Azure Storage) - PowerShell.1.2

How to take backup to URL (Windows Azure Storage) - PowerShell1.3

Step 3 – Verify backup to URL :
Lets go to the container inside the storage and check if the backup is available there or NOT because we saw the confirmation report in the above step.

How to take backup to URL (Windows Azure Storage) - PowerShell.1.1

Now, you can see above that your backup is available on Azure, what you have taken in the above step.

Read Full Post »

In my earlier article, I explained how to take SQL Server backup to URL in SQL Server 2014 using T-SQL. In this article, I will explain how to take SQL Server backup to URL using SQL Server Management Studio (SSMS).

Pre-Requisite :

Let me demonstrate in few easy steps how to take backup to URL using SSMS.

Step 1 – Create Account, storage & container :
First of all, you need to create a windows Azure account, a storage and a container as explained in this article. This step is mandatory to move forward.

Step 2 – Download Publishing Profile :
Once you have the details, you need to download the publishing profile. We will use this publishing profile in the next step to create credentials. Given below is the URL that will take you to your Microsoft Azure account, and once you log in your account, it will download the publishing profile having extension of .publishsettings.

Step 3 – Open Backup dialogue Box :
The next step is to take the backup of any database on Microsoft Azure. In order to do it, first of all select that database, right click on it, go to tasks and further select backup as shown below. This is the same procedure we follow to take the traditional backup.

SQL Server Back to Azure using SSMS.1..1

Step 4 – Open Credential Dialogue box :
Once you selected the backup option, it will open the backup dialogue box. Here, you need to select the Back up to is URL. Here you see few backup options but for now you need to ignore these options and create credentials. Click the Create credential button as shown below.

SQL Server Back to Azure using SSMS.1.3

Step 5 – Create Credential :
Now, you are in the credential creation screen, here you need to BROWSE the file which you have downloaded in Step 2. Then SELECT the CERTIFICATE, it will automatically populate the STORAGE(s) after AZURE account verification. Then you need to select the STORAGE. Once you are done with all selections, you need to give a NAME to the credential and press Create button.

Note : If you have already created the credentials, you do not need to follow this step.

SQL Server Back to Azure using SSMS.1.4

Step 6 – Setup Backup options :
Once you created the credential, you will revert to back up dialogue box and your created credential will be selected automatically. Now you need to enter the FILE NAME of the backup as well as the AZURE STORAGE CONTAINER NAME that we have created in earlier article at Step 9 as shown below. Press OK button.

SQL Server Back to Azure using SSMS.1.5

Step 7 – Back Up in Progress :
Once you pressed OK button, the back up operation will start, however as compared to local, this back up operation will be slow. Be patient and wait for it to finish the operation as shown below.

SQL Server Back to Azure using SSMS.1.6

SQL Server Back to Azure using SSMS.1.7

SQL Server Back to Azure using SSMS.1.8

Step 8 – Verify backup to URL :
The back up is done now, lets go to the container inside the storage in Windows Azure and check if the backup is available there or NOT because we saw the confirmation report in the above step.

SQL Server Back to Azure using SSMS.1.9

As you can see above, the Back up is available in Azure. Isn’t it cool 😉 ?

Read Full Post »

Older Posts »