Migrating Databases to SQL Azure

Migrating Databases to SQL Azure

Hands-OnLab

MigratingDatabasestoSQLAzure

Labversion:2.0.0

Lastupdated:11/15/2018

Contents

Overview

Exercise 1: Moving an Existing Database to the Cloud

Task 1 – Provisioning the Destination SQL Azure Database

Task 2 – Generating a DDL Script

Task 3 – Executing the DDL Script against SQL Azure

Task 4 – Creating Migration Assistant Stored Procedures

Task 5 – Disabling Foreign Key Constraints

Task 6 – Disabling non-clustered Indexes

Task 7 – Using bcp to Export Table Data from your Source SQL Server Database

Task 8 – Using bcp to Import Table Data into your Destination SQL Azure Database

Task 9 – Rebuilding non-clustered Indexes

Task 10 – Enabling Foreign Key Constraints

Summary

Overview

SQLAzureDatabaseisbasedonMicrosoftSQLServer,whichmakesitrelativelyeasytomoveexistingSQLServerdatabasestoSQLAzure.Inthishands-onlab,youwilllearnhowtomoveaschemaanddatatoSQLAzurebyusingbruteforceScriptswithBCPcommand-linetooltocopydataintoandoutofSQLAzure.

Objectives

Inthishands-onlab,youwilllearnhowto:

  • UseSQLAzureasacloudhosteddatabaseplatformforyourapplications.
  • Youwilllearnhowto

◦Migrateanexistingdatabase(schemadata)

Prerequisites

Thefollowingisrequiredtocompletethishands-onlab:

  • SQLServerManagementStudio2008R2ExpressEdition

Exercises

Thishands-onlabincludesthefollowingexercises:

  1. Exercise 1: Moving an Existing Database to the Cloud

Estimatedtimetocompletethislab:30minutes.

Exercise1:MovinganExistingDatabasetotheCloud

Inthisexercise,youwillmoveanexistingon-premisesdatabasefromSQLServertothecloud.Youwilldothisbyusingthebruteforceapproach,usingscripts.

Task1–ProvisioningtheDestinationSQLAzureDatabase

InthisstepyouwillprovisionanemptySQLAzuredatabasethatyouwillusetomigratealloftheAdventureWorksDW2008R2databaseobjectscontainedinyourlocalSQLServerinstance.

  1. Navigate and signintotheWindows Azure Management PortalDatabaselink in the left paneandselecttheProjectNameassociatedwithyourSQLAzuresubscription.
  2. From the Server List, select the server you are going to use to perform the Lab and takenoteoftheinformationinthePropertiessection,including:
    Fully Qualified DNS name(e.g.myserver.database.windows.net)
    AdministratorLogin(e.g.mysqladminuser)
  3. IntheRibbonmenu,clickCreate.

Figure1

CreateDatabase

  1. Enterthefollowinginformation:
  2. Database name:AdventureWorksDW2008R2
  3. Edition:Web
  4. Maximum size:1GB

Note:Rightnow,themaindifferencebetweenWebandBusinessdatabaseeditionsisstorageandprice.Webeditiondatabasescanbeprovisionedforupto5GBofrelationalstorage.Businesseditiondatabasescanbeprovisionedforupto50GBofrelationalstorage.Webeditionissignificantlylessexpensiveforpaidsubscriptions.AsSQLAzureevolves,“enterprise”featureswillmostlikelybemonetizedbyrequiringBusinessedition.

Figure2

CreateDatabase

  1. ClickOK.
  2. ClickFirewallRulesbutton,andcheckthe“AllowotherWindowsAzureservicestoaccessthisserver”option.ThiswillallowotherservicesrunningintheAzureServicesenvironmenttointeroperatewithyourSQLAzureserver.

Figure3

FirewallRules

  1. Now,clicktheAddbutton,andenterthecorrectIPaddressrangetograntaccesstotheIPaddressesyouwillusetoconnecttoSQLAzure,forexample:
    Rule name:GrantAccessToAllAddresses
    IPrange start:0.0.0.0
    IPrange end:255.255.255.255

Figure4

FirewallRules–AddFirewallRule

Note:TheexampleshownheregrantsaccesstoallIPaddresses.GrantaccessonlytotheIPaddressesyouactuallyneedtoavoidunauthorizedusage.

Task2–GeneratingaDDLScript

