Database Integration With SQL Azure

View: 732    Dowload: 0   Comment: 0   Post by: hanhga  
Author: none   Category: SQL Server   Fields: Education - Schools

0 point/1 review File has been tested

We can integrate our database with SQL Azure by using the SQLAzureMW tool that is SQL Azure plugin for migrating the database from our local database to the Windows Azure cloud.

Introduction

Windows Azure SQL Database does not support all of the features and data types found in SQL Server. Analysis Services, Replication, and Service Broker are not currently provided as services on the Windows Azure platform.

Windows Azure SQL Database performs the physical administration, any statements and options that attempt to directly manipulate physical resources will be blocked, such as Resource Governor, file group references, and some physical server DDL statements. It is also not possible to set server options and SQL trace flags or use the SQL Server Profiler or the Database Tuning Advisor utilities.

Initially, what we did was that we tried to generate the SQL scripts for our sample database, i.e., PhoenixDb from the task option available in the SQL Server Management Studio. So therein, we had the option to generate scripts for SQL Azure.

But the problem that we faced while running that SQL file in the SQL Azure was that these table had indexes which were not clustered. So there was a problem while inserting data into those set of tables.

Hence, the result was that we had the tables structure ready in the SQL Azure along with the procedures but still there was no data in the SQL Azure db.

So we resolved to a new way to generate the replica of the database on SQL Azure.

Following are the set of steps we followed.

Step 1: Go to SQLAzureMW.exe file and then the following window comes up:

Select Analyse /Migrate option and click on Next.

Step 2: Now in this step, you need to connect to the database server where your database is currently residing, for example in our case, it would be in the sandbox server.

Step 3: Once the server is connected, we need to select the database that we need to copy to SQL Azure in our case, it is PhoenixSample and click on Next.

 

Step 4: After this, it will ask the user to select the option for what database objects need to be generated for the scripts. You can select all the tables and procedures or go with a few of them.

At the same time, you can click on the advanced tab at the bottom of the window to set the settings as per your needs. You can see the target Server as SQL Azure present over there.

Step 5: Now the window will come up where you need to review all what you have selected as the target database objects to be scripted. Click on the next once you have reviewed your selected database objects.

Step 6: Now you will see that the script generation process has started. So you can see two tabs in the result summary window, one is the Result tab and other is the Script tab.

Once the script is generated, the following screen will show up:

Step 7: Now once the scripts have been generated, you need to set up the Target SQL Azure Server where these generated scripts needs to run.

Herein, you need to mention the following things:

  1. Server Name (It should be in the following format ServerName.database.windows.net)
  2. The username (It should be in the following format Username@ServerName)
  3. The target database

You should have already created the database from the manage azure portal of the same name.

Step 8: Click on connect to proceed. You will land into a new page where it will ask you for confirmation whether you need to run the scripts on the target server or not. Once you click on Yes, the scripts will automatically execute on the SQL Azure server.

So once this is done, you need to verify whether all your data is there in the target SQL Azure Server.

Verifying whether the Complete Data is there on the Targeted SQL Azure Server

In order to do so, we need to make sure of two things:

  1. Total number of tables and Stored Procedure matches in both the places.
  2. The total number of records in the tables on both the servers should match up. Once this is done, our database is ready to move on.

We can run the following script on the SQL server to know the number of entries in each table in SQL Server Db.

CREATE TABLE #counts
(
    table_name varchar(255),
    row_count int
)

EXEC sp_MSForEachTable @command1='INSERT #counts (table_name, row_count) SELECT ''?'', COUNT(*) FROM ?'
SELECT table_name, row_count FROM #counts ORDER BY table_name, row_count DESC

Similarly, we can run the following script in the SQL Azure db to know the number of entries in each table:

select t.name ,s.row_count from sys.tables t
join sys.dm_db_partition_stats s
ON t.object_id = s.object_id
and t.type_desc = 'USER_TABLE'
and t.name not like '%dss%'
and s.index_id = 1

