Migrating a Database Schema to SQL Azure

Document Sample
Migrating a Database Schema to SQL Azure Powered By Docstoc
					Demo Script
Migrating a Database Schema to SQL Azure
Lab version:    1.0.3
Last updated:   10/20/2011
CONTENTS

OVERVIEW ................................................................................................................................................... 3
 Key Messages ........................................................................................................................................... 3
   Key Technologies ...................................................................................................................................... 3
   Time Estimates ......................................................................................................................................... 4

SETUP AND CONFIGURATION .................................................................................................................. 4
       Task 1 –Running Dependency Checker ................................................................................................. 4
       Task 2 – Ensuring that the HolTestDB and HoLTestUser are created and configured ......................... 5
       Task 3 – Ensuring that the AdventureWorksLT2008 Database is attached to the local SQL Server .... 5

DEMO FLOW ................................................................................................................................................ 5

OPENING STATEMENT............................................................................................................................... 6

STEP-BY-STEP WALKTHROUGH .............................................................................................................. 7
  Export a Script via SQL Server Management Studio ................................................................................ 7
   Examine Unsupported Features ............................................................................................................. 12
   Execute a Pre-Prepared Script ............................................................................................................... 16

SUMMARY .................................................................................................................................................. 16
Overview
This document provides setup documentation, step-by-step instructions, and a written script for showing a demo of SQL Azure. This document
can also serve as a tutorial or walkthrough of the technology. In this demo, you will look at how to take an existing SQL Server schema and
migrate it to SQL Azure using Adventure Works 2008 Light. For additional demos of the Windows Azure platform, please visit
http://www.azure.com.



         Note: In order to run through this demo, you must have a SQL Azure account. For more information on how to purchase an account,
         visit the SQL Azure Portal at http://www.microsoft.com/windowsazure/sqlazure/.



Key Messages
In this demo you will see three key things:
       1. SQL Azure supports a subset of SQL Server features
       2. We can migrate a database to SQL Azure by generating a script of an existing database. There are a number of options that we can
          set to assist when exporting the script.
       3. We will generally need to make some modifications to the script by hand.




Key Technologies
This demo uses the following technologies:
       1. SQL Azure
       2. SQL Server 2008 R2 Management Studio
Time Estimates
       Estimated time for setting up and configuring the demo: 1 min
       Estimated time to complete the demo: 10 min




Setup and Configuration
The setup and configuration for this demo involves the following tasks:

       Run the dependency checker
       Ensure that the HolTestDB and HoLTestUser are created and configured

       Ensure that the AdventureWorksLT2008 database is attached to your local SQL Server


Task 1 –Running Dependency Checker
This demo does not have any advanced configuration requirements. You simply need to have the prerequisites installed and have an account
for SQL Azure. For more information on how to purchase an account, visit the SQL Azure Portal at
http://www.microsoft.com/windowsazure/sqlazure/.
The following steps describe how to run the Dependency Checker utility included with the demo to verify that you have the prerequisite
components. You can skip this exercise if you are confident that the prerequisites are properly installed and configured.
     1. Open a Windows Explorer window and browse to the demo’s Source\Script folder.
     2. Double-click the Dependencies.dep file in this folder to launch the Dependency Checker tool and install any missing prerequisites.
     3. If the User Account Control dialog is shown, confirm the action to proceed.
         Note: This process may require elevation. The .dep extension is associated with the Dependency Checker tool during its installation.
         For additional information about the setup procedure and how to install the Dependency Checker tool, refer to the Setup.docx
         document in the Assets folder of the training kit


Task 2 – Ensuring that the HolTestDB and HoLTestUser are created and configured
       You should ensure that you have followed the steps in the Demo scripts for 'Connecting to SQL Azure' and ' Managing Logins and
       Security in SQL Azure'. Specifically you need to have created the HoLTestDB database and HolTestUser login and user account and you
       must have granted the role db_owner to the HoLTestUser login.

Task 3 – Ensuring that the AdventureWorksLT2008 Database is attached to the local SQL Server
       Ensure that you have attached the AdventureWorksLT2008 database to your local SQL Server before beginning. This can be found here:
       http://www.codeplex.com/MSFTDBProdSamples
       You should download and execute the appropriate SQL2008.AdventureWorks_all_Databases.<ProcessorArchitecture>.msi for your
       processor architecture- x86, x64 or ia64.




Demo Flow
The step-by-step guide in this document follows the following demo flow/outline:
        Figure 1
        Demo Flow




Opening Statement
Many users will want to take an existing SQL Server database running on premise or with a hosting partner and migrate this into the cloud. A
simple way to achieve this is by exporting the schema as an SQL script and then executing that script against SQL Azure.
Because SQL Azure supports a subset of features found in SQL Server we will typically have to make some modifications to the script before we
are able to successfully execute it in the cloud.



Step-by-Step Walkthrough
This demo is composed of the following segments:
         1. Export a Script via SQL Server Management Studio
         2. Examine Unsupported Features
         3. Execute a Pre-Prepared Script

Export a Script via SQL Server Management Studio
In this first segment, we will export the existing schema by generating a script from SQL Server Management Studio.