InthisstepyouwillgenerateaDDLscriptcapableofrecreatingalloftheobjectsinyoursourcedatabase.DDLstandsfor“DataDefinitionLanguage”whichisshorthandforthevariousTransact-SQLcommandsusedtocreatedatabaseobjectssuchastables,viewsandstoredprocedures.

  1. LaunchSQLServerManagementStudio.
  2. ConnecttotheSQLServerinstancethatyouinstalledtheAdventureWorksDW2008R2databaseon.
  3. ExpandtheDatabasesnodeinObjectExplorer,right-clickAdventureWorksDW2008R2andselectTasksGenerateScripts.
  4. IfthereappearsanIntroductiondialog,clickNext.
  5. IntheChooseObjectsdialog,choosethe“Selectspecificdatabaseobjects”option,thenchecktheTables,ViewsandUser-DefinedFunctionsoptions.

Figure5

GenerateScripts–ChooseObjects

Note:AdventureWorksDW2008R2containsaDDLTrigger.SQLAzuredoesnotcurrentlysupportDDLTriggerssodonotselectthisoption.

  1. ExpandtheTablesnodeanduncheckthefollowingtables:
  2. AdventureWorksDWBuildVersion
  3. DatabaseLog
  4. FactFinance

Figure6

GenerateScripts–ChooseObjects

Note:SQLAzurerequiresalltablestohaveaclusteredindex.Thesetablesdonothaveclusteredindexes,andtheyarenotessentialforthisparticularsampledatabase.

  1. IntheChooseObjectsdialog,clickNext.
  2. IntheSetScriptingOptionsdialog,clicktheAdvancedbuttonandmakethefollowingchanges,thenclickOK:

Figure7

GenerateScripts–ChooseObjects

  1. SettheGeneral“Scriptforthedatabaseenginetype”optionto“SQLAzureDatabase”
  2. SettheTable/ViewOptionsScriptIndexesoptiontoTrue

Figure8

GenerateScripts–ChooseObjects

  1. IntheSetScriptingOptionsdialog,settheoutputtypeto“Savescriptstoaspecificlocation”,andchoosethe“Savetonewquerywindow”option,thenclickNext.

Figure9

GenerateScripts–SetScriptingOptions

  1. ReviewtheinformationintheSummarydialog,thenclickNext.Atthispointthewizardwillinterrogatetheschemaofthesourcedatabaseandgenerateascripttorebuildalloftheobjectsinthedatabaseintoanewquerywindow.
  2. ReviewtheinformationintheSaveorPublishScriptsdialogthenclickFinish.

Figure10

GenerateScripts–SaveorPublishScripts

  1. ReturntoSQLServerManagementStudioandreviewthescriptthatwasgenerated.

Task3–ExecutingtheDDLScriptagainstSQLAzure

InthisstepyouwillexecutetheDDLScriptyouhave generatedagainsttheAdventureWorksDW2008R2databaseyouprovisionedinSQLAzure.Oncecompleted,allofthetables,views,storedproceduresandotherobjectsfromyoursourcedatabasewillexistinyournewlyprovisionedSQLAzuredatabase.Atthispointtherewillbenodatainthetables.

  1. Right-clickthequerywindowandselectConnectionChangeConnection.

Figure11

GenerateScripts–ChooseObjects

  1. ThiswillbringuptheConnecttoDatabaseEnginedialogsoyoucanconnecttoyournewlycreatedAdventureWorksDW2008R2databaseinSQLAzure.Entertheconnectioninformationyounotedearlier,forexample:
  2. Servername:[myserver.database.windows.net]
  3. Authentication:SQLServerAuthentication
  4. Login:[mysqladminuser@myserver]
  5. Password:[mysqladminuserpassword]

Figure12

ChangeConnection

Note:YoucanselecttheRememberPasswordoptiontoavoidhavingtoretypeyourpasswordeachtimeyouconnect

  1. ClickConnect.
  2. ChangetheactivedatabasetoAdventureWorksDW2008R2usingtheAvailableDatabasesdrop-downlistboxinthetoolbar.

Note:Alwayscheckyourcurrentdatabasecontextbeforerunningascripttoavoidmakingchangestothewrongdatabase.

Figure13

ChangeConnection

  1. ExecutethequerybypressingF5orbyselectingtheQueryExecutemenuoption.
  2. Reviewthestatusinformationatthebottomofthequerywindowandverifythatthequeryexecutedsuccessfully.
  3. UsingObjectExplorer,connecttoyourSQLAzureserverwiththeappropriatecredentials.ExpandtheDatabasesnode,thenexpandtheTablesnode inside AdventureWorksDW2008R2 database.ReviewthetablesthatwerecreatedandcomparethemtothoseinyourlocalSQLServerinstance.

Figure14

Reviewgeneratedtables

Task4–CreatingMigrationAssistantStoredProcedures

