Wednesday, December 4, 2013

Migrate database to Windows Azure

Migrating database with table, store procedure, function, views from SQL server to Windows Azure is tedious job. When direct connection from Azure database to your MS SQL Express is not established then you can follow the below process:

1) Connect with SQL Server Management Studio 2012
2) Download SQL Database Migration Wizard (SQLAzureMW)
3) Convert your script from SQL Server to Windows Azure Compatiable
4) Run the script in Windows Azure


1) Connect with SQL Server Management Studio 2012


2) Download SQL Database Migration Wizard (SQLAzureMW)

http://sqlazuremw.codeplex.com/

Run SQLAzureMW
Fig 1.1: Home UI of SQLAzureMW



















3) Convert your script from SQL Server to Windows Azure Compatiable

4) Open the azure window and run the query over there.

Above steps are helpful when you can not directly connect with Azure database from your SQL server instance.


Tera Data: delete duplicate records

As I am from MS Sql background,could not think deleting duplicate records could be such cumbersome. It is not straight forward like MS SQL. In teradata, you will need help of temporary table to remove duplicates data from your table. My writing is only concerned deleting records from a single table, deleting records from more than one table joined by reference is different.


 --While you do group by and  count is more than one, you get duplicate records
 select StoreId, Smonth, ProdID, Sales from Paul_db.Test_sales_tbl group by 1,2,3,4 having count (*)>1

 --or
 select distinct * from Paul_db.Test_sales_tbl

 --Now need to delete the duplicate records, in tera data, first you move your data in a temporary table

--create temp table without copying data

   Create volatile table Tmp_Tst_sales2 as (select * from Paul_db.Test_sales_tbl) WITH NO DATA
 
 --The above code worked well and created the table structure

 --Now insert data into the table
    insert into Tmp_Tst_sales2 select distinct * from Paul_db.Test_sales_tbl
    select * from Tmp_Tst_sales2

--Unfortunately, above code can't insert the data.

  --So We do other way; create table with records
  drop table Tmp_Tst_sales
  create volatile table Tmp_Tst_sales as (select * from Paul_db.Test_sales_tbl) WITH DATA
  ON COMMIT PRESERVE ROWS;

  select * from Tmp_Tst_sales

  --delete the duplicate records from volatile table and then insert only distinct records
  delete from Tmp_Tst_sales


  insert into Tmp_Tst_sales select distinct * from edwwrk_U.Test_sales_tbl

--Now the above code works

  --So finally insert the distinct records from temp table to real table

  delete from edwwrk_U.Test_sales_tbl

  insert into  edwwrk_U.Test_sales_tbl select distinct * from Tmp_Tst_sales

  drop Tmp_Tst_sales

  -----Done---

Friday, March 15, 2013

Host your Web Application to Windows Azure

There are different ways you can host web application to Windows Azure. One of them are hosting application from Visual Studio. I have used VS 2012 to deploy the web application. This particular web application is using SQL Server 2008 as database.

When you have web application with database then first you should import database to Windows Azure. You will find more details about how you can import database to Azure :

http://msdn.microsoft.com/en-us/library/windowsazure/hh335292.aspx

From the above URL , you will find  "You must also create a container inside Blob storage for your BACPAC files by using a tool such as the Windows Azure Management Tool (MMC) or Azure Storage Explorer."

In my post, I wanted to avoid installing tool for creating the container since  I have seen Windows Azure Management Tool (MMC) is removed from codeplex and a little bit old.

So my process was like below:

1) Make a SQL script from my SQL Server
2) Run the query in Azure SQL
3) Download publishing Profile
4) Deploy from Visual studio 2012

1) Make a SQL script from my SQL Server
Make script from the database,
Make database Script


















2) Run the script in Azure SQL

First login to Azure account by clicking portal:





Second, find your database that you have created inside Azure










Third, find the MANAGE URL from tHE DASHBOARD 















Fourth, Login to the manage url and paste the script to the sql query 


Run Script in query window

After running the script you will find an error, you need delete ON [PRIMARY] from the script to avoid the error



3) Download publishing Profile

Login to Azure management portal and download the publishing profile for your website/Application.
Download publishing profile















4) Deploy/Publish from Visual studio 2012 to Azure

Open your project in VS 2012 and click publish button and then you will find option to import publishing profile option:



Import publishing profile in VS 2012













Follow the wizard and complete the publishing of the web application.

Publishing wizard


















Now you will be redirected to your website in Azure.