SQL Migration to Azure Cloud

Copper Contributor

I have implemented a basic C# application connected with On premises SQL Server, I am going to migrate the same database and the data to Azure cloud using Microsoft Migration Tool, After the migration without touching the coding part I am debugging the same application (Changed the connections string only).

On premises sql database connected with SQL Server,

Gohulan_19-1582556429018.png

 

Gohulan_20-1582556429081.png

 

In the database I am running this query through management studio and I am getting the following results,

Gohulan_21-1582556429052.png

 

Gohulan_22-1582556429082.png

 

Same query I am deploying through my developed C# application, I am testing the connection with sql authentication credentials

Gohulan_23-1582556429053.png

 

Gohulan_24-1582556429053.png

 

String source = @”Data Source =” + textBox1.Text; Initial Catalog = CheckPostingDb; User Id =” + textBox2.Text;; Password=” + textBox3.Text;

SqlConnection con = new SqlConnection(source);

con.Open();

MessageBox.Show(“Db Connected”);

Once it’s succeeded, I’m running the same query through sqlcommand function in C#, got the same results in the text box

Gohulan_25-1582556429055.png

 

Gohulan_26-1582556429056.png

 

String sqlSelectQuery = “SELECT COUNT(*) AS MREQUESTS FROM MREQUESTS WHERE REQSTATE=1”;

SqlCommand cmd = new SqlCommand(sqlSelectQuery, con);

if (dr.Read())

{

textBox4.Text = Convert.ToString(dr[“MREQUESTS”])

}

con.Close();

Let’s migrate to Cloud

I have deployed a sample database in the Azure Cloud with SQL authentication,

Gohulan_27-1582556429057.png

 

Gohulan_28-1582556429084.png

 

It’s just a blank database and it doesn’t have any tables

Gohulan_29-1582556429058.png

 

Gohulan_30-1582556429085.png

 

Tried the same query here and returning with failed errors,

Gohulan_31-1582556429059.png

 

Gohulan_32-1582556429060.png

 

Start ab new project type as Migration

Gohulan_33-1582556429061.png

 

Gohulan_34-1582556429062.png

 

In this step I am specifying the source and target server details

In my scenario Source server is in localhost and target sql server is in Azure could

Source Server — localhost , Target Server — gohulan.database.windows.net

Gohulan_35-1582556429063.png

 

Gohulan_36-1582556429086.png

 

Select the Correct database from the source server to Migrate to cloud,

Gohulan_37-1582556429063.png

 

Gohulan_38-1582556429088.png

 

In the target server select the correct database from Azure cloud, in my Azure cloud I have only one database named CheckPostingDb

Gohulan_39-1582556429065.png

 

Gohulan_40-1582556429090.png

 

Once its’s connected I am going to select the objects or tables from the source database that I would like to migrate

In my testing environment I am selecting only one table, my table is MREQUESTS since I am targeting the results only from this table through my C# application.

Once the table is ticked, I have generated the SQL script

Gohulan_41-1582556429065.png

 

Gohulan_42-1582556429092.png

 

Once the script is generated, I am deploying the schema,

/******** DMA Schema Migration Deployment Script Script Date: 2/24/2020 12:50:55 PM ********/

/****** Object: Table [dbo].[MREQUESTS] Script Date: 2/24/2020 12:50:55 PM ******/

SET ANSI_NULLS ON

GO

SET QUOTED_IDENTIFIER ON

GO

IF NOT EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N’[dbo].[MREQUESTS]’) AND type in (N’U’))

BEGIN

CREATE TABLE [dbo].[MREQUESTS](

[ID] [bigint] IDENTITY(1,1) NOT NULL,

[RID] [uniqueidentifier] NOT NULL,

[ReqTime] [datetime] NOT NULL,

[ReqState] [tinyint] NOT NULL,

[RecordType] [int] NOT NULL,

[Data1] [bigint] NULL,

[ServiceID] [int] NULL,

[FirstRequestTime] [datetime] NULL,

[OfflinePosting] [bit] NULL,

[ServiceHostInfo] [nvarchar](80) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,

CONSTRAINT [PK_MREQUESTS] PRIMARY KEY CLUSTERED

(

[ID] ASC

)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON)

)

END

GO

IF NOT EXISTS (SELECT * FROM sys.indexes WHERE object_id = OBJECT_ID(N’[dbo].[MREQUESTS]’) AND name = N’AK_MREQUESTS_RID’)

CREATE UNIQUE NONCLUSTERED INDEX [AK_MREQUESTS_RID] ON [dbo].[MREQUESTS]

(

[RID] ASC

)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, SORT_IN_TEMPDB = OFF, IGNORE_DUP_KEY = OFF, DROP_EXISTING = OFF, ONLINE ​= OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON)

GO

IF NOT EXISTS (SELECT * FROM sys.indexes WHERE object_id = OBJECT_ID(N’[dbo].[MREQUESTS]’) AND name = N’IX_MREQUESTS_2')

CREATE NONCLUSTERED INDEX [IX_MREQUESTS_2] ON [dbo].[MREQUESTS]

(

[ReqTime] ASC,

[ReqState] ASC

)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, SORT_IN_TEMPDB = OFF, DROP_EXISTING = OFF, ONLINE ​= OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON)

GO

After I deployed the generated script in the previous step, it doesn’t give any errors or warnings.

Successfully Executed.

Once the script is migrated, I can see my table was created in the Azure cloud. But my query was returning with 0 results, it means that MREQUESTS table has been deployed but not the data yet.

Gohulan_43-1582556429066.png

 

Gohulan_44-1582556429094.png

 

I am migrating the data as well in my next step

Gohulan_45-1582556429067.png

 

Gohulan_46-1582556429095.png

 

Once after I started the migration it will start the process to send the data to cloud, time depends on the data capacity and the network speed.

Gohulan_47-1582556429068.png

 

Gohulan_48-1582556429068.png

 

Since my table doesn’t have huge data it finished with in short time without any warning or errors.

Gohulan_49-1582556429070.png

 

Gohulan_50-1582556429097.png

 

Running the same query in the Query Editor in Azure to check my data, it’s succeeded and returned with same value as on-premise query returns earlier.

Gohulan_51-1582556429070.png

 

Gohulan_52-1582556429071.png

 

Returning to my C# application and no changes made in the application but changing the connection string by changing the server name and sql authentication credentials,

Debugging the application to confirm it’s functioning properly, tested my connection with correct sql credentials

Gohulan_53-1582556429072.png

 

Gohulan_54-1582556429073.png

 

Wow the results as expected, means Migration is succeeded.

Gohulan_55-1582556429074.png

 

Gohulan_56-1582556429075.png

 

0 Replies