Archive

Archive for the ‘SQL Databases’ Category

Import Azure SQL Database export file to On-Premises SQL Server

March 20, 2015 Leave a comment

Login to On-Premises SQL Server Instance.

 

image

 

Right Click the Databases Folder and select [Import Data-tier Application]

image

 

image

Choose Import from Windows Azure Storage

image

image

Provide the Storage Account information

image

 

Provide the temporary storage location for file to be downloaded.Provide the location which has enough space (.bacpac filesize + 10%)

Specify the target Database Name and file locations

 

image

 

image

 

image

image

image

image

Categories: SQL Databases

Export Azure SQL Database to Azure Storage

March 20, 2015 Leave a comment

You can export the Azure SQL Database using PowerShell , Azure Portal , Visual Studio,SQL Server Management Studio. In this post i have used PowerShell to export the Azure SQL Database to Azure Storage.(You can also export it to you on-premises local storage)

#————————–Create SQL Server Azure Database Context —————————#

import-module ‘C:\Program Files (x86)\Microsoft SDKs\Azure\PowerShell\ServiceManagement\Azure\Azure.psd1’

#—-Provide the sql login and password whose database will be exported—-#

$cred=Get-Credential

$sqlDatabaseServerNameFQDN=’faw5udm867.database.windows.net’

$Sqlctx=New-AzureSqlDatabaseServerContext -FullyQualifiedServerName $sqlDatabaseServerNameFQDN -Credential $cred

Get-AzureSqlDatabase -ConnectionContext $Sqlctx | SELECT Name,CollationName,Edition,MaxSizeGB,Createdate |Format-Table –Autosize

#————————–Create Azure Storage Context —————————#

Import-AzurePublishSettingsFile C:\Azure\Azure_credentials_file_2.publishsettings

$subscriptionName = "Free Trial"

$storageAccountName = "dbproxazurestorage"

$storageaccountkey = Get-AzureStorageKey $storageAccountName | %{$_.Primary}

$StorageContainer = Get-AzureStorageContainer -Name containerstorage -Context $StorageCtx

#————————–Initiate Export —————————#

$exportRequest = Start-AzureSqlDatabaseExport -SqlConnectionContext $Sqlctx -StorageContainer $StorageContainer -DatabaseName Rehsosu –BlobName Rehsosu_database_export.bacpac

#————————–Check Export Status—————————#

Get-AzureSqlDatabaseImportExportStatus -Request $exportRequest

image

image

 

Categories: SQL Databases

Deploy Database from Azure SQL Database to Azure VM SQL Database

March 20, 2015 Leave a comment

 

In this post we will go-over on how to move the Azure SQL Database to Azure VM SQL Database. You cannot backup the the Azure SQL Database by issuing Backup Database  Command on version

[Microsoft SQL Azure (RTM) – 11.0.9230.80  Mar 10 2015 12:37:14      Copyright (c) Microsoft Corporation]

it will prompt the below error

Msg 40510, Level 16, State 1, Line 1
Statement ‘BACKUP DATABASE’ is not supported in this version of SQL Server.

To quickly deploy the database from Azure SQL Database Server to Azure VM SQL Database Server i used [Deploy SQL Database to Windows Azure Database] Wizard from On-Premises SQL Server Management Studio. (Note:  If the firewall rules are not opened to connect to Source and destination from On-premises firewall  the wizard will fail to connect and deploy)

 

image

 

Categories: SQL Databases

Connecting Azure SQL Database using PowerShell

March 19, 2015 Leave a comment

 

In this post we will connect Azure SQL Database using PowerShell.

I assume that you have a Azure Database Created using Azure Portal and the login information which was provided at the time of database creation.

Import Azure PowerShell Module.

import-module ‘C:\Program Files (x86)\Microsoft SDKs\Azure\PowerShell\ServiceManagement\Azure\Azure.psd1’

Azure SQL Database can only be accessed using SQL Server Authentication

#Create Azure SQL Database server connection context
# Define a SQL Database server credential – Specify the username, and password in the Popup dialog

 $cred=Get-Credential

image

 

image

$sqlDatabaseServerNameFQDN=’faw5udm867.database.windows.net’

 

image

$ctx=New-AzureSqlDatabaseServerContext -FullyQualifiedServerName $sqlDatabaseServerNameFQDN -Credential $cred

image

Get-AzureSqlDatabase -ConnectionContext $ctx | SELECT Name,CollationName,Edition,MaxSizeGB,Createdate |Format-Table

image

Categories: SQL Databases

Connecting Azure SQL Database using SQL Server Management Studio

March 18, 2015 Leave a comment

There are multiple ways of connecting Azure SQL Server

  • Connect by SQL Server Name
  • Connect by URL
  • Connect by FQDN

 

Connect by server name require you host name configuration changes at your end or you should have your VPC / VPN .You can read more here on the guidelines for Connecting to Azure SQL Database.

In this post we will see how to connect Azure SQL Database using SQL Server Management Studio and PowerShell.

I assume that you have a Azure Database Created using Azure Portal and the login information which was provided at the time of database creation.

 

image

Click on the database , it will take you to the database information page, here you will see [Connect to your database] and the Server details.

image

image

Copy the Server Information  (Server: faw5udm867.database.windows.net,1433)

open SQL Server Management Studio and pass the details as below. You may get Error while connecting for the first time which is to allow access to you IP Address.

To get you IP Address added to the Azure firewall rule follow the below steps on Azure Portal.

image

image

Categories: SQL Databases