InthisstepyouwillcreatetwostoredproceduresintheSQLAzureAdventureWorksDW2008R2databasethatwillsimplifytheprocessofmigratingyourdata.

TheSetForeignKeyEnabledStatusstoredprocedurewillbeusedtodisableforeignkeyconstraintsduringdatamigration.Thiswillpermityoutoloadyourtablesinwhateverorderisconvenientwithoutregardtoprimarykey/foreignkeyrelationships.Youwillrunthisstoredprocedureagainwhendatamigrationiscompletetore-enablealloftheforeignkeyconstraints.

TheSetIndexEnabledStatusstoredprocedurewillbeusedtodisableallnon-clusteredindexesduringdatamigration.Thisspeedsupthedataloadingprocess.Youwillrunthisstoredprocedureagainwhendatamigrationiscompletetorebuildallofthenon-clusteredindexes.

Usethefollowingproceduretocreatethestoredprocedures.ThesourcecodefortheCreateMigrationAssistantProcedures.sqlTransact-SQLscriptfilereferencedintheinstructionsisincludedattheendofthissection.Toexecutethescript,youwillusethesqlcmdutility,whichisauseful command-promptutilityforperformingbatchoperationsagainstSQLServerandSQLAzure.

  1. Openacommandpromptandchangethecurrentdirectorytothe[labPath]\Assetsfolder.
  2. ExecutethefollowingcommandusingtheappropriateconnectiondetailsforyourSQLAzureserver:

CommandPrompt

-b

Note:The–bparameterensuresthatsqlcmdwillreturnanon-zeroerrorcodeifanerroroccurswhenrunningascript.Thiscanbeusedtoterminateabatchfileifanerroroccurs.

  1. LaunchSQLServerManagementStudio,connecttoyourSQLAzureserver,andexpandtheDatabasesAdventureWorksDW2008R2ProgrammabilityStoredProceduresnode.VerifythattheSetForeignKeyEnabledStatusandSetIndexEnabledStatusstoredproceduresareinthelist.

Figure15

Reviewgeneratedstoredprocedures

Thesetwostoredproceduresiteratethroughobjects(foreignkeysandindexes)insystemcatalogviews.Foreachiteration,these objectsdynamicallyconstructaTransact-SQLcommandtoenableordisabletheobject,and thenthey executethedynamicallyconstructedcommand.

Task5–DisablingForeignKeyConstraints

InthisstepyouwillusetheSetForeignKeyEnabledStatusstoredprocedurecreatedintheprevioussteptodisablealloftheforeignkeyconstraintsintheAdventureWorksDW2008R2SQLAzuredatabase.Thiswillmakeiteasiertoloaddatabyremovingtheneedtodoitinaspecificordertoavoidviolatingforeignkeyconstraints.Youwillre-enabletheforeignkeyconstraintslaterafteryouarefinishedloadingyourdata.

Notethatbydefault,bcpwillbypassconstraintchecking,howeveritisstillusefultodothisifyouareusingothermechanismstoloadyourdata,suchasscriptfilesorsomeotherapplication.

  1. Openacommandprompt.
  2. ExecutethefollowingcommandusingtheappropriateconnectiondetailsforyourSQLAzureserver:

CommandPrompt

sqlcmd-Smyserver.database.windows.net-Umysqladminuser@myserver-Pmysqladminuserpassword-dAdventureWorksDW2008R2-Q"EXECUTE[dbo].[SetForeignKeyEnabledStatus]0" -b

Note:Usethe–Qparametertosendasinglecommandinsteadofexecutingascriptfile.

Task6–Disablingnon-clusteredIndexes

InthisstepyouwillusetheSetIndexEnabledStatusstoredprocedurecreatedpreviouslytodisableallofthenon-clusteredindexesintheAdventureWorksDW2008R2SQLAzuredatabase.Thiswillimprovetheperformanceofdataloadingbyavoidingincrementalindexrebuildsduringtheloadingprocess.Youwillre-buildthenon-clusteredindexeslaterafteryouarefinishedloadingyourdata.Notethatyouwillnotdisableclusteredindexessincethedataisloadedinclusteredindexorder,andSQLAzurerequiresclusteredindexesonalltables.

  1. Openacommandprompt.
  2. ExecutethefollowingcommandusingtheappropriateconnectiondetailsforyourSQLAzureserver:

CommandPrompt

sqlcmd-Smyserver.database.windows.net-Umysqladminuser@myserver-Pmysqladminuserpassword-dAdventureWorksDW2008R2-Q"EXECUTE[dbo].[SetIndexEnabledStatus]0" -b

Task7–UsingbcptoExportTableDatafromyourSourceSQLServerDatabase