Action                                      Script                                     Screenshot

1. Open SQL Server Management                  We’re going to be using SQL Server
   Studio (SSMS) Start > All Programs >         2008 R2 Management studio to work
   Microsoft SQL Server 2008 R2                 with SQL Azure today.
   November CTP > SQL Server
   Management Studio
2. Connect to your local instance where      In this demo we’re going to look at
   you have the AdventureWorksLT2008          migrating the AdventureWorksLT208
   database attached.                         database into SQL Azure.
                                             Because this process does involve a
                                              reasonable amount of hand editing of
                                              our database script we will use some
                                              pre-edited components at the end,
                                              but, we’ll also examine some of the
                                              techniques used to build the final
                                              script by looking at the unsupported
                                              features.




3. Right click the                           We’ll start by generating a script for
   AdventureWorksLT2008 database              the AdventureWorksLT2008
   and select Tasks > Generate Scripts        database.
4. Click Next
5. Select Script entire database and all
   database objects.
6. Click Next
7. Select Save scripts to a specific
   location.
8. Select Save to new query window.
9. Click Advanced button                      We’ll largely use the default script
10. Use the default script options apart       options but there are some key option
    from the following.                        flags that we need to change for a
11. Convert UDDTs to base types: True          SQL Azure ready export.
12. Script extended properties: False
13. Script USE DATABASE: False                SQL Azure does not support User
14. Click OK                                   Defined Data Types. We’ll set the
15. Click Next                                 option to convert any user defined
                                               types into their underlying base types.
                                              SQL Azure does not support
                                               extended properties. Therefore we do
                                               not need to script out these properties
                                              SQL Azure does not support the USE
                                               DATABASE commend for changing
                                               database context so we’ll choose not
                                               to script that out..
16. Press Next                                  Now we can watch as SQL Server
17. Press Finish                                 generates the script for our database.




Examine Unsupported Features
In this segment, we will take a look at a selection of statements that are not in the subset of SQL Azure supported functionality.

Action                                       Script                                       Screenshot

                                                Now that we’ve got our script
                                                 generated let’s take a look at a
                                                 selection of statements that are not
                                                 in the subset of SQL Azure
                                                 supported functionality.
                                                When taking this approach for your
                                                 own schema you’ll need to actually
                                                 change the script as you go along,
                                                 but, for our purposes today we’ll
                                 open a pre-edited script shortly.

18. Press Ctrl-F                When we set the option about to
19. Search for CREATE TYPE       convert UDDTs to base types, it
20. Press Find Next              ensured that all of our table
                                 definitions and so forth used the
                                 base types. Nevertheless, our User
                                 Defined Types were still generated.
                                Because SQL Azure does not
                                 support UDDTs we’d need to remove
                                 all of the type definitions from the
                                 script.
21. Press Ctrl-F           Because SQL Azure takes care of all
22. Find ON [PRIMARY]       of our physical storage in the cloud,
23. Press Find Next.        it does not support specifying
                            filegroups.
                           We’d need to modify all the object
                            creation statements that explicitly
                            specify the Filegroup..
24. Press Ctrl-F                  SQL Azure does not support the
25. Find NOT FOR REPLICATION       NOT FOR REPLICATION column
26. Press Find Next                constraint so we would need to
                                   remove that from our table
                                   definitions.




27. Press Ctrl-F                  There are a number of options on
28. Find PAD_INDEX                 indexes that are not supported in
29. Press Find Next                SQL Azure. You can find these
                                   specified in the SQL Azure
                                   documentation.
                                  PAD_INDEX is just one of these
                                   unsupported statements.
                                  There are a number of other
                                   supported statements in the
                                   generated script. The Hands on lab
                                   on Migrating a Database to the
                                   Cloud will discuss all of these
                                   supported statements and is well
                                   worth completing.
                                  There are a number of other
                                   unsupported statements that need to
                                   be modified. These include things
                                   like
                                                      a. The removal of unsupported
                                                         Windows logins
                                                      b. The removal of certain
                                                         unsupported XML features
                                                      c.   Certain tables need
                                                           clustered indexes added



Execute a Pre-Prepared Script
In this segment, we will execute a prebuilt script that has had all the un-supported features modified already.

Action                                       Script                                          Screenshot

30. Click Change Connection.                    We’ll cheat a little now and use a
                                                 pre-prepared script.

31. Login to the HoLTestDB                      It’ll take a while to execute this script
                                                 so we’ll come back to it later in the
32. Open the
                                                 presentation.
    AdventureWorks2008LT_Azure.sql
    File from the Assets\queries folder.
33. Execute the Script by pressing F5




Summary
In this demo we’ve examined an approach for moving an existing SQL Server Schema to SQL Azure. We saw some of the key options to set when
generating the script in SQL Server Management Studio and we then looked at the unsupported features that required editing.
Finally we executed a pre-prepared script to generate both the schema and data in our SQL Azure database.

				
DOCUMENT INFO
Shared By:
Categories:
Tags:
Stats:
views:6
posted:10/21/2011
language:English
pages:16