{"id":4142,"date":"2023-11-04T23:14:05","date_gmt":"2023-11-04T23:14:05","guid":{"rendered":"http:\/\/localhost:10003\/migrating-a-sql-server-database-to-azure-sql-database\/"},"modified":"2023-11-05T05:47:59","modified_gmt":"2023-11-05T05:47:59","slug":"migrating-a-sql-server-database-to-azure-sql-database","status":"publish","type":"post","link":"http:\/\/localhost:10003\/migrating-a-sql-server-database-to-azure-sql-database\/","title":{"rendered":"Migrating a SQL Server database to Azure SQL Database"},"content":{"rendered":"

Introduction<\/h1>\n

Many businesses are moving their on-premises infrastructures to the cloud to take advantage of the scalability, flexibility, and cost-effectiveness of cloud services. As a result, migrating databases to the cloud has become a common task for many IT professionals. Microsoft Azure provides a cloud-based relational database service called Azure SQL Database, which is a fully managed service that meets industry standards for security, compliance, and availability. In this tutorial, we will walk through the steps to migrate an on-premises SQL Server database to Azure SQL Database.<\/p>\n

Prerequisites<\/h1>\n

Before we begin, there are a few prerequisites that must be taken care of:<\/p>\n

    \n
  1. A Microsoft Azure account.<\/li>\n
  2. The latest version of SQL Server Management Studio (SSMS) installed on the machine that will be used for the migration.<\/li>\n
  3. The latest version of SQL Server Data Tools (SSDT) installed on the machine that will be used for the migration.<\/li>\n
  4. Firewall rules on the on-premises SQL Server must allow connectivity from the machine that will be used for the migration to the SQL Server instance.<\/li>\n<\/ol>\n

    Step 1: Prepare the Azure SQL Database<\/h1>\n

    The first step is to prepare the Azure SQL Database that will host the migrated database.<\/p>\n

      \n
    1. Log in to the Azure portal and navigate to the SQL databases page.<\/li>\n
    2. Click the “+ Add” button to create a new database.<\/li>\n
    3. Provide a name for the new database, select the subscription, resource group, and location for the database, and set the pricing tier.<\/li>\n
    4. Click “Review +Create”, review the settings, and then click “Create” to create the new database.<\/li>\n<\/ol>\n

      Step 2: Generate the Azure SQL Database Schema<\/h1>\n

      The next step is to generate the schema of the on-premises SQL Server database as a SQL script that can be executed on the Azure SQL Database.<\/p>\n

        \n
      1. Open SQL Server Management Studio and connect to the on-premises SQL Server.<\/li>\n
      2. Right-click on the database you want to migrate and select “Tasks” > “Generate Scripts”.<\/li>\n
      3. In the “Introduction” screen, click “Next”.<\/li>\n
      4. In the “Choose Objects” screen, select “Select specific database objects” and choose the tables, views, and stored procedures that you want to migrate. Click “Next”.<\/li>\n
      5. In the “Set Scripting Options” screen, set the “Script for Server Version” option to “SQL Server 2016”, and set the “Script Data” option to “False”. Click “Next”.<\/li>\n
      6. In the “Summary” screen, review the options and click “Next”.<\/li>\n
      7. In the “Save or Publish Scripts” screen, set the “Save to File” option to “Single file”, and browse to a location on your local machine where you want to save the generated script. Click “Next”.<\/li>\n
      8. In the “Summary” screen, review the options and click “Finish” to generate the script.<\/li>\n<\/ol>\n

        Step 3: Modify the Azure SQL Database Schema Script<\/h1>\n

        Before executing the generated schema script on the Azure SQL Database, there are a few modifications that need to be made to the script.<\/p>\n

          \n
        1. Open the generated schema script in a text editor.<\/li>\n
        2. Remove the following line from the top of the script:\n
          USE [DatabaseName]\n<\/code><\/pre>\n
          This line is not needed since the script will be executed against the Azure SQL Database specified in the connection string.<\/p>\n<\/li>\n
        3. \n
          Search for the following line in the script:<\/p>\n
          SET ANSI_NULLS ON\n<\/code><\/pre>\n
          Add the following line immediately after it:<\/p>\n
          SET ANSI_PADDING ON\n<\/code><\/pre>\n
          This will ensure that the data types are aligned between the two databases.<\/p>\n<\/li>\n
        4. Save the modified schema script.<\/li>\n<\/ol>\n
          Step 4: Deploy the Azure SQL Database Schema<\/h1>\n
          The next step is to deploy the modified schema script to the Azure SQL Database.<\/p>\n
            \n
          1. Open SQL Server Management Studio and connect to the Azure SQL Database.<\/li>\n
          2. Open a new query window and copy and paste the modified schema script into it.<\/li>\n
          3. Execute the script by clicking the “Execute” button in the toolbar or pressing F5.<\/li>\n<\/ol>\n
            Step 5: Migrate the Data<\/h1>\n
            The final step is to migrate the data from the on-premises SQL Server database to the Azure SQL Database.<\/p>\n
              \n
            1. Open SQL Server Management Studio and connect to the on-premises SQL Server.<\/li>\n
            2. Right-click the database you want to migrate and select “Tasks” > “Export Data…”<\/li>\n
            3. In the\n
              “Introduction” screen, click “Next”.<\/p>\n<\/li>\n
            4. In the “Choose a Data Source” screen, select “SQL Server Native Client 11.0” as the data source and provide the server name, database name, and authentication details. Click “Next”.<\/li>\n
            5. In the “Choose a Destination” screen, select “Microsoft OLE DB Provider for SQL Server” as the destination and provide the Azure SQL Database connection string. Click “Next”.<\/li>\n
            6. In the “Specify Table Copy or Query” screen, select “Copy data from one or more tables or views” and select the tables you want to migrate. Click “Next”.<\/li>\n
            7. In the “Completing the Wizard” screen, review the options and click “Finish” to start the data migration.<\/li>\n<\/ol>\n
              Conclusion<\/h1>\n
              Migrating a SQL Server database to Azure SQL Database is a relatively straightforward process that can be accomplished with just a few steps. By following the steps outlined in this tutorial, you should be able to successfully migrate your on-premises SQL Server database to Azure SQL Database. Remember to test the migrated database thoroughly before making it live in a production environment.<\/p>\n","protected":false},"excerpt":{"rendered":"
              Introduction Many businesses are moving their on-premises infrastructures to the cloud to take advantage of the scalability, flexibility, and cost-effectiveness of cloud services. As a result, migrating databases to the cloud has become a common task for many IT professionals. Microsoft Azure provides a cloud-based relational database service called Azure Continue Reading<\/a><\/p>\n","protected":false},"author":1,"featured_media":0,"comment_status":"closed","ping_status":"open","sticky":false,"template":"","format":"standard","meta":{"_import_markdown_pro_load_document_selector":0,"_import_markdown_pro_submit_text_textarea":"","footnotes":""},"categories":[1],"tags":[411,1447,1450,1448,1449,1446],"yoast_head":"\nMigrating a SQL Server database to Azure SQL Database - Pantherax Blogs<\/title>\n<meta name=\"robots\" content=\"index, follow, max-snippet:-1, max-image-preview:large, max-video-preview:-1\" \/>\n<link rel=\"canonical\" href=\"http:\/\/localhost:10003\/migrating-a-sql-server-database-to-azure-sql-database\/\" \/>\n<meta property=\"og:locale\" content=\"en_US\" \/>\n<meta property=\"og:type\" content=\"article\" \/>\n<meta property=\"og:title\" content=\"Migrating a SQL Server database to Azure SQL Database\" \/>\n<meta property=\"og:description\" content=\"Introduction Many businesses are moving their on-premises infrastructures to the cloud to take advantage of the scalability, flexibility, and cost-effectiveness of cloud services. As a result, migrating databases to the cloud has become a common task for many IT professionals. Microsoft Azure provides a cloud-based relational database service called Azure Continue Reading\" \/>\n<meta property=\"og:url\" content=\"http:\/\/localhost:10003\/migrating-a-sql-server-database-to-azure-sql-database\/\" \/>\n<meta property=\"og:site_name\" content=\"Pantherax Blogs\" \/>\n<meta property=\"article:published_time\" content=\"2023-11-04T23:14:05+00:00\" \/>\n<meta property=\"article:modified_time\" content=\"2023-11-05T05:47:59+00:00\" \/>\n<meta name=\"author\" content=\"Panther\" \/>\n<meta name=\"twitter:card\" content=\"summary_large_image\" \/>\n<meta name=\"twitter:label1\" content=\"Written by\" \/>\n\t<meta name=\"twitter:data1\" content=\"Panther\" \/>\n\t<meta name=\"twitter:label2\" content=\"Est. reading time\" \/>\n\t<meta name=\"twitter:data2\" content=\"4 minutes\" \/>\n<script type=\"application\/ld+json\" class=\"yoast-schema-graph\">{\n\t    \"@context\": \"https:\/\/schema.org\",\n\t    \"@graph\": [\n\t        {\n\t            \"@type\": \"Article\",\n\t            \"@id\": \"http:\/\/localhost:10003\/migrating-a-sql-server-database-to-azure-sql-database\/#article\",\n\t            \"isPartOf\": {\n\t                \"@id\": \"http:\/\/localhost:10003\/migrating-a-sql-server-database-to-azure-sql-database\/\"\n\t            },\n\t            \"author\": {\n\t                \"name\": \"Panther\",\n\t                \"@id\": \"http:\/\/localhost:10003\/#\/schema\/person\/b63d816f4964b163e53cbbcffaa0f3d7\"\n\t            },\n\t            \"headline\": \"Migrating a SQL Server database to Azure SQL Database\",\n\t            \"datePublished\": \"2023-11-04T23:14:05+00:00\",\n\t            \"dateModified\": \"2023-11-05T05:47:59+00:00\",\n\t            \"mainEntityOfPage\": {\n\t                \"@id\": \"http:\/\/localhost:10003\/migrating-a-sql-server-database-to-azure-sql-database\/\"\n\t            },\n\t            \"wordCount\": 820,\n\t            \"publisher\": {\n\t                \"@id\": \"http:\/\/localhost:10003\/#organization\"\n\t            },\n\t            \"keywords\": [\n\t                \"\\\"Azure cloud services\\\"\",\n\t                \"\\\"Azure SQL Database migration\\\"\",\n\t                \"\\\"Database migration solutions\\\"]\",\n\t                \"\\\"Database migration tips\\\"\",\n\t                \"\\\"SQL database management\\\"\",\n\t                \"[\\\"Migrating SQL Server database\\\"\"\n\t            ],\n\t            \"inLanguage\": \"en-US\"\n\t        },\n\t        {\n\t            \"@type\": \"WebPage\",\n\t            \"@id\": \"http:\/\/localhost:10003\/migrating-a-sql-server-database-to-azure-sql-database\/\",\n\t            \"url\": \"http:\/\/localhost:10003\/migrating-a-sql-server-database-to-azure-sql-database\/\",\n\t            \"name\": \"Migrating a SQL Server database to Azure SQL Database - Pantherax Blogs\",\n\t            \"isPartOf\": {\n\t                \"@id\": \"http:\/\/localhost:10003\/#website\"\n\t            },\n\t            \"datePublished\": \"2023-11-04T23:14:05+00:00\",\n\t            \"dateModified\": \"2023-11-05T05:47:59+00:00\",\n\t            \"breadcrumb\": {\n\t                \"@id\": \"http:\/\/localhost:10003\/migrating-a-sql-server-database-to-azure-sql-database\/#breadcrumb\"\n\t            },\n\t            \"inLanguage\": \"en-US\",\n\t            \"potentialAction\": [\n\t                {\n\t                    \"@type\": \"ReadAction\",\n\t                    \"target\": [\n\t                        \"http:\/\/localhost:10003\/migrating-a-sql-server-database-to-azure-sql-database\/\"\n\t                    ]\n\t                }\n\t            ]\n\t        },\n\t        {\n\t            \"@type\": \"BreadcrumbList\",\n\t            \"@id\": \"http:\/\/localhost:10003\/migrating-a-sql-server-database-to-azure-sql-database\/#breadcrumb\",\n\t            \"itemListElement\": [\n\t                {\n\t                    \"@type\": \"ListItem\",\n\t                    \"position\": 1,\n\t                    \"name\": \"Home\",\n\t                    \"item\": \"http:\/\/localhost:10003\/\"\n\t                },\n\t                {\n\t                    \"@type\": \"ListItem\",\n\t                    \"position\": 2,\n\t                    \"name\": \"Migrating a SQL Server database to Azure SQL Database\"\n\t                }\n\t            ]\n\t        },\n\t        {\n\t            \"@type\": \"WebSite\",\n\t            \"@id\": \"http:\/\/localhost:10003\/#website\",\n\t            \"url\": \"http:\/\/localhost:10003\/\",\n\t            \"name\": \"Pantherax Blogs\",\n\t            \"description\": \"\",\n\t            \"publisher\": {\n\t                \"@id\": \"http:\/\/localhost:10003\/#organization\"\n\t            },\n\t            \"potentialAction\": [\n\t                {\n\t                    \"@type\": \"SearchAction\",\n\t                    \"target\": {\n\t                        \"@type\": \"EntryPoint\",\n\t                        \"urlTemplate\": \"http:\/\/localhost:10003\/?s={search_term_string}\"\n\t                    },\n\t                    \"query-input\": \"required name=search_term_string\"\n\t                }\n\t            ],\n\t            \"inLanguage\": \"en-US\"\n\t        },\n\t        {\n\t            \"@type\": \"Organization\",\n\t            \"@id\": \"http:\/\/localhost:10003\/#organization\",\n\t            \"name\": \"Pantherax Blogs\",\n\t            \"url\": \"http:\/\/localhost:10003\/\",\n\t            \"logo\": {\n\t                \"@type\": \"ImageObject\",\n\t                \"inLanguage\": \"en-US\",\n\t                \"@id\": \"http:\/\/localhost:10003\/#\/schema\/logo\/image\/\",\n\t                \"url\": \"http:\/\/localhost:10003\/wp-content\/uploads\/2023\/11\/cropped-9e7721cb-2d62-4f72-ab7f-7d1d8db89226.jpeg\",\n\t                \"contentUrl\": \"http:\/\/localhost:10003\/wp-content\/uploads\/2023\/11\/cropped-9e7721cb-2d62-4f72-ab7f-7d1d8db89226.jpeg\",\n\t                \"width\": 1024,\n\t                \"height\": 1024,\n\t                \"caption\": \"Pantherax Blogs\"\n\t            },\n\t            \"image\": {\n\t                \"@id\": \"http:\/\/localhost:10003\/#\/schema\/logo\/image\/\"\n\t            }\n\t        },\n\t        {\n\t            \"@type\": \"Person\",\n\t            \"@id\": \"http:\/\/localhost:10003\/#\/schema\/person\/b63d816f4964b163e53cbbcffaa0f3d7\",\n\t            \"name\": \"Panther\",\n\t            \"image\": {\n\t                \"@type\": \"ImageObject\",\n\t                \"inLanguage\": \"en-US\",\n\t                \"@id\": \"http:\/\/localhost:10003\/#\/schema\/person\/image\/\",\n\t                \"url\": \"http:\/\/2.gravatar.com\/avatar\/b8c0eda5a49f8f31ec32d0a0f9d6f838?s=96&d=mm&r=g\",\n\t                \"contentUrl\": \"http:\/\/2.gravatar.com\/avatar\/b8c0eda5a49f8f31ec32d0a0f9d6f838?s=96&d=mm&r=g\",\n\t                \"caption\": \"Panther\"\n\t            },\n\t            \"sameAs\": [\n\t                \"http:\/\/localhost:10003\"\n\t            ],\n\t            \"url\": \"http:\/\/localhost:10003\/author\/pepethefrog\/\"\n\t        }\n\t    ]\n\t}<\/script>\n<!-- \/ Yoast SEO Premium plugin. -->","yoast_head_json":{"title":"Migrating a SQL Server database to Azure SQL Database - Pantherax Blogs","robots":{"index":"index","follow":"follow","max-snippet":"max-snippet:-1","max-image-preview":"max-image-preview:large","max-video-preview":"max-video-preview:-1"},"canonical":"http:\/\/localhost:10003\/migrating-a-sql-server-database-to-azure-sql-database\/","og_locale":"en_US","og_type":"article","og_title":"Migrating a SQL Server database to Azure SQL Database","og_description":"Introduction Many businesses are moving their on-premises infrastructures to the cloud to take advantage of the scalability, flexibility, and cost-effectiveness of cloud services. As a result, migrating databases to the cloud has become a common task for many IT professionals. Microsoft Azure provides a cloud-based relational database service called Azure Continue Reading","og_url":"http:\/\/localhost:10003\/migrating-a-sql-server-database-to-azure-sql-database\/","og_site_name":"Pantherax Blogs","article_published_time":"2023-11-04T23:14:05+00:00","article_modified_time":"2023-11-05T05:47:59+00:00","author":"Panther","twitter_card":"summary_large_image","twitter_misc":{"Written by":"Panther","Est. reading time":"4 minutes"},"schema":{"@context":"https:\/\/schema.org","@graph":[{"@type":"Article","@id":"http:\/\/localhost:10003\/migrating-a-sql-server-database-to-azure-sql-database\/#article","isPartOf":{"@id":"http:\/\/localhost:10003\/migrating-a-sql-server-database-to-azure-sql-database\/"},"author":{"name":"Panther","@id":"http:\/\/localhost:10003\/#\/schema\/person\/b63d816f4964b163e53cbbcffaa0f3d7"},"headline":"Migrating a SQL Server database to Azure SQL Database","datePublished":"2023-11-04T23:14:05+00:00","dateModified":"2023-11-05T05:47:59+00:00","mainEntityOfPage":{"@id":"http:\/\/localhost:10003\/migrating-a-sql-server-database-to-azure-sql-database\/"},"wordCount":820,"publisher":{"@id":"http:\/\/localhost:10003\/#organization"},"keywords":["\"Azure cloud services\"","\"Azure SQL Database migration\"","\"Database migration solutions\"]","\"Database migration tips\"","\"SQL database management\"","[\"Migrating SQL Server database\""],"inLanguage":"en-US"},{"@type":"WebPage","@id":"http:\/\/localhost:10003\/migrating-a-sql-server-database-to-azure-sql-database\/","url":"http:\/\/localhost:10003\/migrating-a-sql-server-database-to-azure-sql-database\/","name":"Migrating a SQL Server database to Azure SQL Database - Pantherax Blogs","isPartOf":{"@id":"http:\/\/localhost:10003\/#website"},"datePublished":"2023-11-04T23:14:05+00:00","dateModified":"2023-11-05T05:47:59+00:00","breadcrumb":{"@id":"http:\/\/localhost:10003\/migrating-a-sql-server-database-to-azure-sql-database\/#breadcrumb"},"inLanguage":"en-US","potentialAction":[{"@type":"ReadAction","target":["http:\/\/localhost:10003\/migrating-a-sql-server-database-to-azure-sql-database\/"]}]},{"@type":"BreadcrumbList","@id":"http:\/\/localhost:10003\/migrating-a-sql-server-database-to-azure-sql-database\/#breadcrumb","itemListElement":[{"@type":"ListItem","position":1,"name":"Home","item":"http:\/\/localhost:10003\/"},{"@type":"ListItem","position":2,"name":"Migrating a SQL Server database to Azure SQL Database"}]},{"@type":"WebSite","@id":"http:\/\/localhost:10003\/#website","url":"http:\/\/localhost:10003\/","name":"Pantherax Blogs","description":"","publisher":{"@id":"http:\/\/localhost:10003\/#organization"},"potentialAction":[{"@type":"SearchAction","target":{"@type":"EntryPoint","urlTemplate":"http:\/\/localhost:10003\/?s={search_term_string}"},"query-input":"required name=search_term_string"}],"inLanguage":"en-US"},{"@type":"Organization","@id":"http:\/\/localhost:10003\/#organization","name":"Pantherax Blogs","url":"http:\/\/localhost:10003\/","logo":{"@type":"ImageObject","inLanguage":"en-US","@id":"http:\/\/localhost:10003\/#\/schema\/logo\/image\/","url":"http:\/\/localhost:10003\/wp-content\/uploads\/2023\/11\/cropped-9e7721cb-2d62-4f72-ab7f-7d1d8db89226.jpeg","contentUrl":"http:\/\/localhost:10003\/wp-content\/uploads\/2023\/11\/cropped-9e7721cb-2d62-4f72-ab7f-7d1d8db89226.jpeg","width":1024,"height":1024,"caption":"Pantherax Blogs"},"image":{"@id":"http:\/\/localhost:10003\/#\/schema\/logo\/image\/"}},{"@type":"Person","@id":"http:\/\/localhost:10003\/#\/schema\/person\/b63d816f4964b163e53cbbcffaa0f3d7","name":"Panther","image":{"@type":"ImageObject","inLanguage":"en-US","@id":"http:\/\/localhost:10003\/#\/schema\/person\/image\/","url":"http:\/\/2.gravatar.com\/avatar\/b8c0eda5a49f8f31ec32d0a0f9d6f838?s=96&d=mm&r=g","contentUrl":"http:\/\/2.gravatar.com\/avatar\/b8c0eda5a49f8f31ec32d0a0f9d6f838?s=96&d=mm&r=g","caption":"Panther"},"sameAs":["http:\/\/localhost:10003"],"url":"http:\/\/localhost:10003\/author\/pepethefrog\/"}]}},"jetpack_sharing_enabled":true,"jetpack_featured_media_url":"","_links":{"self":[{"href":"http:\/\/localhost:10003\/wp-json\/wp\/v2\/posts\/4142"}],"collection":[{"href":"http:\/\/localhost:10003\/wp-json\/wp\/v2\/posts"}],"about":[{"href":"http:\/\/localhost:10003\/wp-json\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"http:\/\/localhost:10003\/wp-json\/wp\/v2\/users\/1"}],"replies":[{"embeddable":true,"href":"http:\/\/localhost:10003\/wp-json\/wp\/v2\/comments?post=4142"}],"version-history":[{"count":1,"href":"http:\/\/localhost:10003\/wp-json\/wp\/v2\/posts\/4142\/revisions"}],"predecessor-version":[{"id":4398,"href":"http:\/\/localhost:10003\/wp-json\/wp\/v2\/posts\/4142\/revisions\/4398"}],"wp:attachment":[{"href":"http:\/\/localhost:10003\/wp-json\/wp\/v2\/media?parent=4142"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"http:\/\/localhost:10003\/wp-json\/wp\/v2\/categories?post=4142"},{"taxonomy":"post_tag","embeddable":true,"href":"http:\/\/localhost:10003\/wp-json\/wp\/v2\/tags?post=4142"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}