Inthisstepyouwillusethebcp(bulkcopyprogram)command-lineutilitytoexportdatafromyoursourceSQLServerdatabase.Whenyoucompletethisstep,youshouldhaveone“native”fileforeachofthetables.

  1. OpentheExportTableData.cmdfilefromthe[labPath]\Assetsfolder.
  2. Replacethefollowingvariablesatthebeginningofthedocumentwiththeappropriatevalues:
  3. SourceSqlServerName:LocalSQLServername
  4. SourceSqlDbName:LocalSQLServerdatabasename

Figure16

ExportTableData-Replacescript’svariables

  1. ClosetheExportTableData.cmdfile.
  2. Openacommandpromptandchangethecurrentdirectorytothe[labPath]\Assetsfolder.
  3. ExecutetheExportTableData.cmdscript.

Internallythescriptwillexecutethefollowingcommandonceforeachtable:

CommandPrompt

bcpdbo.MyTableNameoutMyTableName.dat-n-SMySqlServerName-T

Note:Usethe–nparametertousethe“native”fileformatforexport. Thenativeformatimprovesperformanceonimportbyavoidingunnecessaryconversions.Usethe–TparametertoconnecttoSQLServerusingyourWindowscredentials.

  1. Examinetheoutputandverifythatthefileswerecreated.

Task8–UsingbcptoImportTableDataintoyourDestinationSQLAzureDatabase

Inthisstepyouwillusethebcpcommand-lineutilitytoimportdatafroma“native”fileintoyourdestinationSQLAzuredatabase.Whenyoucompletethisstep,allofyourdatashouldbeloaded.

  1. OpentheImportTableData.cmdfilefromthe[labPath]\Assetsfolder.
  2. Replacethefollowingvariablesatthebeginningofthedocumentwiththeappropriatevalues:
  3. DestSqlServerName:[myserver.database.windows.net]
  4. DestSqlDbName:SQLAzureServerdatabasename
  5. DestSqlUserName:[mysqladminuser@myserver]
  6. DestSqlUserPassword:SQLAzureAccountLoginpassword

Figure17

ImportTableData-Replacescript’svariables

  1. ClosetheImportTableData.cmdfile.
  2. Openacommandpromptandchangethecurrentdirectorytothe[labPath]\Assetsfolder.
  3. ExecutetheImportTableData.cmdscript.

Internallythescriptwillexecutethefollowingcommandonceforeachtable::

CommandPrompt

bcpdbo.MyTableNameinMyTableName.dat-n-Smyserver.database.windows.net-Umysqladminuser@myserver-Pmysqladminuserpassword-E

Note:Bcpisverypickyaboutparameterformats.Donotuseaspaceafterthe–Uand–Pparameters.

Note:BecarefulwhenimportingdataintocolumnsthatusetheIDENTITYproperty. Usethe–EparametertousevaluesinthedatafileforIDENTITYcolumnsinsteadofgeneratingnewidentityvaluesduringimport.Ifyoudo notdothisyouriskbreakingforeignkeyrelationships.

  1. Examinetheoutputtoensurethatalldatawassuccessfullycopiedintothedestinationtable.

Task9–Rebuildingnon-clusteredIndexes

InthisstepyouwillusetheSetIndexEnabledStatusstoredproceduretorebuildthenon-clusteredindexesyoudisabledinstep6.It’sbesttodothisinoneshotafterloadingallofyourdata.

  1. Openacommandprompt.
  2. ExecutethefollowingcommandusingtheappropriateconnectiondetailsforyourSQLAzureserver:

CommandPrompt

sqlcmd-Smyserver.database.windows.net-Umysqladminuser@myserver-Pmysqladminuserpassword-dAdventureWorksDW2008R2-Q"EXECUTE[dbo].[SetIndexEnabledStatus]1"-b

Task10–EnablingForeignKeyConstraints

InthisstepyouwillusetheSetForeignKeyEnabledStatusstoredproceduretoenabletheforeignkeyconstraintsyoudisabledinstep5.

  1. Openacommandprompt.
  2. ExecutethefollowingcommandusingtheappropriateconnectiondetailsforyourSQLAzureserver:

CommandPrompt

sqlcmd-Smyserver.database.windows.net-Umysqladminuser@myserver-Pmysqladminuserpassword-dAdventureWorksDW2008R2-Q"EXECUTE[dbo].[SetForeignKeyEnabledStatus]1"-b

Summary

WhenworkinghandsonwithSQLAzuremigrating,existingSQLServerdatabasesisoneofthefirstthingscustomersexperimentwith.Inthisexercise,youworkedhandsonwiththebruteforceapproach.

Top View