{"id":82805,"date":"2019-02-15T20:28:10","date_gmt":"2019-02-15T20:28:10","guid":{"rendered":"https:\/\/blogs.msdn.microsoft.com\/webdev\/?p=16235"},"modified":"2019-02-15T20:28:10","modified_gmt":"2019-02-15T20:28:10","slug":"migrating-your-existing-on-prem-sql-server-database-to-azure-sql-db","status":"publish","type":"post","link":"https:\/\/sickgaming.net\/blog\/2019\/02\/15\/migrating-your-existing-on-prem-sql-server-database-to-azure-sql-db\/","title":{"rendered":"Migrating your existing on-prem SQL Server database to Azure SQL DB"},"content":{"rendered":"<p>If you are in the process of moving an existing .NET application to Azure, it\u2019s likely you\u2019ll have to migrate an existing, on-prem SQL database as well. There are a few different ways you can go about this, so let\u2019s go through them.<\/p>\n<h3>Data Migration Assistant (downtime required)<\/h3>\n<p>The\u00a0Data Migration Assistant (<span><a href=\"https:\/\/www.microsoft.com\/download\/details.aspx?id=53595\">download<\/a><\/span> | <span><a href=\"https:\/\/docs.microsoft.com\/en-us\/sql\/dma\/dma-overview?view=sql-server-2017\">documentation<\/a><\/span>)\u00a0is free, easy to use, slick and extremely powerful! It can:<\/p>\n<ul>\n<li>Evaluate if your database is ready to migrate and produce a readiness report (command line support included)<\/li>\n<li>Provide recommendations for how to remediate migration blocking issues<\/li>\n<li>Recommend the minimum Azure SQL Database SKU based on performance counter data of your existing database<\/li>\n<li>Perform the actual migration of schema, data and objects (server roles, logins, etc.)<\/li>\n<\/ul>\n<p><a href=\"http:\/\/www.sickgaming.net\/blog\/wp-content\/uploads\/2019\/02\/migrating-your-existing-on-prem-sql-server-database-to-azure-sql-db.png\"><img loading=\"lazy\" decoding=\"async\" src=\"http:\/\/www.sickgaming.net\/blog\/wp-content\/uploads\/2019\/02\/migrating-your-existing-on-prem-sql-server-database-to-azure-sql-db.png\" alt=\"\" width=\"986\" height=\"606\" class=\"alignnone size-full wp-image-16265\" \/><\/a><\/p>\n<p>After a successful migration, applications will be able to connect to the target SQL server databases seamlessly. There are currently a <span><a href=\"https:\/\/docs.microsoft.com\/en-us\/azure\/dms\/known-issues-azure-sql-online\">couple of limitations<\/a><\/span>, but the majority of databases shouldn\u2019t be impacted. If this sounds interesting, check out the full tutorials on <span><a href=\"https:\/\/docs.microsoft.com\/en-us\/azure\/dms\/tutorial-sql-server-to-azure-sql\">how to migrate to Azure SQL DB<\/a><\/span> and how to migrate to <span><a href=\"https:\/\/docs.microsoft.com\/en-us\/azure\/dms\/tutorial-sql-server-to-managed-instance\">Azure SQL DB Managed Instance<\/a><\/span>.<\/p>\n<h3><\/h3>\n<h3>Azure Data Migration Service (no downtime required)<\/h3>\n<p>The <span><a href=\"https:\/\/azure.microsoft.com\/en-us\/services\/database-migration\/\">Azure Data Migration Service<\/a><\/span> allows you to move your on-prem database to Azure without taking it offline during the migration. Applications can keep on running while the migration is taking place. Once the database in Azure is ready you can switch your applications over immediately.<\/p>\n<p><a href=\"http:\/\/www.sickgaming.net\/blog\/wp-content\/uploads\/2019\/02\/migrating-your-existing-on-prem-sql-server-database-to-azure-sql-db-1.png\"><img loading=\"lazy\" decoding=\"async\" src=\"http:\/\/www.sickgaming.net\/blog\/wp-content\/uploads\/2019\/02\/migrating-your-existing-on-prem-sql-server-database-to-azure-sql-db-1.png\" alt=\"\" width=\"826\" height=\"308\" class=\"alignnone size-full wp-image-16275\" \/><\/a><\/p>\n<p>If this sounds interesting, check out the full tutorials on <span><a href=\"https:\/\/docs.microsoft.com\/en-us\/azure\/dms\/tutorial-sql-server-azure-sql-online\">how to migrate to Azure SQL DB<\/a><\/span> and how to migrate to <span><a href=\"https:\/\/docs.microsoft.com\/en-us\/azure\/dms\/tutorial-sql-server-managed-instance-online\">Azure SQL DB Managed Instance<\/a><\/span> without downtime.<\/p>\n<h3><\/h3>\n<h3>SQL Server Management Studio (downtime required)<\/h3>\n<p>You are probably already familiar with SQL Server Management Studio (<span><a href=\"https:\/\/go.microsoft.com\/fwlink\/?linkid=2043154\">download<\/a><\/span> | <span><a href=\"https:\/\/docs.microsoft.com\/en-us\/sql\/ssms\/download-sql-server-management-studio-ssms?view=sql-server-2017\">documentation<\/a><\/span>), but if you are not it\u2019s basically an IDE for SQL Server built on top of the Visual Studio shell and it\u2019s free! Unlike the Data Migration Assistant, it cannot produce readiness reports nor can it suggest remediating actions, but it can perform the actual migration two different ways.<\/p>\n<p>The first way is by selecting the command \u201cDeploy Database to Microsoft Azure SQL Database\u2026\u201d which will bring up the migration wizard to take you through the process step by step:<\/p>\n<p><a href=\"http:\/\/www.sickgaming.net\/blog\/wp-content\/uploads\/2019\/02\/migrating-your-existing-on-prem-sql-server-database-to-azure-sql-db-2.png\"><img loading=\"lazy\" decoding=\"async\" src=\"http:\/\/www.sickgaming.net\/blog\/wp-content\/uploads\/2019\/02\/migrating-your-existing-on-prem-sql-server-database-to-azure-sql-db-2.png\" alt=\"\" width=\"756\" height=\"830\" class=\"alignnone size-full wp-image-16245\" \/><\/a><\/p>\n<p>The second way is by exporting the existing, on-prem database as a .bacpac file (<span><a href=\"https:\/\/docs.microsoft.com\/en-us\/sql\/relational-databases\/data-tier-applications\/export-a-data-tier-application?view=sql-server-2017#UsingDeployDACWizard\">docs to help with that<\/a><\/span>) and then importing the .backpac file into Azure:<\/p>\n<h3><a href=\"http:\/\/www.sickgaming.net\/blog\/wp-content\/uploads\/2019\/02\/migrating-your-existing-on-prem-sql-server-database-to-azure-sql-db-3.png\"><img loading=\"lazy\" decoding=\"async\" src=\"http:\/\/www.sickgaming.net\/blog\/wp-content\/uploads\/2019\/02\/migrating-your-existing-on-prem-sql-server-database-to-azure-sql-db-3.png\" alt=\"\" width=\"764\" height=\"837\" class=\"alignnone size-full wp-image-16255\" \/><\/a><\/h3>\n<h3>Resolving database migration compatibility issues<\/h3>\n<p>There are a wide variety of compatibility issues that you might encounter, depending both on the version of SQL Server in the source database and the complexity of the database you are migrating. Use the following resources, in addition to a targeted Internet search using your search engine of choices:<\/p>\n<p>In addition to searching the Internet and using these resources, use the\u00a0<span><a href=\"https:\/\/social.msdn.microsoft.com\/Forums\/sqlserver\/home?category=sqlserver\">MSDN SQL Server community forums<\/a><\/span>\u00a0or\u00a0<a href=\"http:\/\/stackoverflow.com\/\">StackOverflow<\/a>. If you have any questions or problems just leave us a comment below.<\/p>\n<p> &#013; <\/p>\n","protected":false},"excerpt":{"rendered":"<p>If you are in the process of moving an existing .NET application to Azure, it\u2019s likely you\u2019ll have to migrate an existing, on-prem SQL database as well. There are a few different ways you can go about this, so let\u2019s go through them. Data Migration Assistant (downtime required) The\u00a0Data Migration Assistant (download | documentation)\u00a0is free, [&hellip;]<\/p>\n","protected":false},"author":2,"featured_media":82806,"comment_status":"open","ping_status":"closed","sticky":false,"template":"","format":"standard","meta":{"footnotes":""},"categories":[66],"tags":[54],"class_list":["post-82805","post","type-post","status-publish","format-standard","has-post-thumbnail","hentry","category-microsoft-webdev","tag-azure"],"_links":{"self":[{"href":"https:\/\/sickgaming.net\/blog\/wp-json\/wp\/v2\/posts\/82805","targetHints":{"allow":["GET"]}}],"collection":[{"href":"https:\/\/sickgaming.net\/blog\/wp-json\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/sickgaming.net\/blog\/wp-json\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/sickgaming.net\/blog\/wp-json\/wp\/v2\/users\/2"}],"replies":[{"embeddable":true,"href":"https:\/\/sickgaming.net\/blog\/wp-json\/wp\/v2\/comments?post=82805"}],"version-history":[{"count":0,"href":"https:\/\/sickgaming.net\/blog\/wp-json\/wp\/v2\/posts\/82805\/revisions"}],"wp:featuredmedia":[{"embeddable":true,"href":"https:\/\/sickgaming.net\/blog\/wp-json\/wp\/v2\/media\/82806"}],"wp:attachment":[{"href":"https:\/\/sickgaming.net\/blog\/wp-json\/wp\/v2\/media?parent=82805"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/sickgaming.net\/blog\/wp-json\/wp\/v2\/categories?post=82805"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/sickgaming.net\/blog\/wp-json\/wp\/v2\/tags?post=82805"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}