Once this is done, we can move ahead with the deployment scenarios.

I will illustrate the use of web and worker roles in my next article.

Database Integration With SQL Azure

Database Integration With SQL Azure Posted on 03-04-2014  We can integrate our database with SQL Azure by using the SQLAzureMW tool that is SQL Azure plugin for migrating the database from our local database to the Windows Azure cloud. 5/10 732

Comment:

To comment you must be logged in members.

Files with category

  • Insufficient access rights error when doing backup in SQL Server

    View: 503    Download: 0   Comment: 0   Author: none  

    Insufficient access rights error when doing backup in SQL Server

    Category: SQL Server
    Fields: none

    2.25/2 review
    All of sudden SQL Server Management studio gives me the following error message when I'm trying to browse for the backup file location.

  • Migrate MySQL to Microsoft SQL Server

    View: 2591    Download: 0   Comment: 0   Author: none  

    Migrate MySQL to Microsoft SQL Server

    Category: SQL Server
    Fields: none

    0/2 review
    This article describes a few simple steps in order to migrate MySQL into Microsoft SQL Server 2005. The technique is very easy, but useful if you plan to move your data from MySQL and upgrade it finally to a Microsoft SQL Server environment.

  • COUNT of DISTINCT Rows in SQL Server

    View: 1424    Download: 0   Comment: 0   Author: none  

    COUNT of DISTINCT Rows in SQL Server

    Category: SQL Server
    Fields: Other

    0/4 review
    SQL Server does not support COUNT(DISTINCT *). For example, the below query fails.

  • Finding SQL Servers on the Network

    View: 760    Download: 0   Comment: 0   Author: none  

    Finding SQL Servers on the Network

    Category: SQL Server
    Fields: Other

    0/2 review
    I am a TSQL fanatic. The programs I code are highly dependent upon MS SQL stored procedures. I pay for this love of TSQL when a major overhaul of the system is necessary. Sometimes my code needs to be updated heavily in two places; client and...

  • SQL Server and Database Enumerator

    View: 745    Download: 0   Comment: 0   Author: none  

    SQL Server and Database Enumerator

    Category: SQL Server
    Fields: Other

    0/2 review
    This sample helps you to enumerate the list of SQL Servers and the databases and languages that a particular SQL server has. The class CSQLInfoEnumerator encapsulates this functionality into it.

  • Dynamic Management Views [DMV] – A SQL Server 2005 Feature

    View: 749    Download: 0   Comment: 0   Author: none  

    Dynamic Management Views [DMV] – A SQL Server 2005 Feature

    Category: SQL Server
    Fields: Other

    0/2 review
    The DMVs; newly introduced in SQL Server 2005 gives the database administrator information about the current state of the SQL Server machine. These values will help the administrator to diagnose problems and tune the server for optimal performance....

  • Table Value Parameter in SQL Server 2008

    View: 673    Download: 0   Comment: 0   Author: none  

    Table Value Parameter in SQL Server 2008

    Category: SQL Server
    Fields: Computers - Technology

    0/2 review
    One of the fantastic new features of SQL Server 2008 is the Table value parameter. In previous versions of SQL Server, there wasn’t a native way to pass a table to a Stored Procedure or functions. The usual workaround was to pass a large varchar or...

  • SQL Server DO's and DONT's

    View: 657    Download: 0   Comment: 0   Author: none  

    SQL Server DO's and DONT's

    Category: SQL Server
    Fields: Computers - Technology

    0/0 review
    So, you are now the leader of a SQL Server based project and this is your first one, perhaps migrating from Access. Or maybe you have performance problems with your SQL Server and don't know what to do next. Or maybe you simply want to know of some...

 

File suggestion for you

File top downloads

logo codetitle
Codetitle.com - library source code to share, download the file to the community
Copyright © 2015. All rights reserved. codetitle.com Develope by Vinagon .Ltd