Windows Azure – Migration of on premise SQL Server 2014 database to Azure – Part 2

In my previous post (On-Premise SQL Server Database Migration to Windows Azure), I had detailed migration of the database by connecting directly from SSMS (supported by SQL Server 2012 and 2014). Today, I will go through alternate migration method using data-tier application files (.dacpac files. For additional reference click here )

Prerequisite: This article also refers to usage of Blobs container in Azure Storage account. The steps explained here requires you to have an Azure Storage account configured with valid access keys for connectivity. If you aren’t familiar with Azure Storage account, go through my post (Windows Azure – Configuring Storage Accounts)

There are two stages in the migration. The first stage is to generate the .bacpac file of your target on-premise database for migration. The option in SQL Server 2014 is shown below:

Untitled0

 

For the destination, you have to connect to your Azure Storage account. Establish the connectivity using your Account Key.

Untitled

Once connected to your Azure storage, select the container (here, I have selected my Blob Container )

Untitled1

Few moments of waiting; and your .bacpac is uploaded to cloud storage.

Untitled2

The second stage is performed at Azure portal. Login to your azure account and select the SQL database server, and select the option “Import Database”

Untitled3

Select the Azure Storage and the container  as shown below

Untitled4

Select the .bacpac file

Untitled5

Specify the Pricing tier desired, database name and user account for login

Untitled51

Few moments of waiting… your database is getting created on the target server.

Untitled67

And the success notification. The database is online !

Untitled7.png

For verifying the database, let us try connecting from your local SSMS 2014 to new database in Azure and probably fire up some queries & procedures.

Untitled8

Whohaa ! All looking good here

Untitled9

For connectivity from your application, remember to use the connection strings as specified in Azure DB server. For example, connection strings format for this Sales DB to be used in your applications are specified below:-

ADO.NET

Server=tcp:{servername}.database.windows.net,1433;Database=SalesDB;User ID={username_here}@{servername_here};Password={your_password_here};Encrypt=True;TrustServerCertificate=False;Connection Timeout=30;

JDBC

jdbc:sqlserver://{servername_here}.database.windows.net:1433;
database=SalesDB;user={your_username_here}@{servername_here};password={your_password_here};encrypt=true;
trustServerCertificate=false;hostNameInCertificate=*.database.windows.net;
loginTimeout=30;

 

Windows Azure – Configure Firewall for SQL Server Connectivity

Facing connectivity issues while connecting from client to Azure SQL Server database? Security settings in Windows Azure mandates that the firewall must be configured with a range of IP addresses to allow connectivity to server. The steps below demonstrates how to set this from Azure portal.

Login to your Azure account at  https://portal.azure.com

SQL databases ->  Select your database -> Settings

Untitled

Under Firewall settings, you can specify specific IP addresses or range of IP address from which you want to allow connections to your SQL server database. Even though the time delay to effect this is specified as 5 minutes, most of the time the changes are reflected instantly.

Untitled1

 

Windows Azure – Migration of on premise SQL Server 2014 database to Azure

While planning to move your existing applications to Azure, one of the key considerations is how to move your existing on premise SQL server database to cloud. There is more than one way to accomplish this based on the specific requirement. For example, if you cannot afford down time between migration, the way forward is to use transactional replication setting your on premise database as publisher and your Azure SQL database as subscriber.

If real time migration isn’t a concern, you can go for exporting export your data (as .bacpac file that encapsulates a database’s schema and data) and importing it in your Azure database. Another solution is to deploy your on premise database directly to Azure as described below.

The feature to deploy database from SSMS to Azure was introduced in SQL Server 2012 and is available in SQL Server 2014 as well.

Deploy Database to Windows Azure SQL database

 

Note that the firewall settings for your Azure database server should be allowed to accept connections from your client IP.

Untitled21

The deployment wizard will let you to specify the connection and migration options as below.

Untitled17

Untitled18

Click on Finish to let the deployment/migration to complete

Untitled19

 

 

 

 

CLR Stored Procedures – Part 1: Highlights

New to .NET integration in SQL Server? Here is some useful information put together for you:

·         .NET integration is one of the key feature in SQL Server 2005

·         The CLR is hosted by SQL server thereby executing the stored procedures in SQL Server’s process space

·         CLR stored procedures uses ADO.NET objects (SqlCommand, SqlConnection, DataSet etc) to retrieve data from and write data into SQL Server

·         Microsoft.SqlServer.Server namespace provides the classes and attributes for supporting the managed CLR routines

·         An object called ‘SqlContext’ represents the connection instantiated by the CLR stored procedure and manages passing of parameters

·         ‘SqlContext’ is defined in the Microsoft.SqlServer.Server namespace

·         To create a CLR stored procedure

o       Create a Database Project in Visual Studio

o       Add a database reference

o       Add à Stored procedure to the project

·         The steps for creating the CLR stored procedure is explained in my blog here

·         The default entries created when you add a CLR stored procedure  using visual studio are shown below. (Stored procedure name is given as ‘GetSales’):

 

using System;

using System.Data;

using System.Data.SqlClient;

using System.Data.SqlTypes;

using Microsoft.SqlServer.Server;

 

 

public partial class StoredProcedures

{

    [Microsoft.SqlServer.Server.SqlProcedure]

    public static void GetSales()

    {

        // your code here

    }

};

 

·         The [Microsoft.SqlServer.Server.SqlProcedure] attribute specifies that this method is a stored procedure

·         The method GetSales() is static, as it needs to be invoked without instantiation

·         The namespace System.Data.SqlTypes provides all the data types that correspond to each of the SQL Server data types (Example: SqlTypes.SqlDateTime, SqlTypes.SqlInt32 etc.)

 

CLR Stored Procedure Parameters

 

·         The parameters are passed by specifying them in the stored procedure method. Example is given below

 

    [Microsoft.SqlServer.Server.SqlProcedure]

    public static void GetSales(SqlDateTime StartDate, SqlInt32 Days)

    {

        // your code here

    }

·         Output parameters can be passed by using the ref keyword

 

SqlPipe

 

·         SqlPipe is an object that allows us to send data or commands to be executed from a CLR routine back to the caller.

 

·         Send() method of the SqlPipe is used to send the data which has 3 overloads

            Send(string message)

            Send(SqlDataRecord record)

            Send(SqlDatareader reader)

     

 

·         Send() methods can be called any number of times during the course of a CLR stored procedure.

 

Example:

      command.CommandText = “Select * from MonthlySales”;

      SqlDataReader reader = command.EndExecuteReader();

      SqlContext.Pipe.Send(reader);

 

·         ExecuteAndSend() method of the SqlPipe does take care of the execution and subsequent return of the result back to the caller. So the last 2 lines of the above example can be replaced with:

SqlContext.Pipe.ExecuteAndSend(command);

 

 

·         CLR stored procedure can return multiple result sets and multiple messages or errors

 

Error Handling

 

·         Use Try{…} Catch{..}

·         To throw custom exceptions, use RAISEERROR

CTE – A T-SQL Solution to Recursive Queries

We often need to deal with hierarchies (tree structures) in SQL. Some common examples are employee hierarchies, parent-child hierarchies etc.

 

There is a clean solution to achieve it using CTE (Common Table Expressions) which is demonstrated below with a simple example.

 

The diagram below represents a parent-child hierarchy:

 

  

And the table “Persons” implements the above tree:

 

 

 

Here is the CTE approach (recursion) to retrieve all the descendants of a given person.

 

–To find all the descendants of Sam

 

DECLARE @parentID int;

SET @parentID = 1; –Sam

 

WITH Descendants (PersonID,ParentID)

AS

(

      SELECT PersonID, ParentID FROM Persons WHERE ParentID = @parentID

      UNION ALL

      SELECT Persons.PersonID, Persons.ParentID FROM Persons

            INNER JOIN Descendants ON Persons.ParentID = Descendants.PersonID

)

 

SELECT Persons.PersonID, Persons.Name FROM Persons

INNER JOIN Descendants

      ON Persons.PersonID = Descendants.PersonID

 

Using T-SQL PIVOT Clause

 

One feature in SQL Server 2005 that made my life easier many occasions is PIVOT clause in T-SQL. You will find it useful if you need to generate Cross-Tab summaries especially while generating reports. 

 

This feature allows you to convert a column’s values in table rows into columns. To see how it works, look at the following example

 

–Sales table rows

 

Select * from Sales

 

salesId     salesYear salesProduct salesQuantity

———– ——— ———— ————-

1           2004      Camera       100

2           2004      Memory       200

3           2004      HDD          1000

4           2005      Camera       150

5           2005      Memory       1200

6           2005      HDD          100

7           2006      Camera       300

8           2006      Memory       200

9           2006      HDD          400

 

 

With SQL Server 2000, a cross-tab summary to list sales-quantity-by-month can be generated by a query like this:

 

–Generate CrossTab (SQL Server 2000 style)

Select salesYear,

         Sum(case when salesProduct = ‘Camera’

                        then salesQuantity

                        else 0

                        end

              ) as ‘Camera’,

         Sum(case when salesProduct = ‘Memory’

                        then salesQuantity

                        else 0

                        end

              ) as ‘Memory’,

         Sum(case when salesProduct = ‘HDD’

                        then salesQuantity

                        else 0

                        end

              ) as ‘HDD’

From Sales

Group By salesYear

 

And you get result below:

 

salesYear Camera      Memory      HDD

——— ———– ———– ———–

2004      100         200         1000

2005      150         1200        100

2006      300         200         400

 

 

Using PIVOT

 

 

In SQL Server 2005, the above cross tab result be generated using the PIVOT clause which makes the query simple as shown below.

 

–SQL 2005, Use Pivot clause to get the cross tab summary

SELECT salesYear, [Camera],[Memory],[HDD]

FROM (SELECT salesYear, salesQuantity, salesProduct FROM Sales ) AS ProductSales

PIVOT (SUM(salesQuantity)

FOR salesProduct IN ([Camera],[Memory],[HDD])) AS SamplePivot

 

 

Output:

 

salesYear Camera      Memory      HDD

——— ———– ———– ———–

2004      100         200         1000

2005      150         1200        100

2006      300         200         400

 

 

Note that the results are grouped by ‘salesYear’ eventhough it is not a part of PIVOT statement. Check the result by removing the ‘salesYear’ from the query as follows:

 

SELECT [Camera],[Memory],[HDD]

FROM (SELECT salesQuantity, salesProduct FROM Sales ) AS ProductSales

PIVOT (SUM(salesQuantity)

FOR salesProduct IN ([Camera],[Memory],[HDD])) AS SamplePivot

 

Output:

 

Camera      Memory      HDD

———– ———– ———–

550         1600        1500

 

 

IIS Feature Requirement on Windows Vista for SQL Server 2005

When you try to install SQL Server 2005 on Windows Vista, you may get the following warning against IIS Feature requirement : “Microsoft Internet Information Services (IIS) is either not installed or is disabled

This indicates that some of IIS 7.0 components are missing in your IIS configuration. You can verify this by going to :

Start -> Control Panel ->Programs ->Turn Windows features on or off ->Internet Information Services

The following components in IIS are mandatory for SQL Server 2005 IIS feature requirement

  1. Static Content  (Under Common HTTP Features)
  2. Default Document (Under Common HTTP Features)
  3. HTTP Redirection (Under Common HTTP Features)
  4. Directory Browsing  (Under Common HTTP Features)
  5. ASP.Net (Under Application Development)
  6. ISAPI Extension (Under Application Development)
  7. ISAPI Filters (Under Application Development)
  8. Windows Authentication (Under Security)
  9. IIS Metabase (Under Web Management Tools)
  10. IIS 6 WMI  (Under Web Management Tools)

 IIS Feature Requirement for SQL Server 2005