Migrate SQL Server databases from an Azure SQL database to Amazon RDS for SQL Server using bacpac method

{"value":"Customers choose [Amazon Relational Database Service (Amazon RDS) for SQL Server](https://aws.amazon.com/rds/sqlserver/) because it manages time-consuming database administration tasks including provisioning, backups, software patching, monitoring, and hardware scaling. With [Amazon RDS](https://aws.amazon.com/cn/rds/?trk=cndc-detail) for SQL Server, you can enable single-click high availability by enabling the [Multi-AZ](https://aws.amazon.com/rds/features/multi-az/) flag in the [RDS Console](https://console.aws.amazon.com/rds/home). With Multi-AZ, we replicate data synchronously across different availability zones. In case the primary node crashes, your database automatically fails over to the secondary and Amazon Web Services automatically re-builds the secondary. Increasingly, customers are looking to migrate their databases to [Amazon RDS](https://aws.amazon.com/cn/rds/?trk=cndc-detail) for SQL Server to take advantage of its benefits. Industry analyst firm Gartner has published the [Solution Scorecard for Amazon Relational Database Service](https://aws.amazon.com/resources/analyst-reports/gartner-solution-scorecard-amazon-rds-2022/), with Amazon Web Services earning a 95 rating, including 100% of required criteria by Gartner for an operational database platform as a service (dbPaaS). You can check details of the report [here](https://aws.amazon.com/blogs/database/gartner-recognizes-amazon-rds-in-new-report/). Although there are several ways to migrate SQL Server databases, using SQL Server backup files is the simplest and most reliable method.\n\nIn this post, we demonstrate the process to migrate Azure SQL databases to [Amazon RDS for SQL Server](https://aws.amazon.com/rds/sqlserver/). Specifically, we show how to take the ```.bacpac```file from your Azure SQL database and restore the database to an [Amazon RDS](https://aws.amazon.com/cn/rds/?trk=cndc-detail) for SQL Server database.\n\n### **Migration options**\n\nMicrosoft Azure provides three distinct SQL Server offerings:\n\n- SQL Server on Azure VMs\n- Azure SQL Managed Instance, which is a managed database service that is compatible with the latest SQL Server database engine\n- Azure SQL Database, which is a cloud-based managed service that provides features such as serverless compute and Hyperscale storage\n\nThe standard methodology of migrating a database from source to target servers consists of using the native backup and restore mechanism. SQL Server comes with two common database backup options: the native backup (```.bak```) file and the data-tier application backup package file (```.bacpac```). Both options have their pros and cons, which we will discuss in this section.\n\nFor more information about how to export database to ```.bacpac```file, refer to [exporting database to .bacpac file.](https://docs.microsoft.com/en-us/azure/azure-sql/database/database-export?view=azuresql)\n\n#### **Native backup files (.bak)**\n\nA native backup is a copy of a database that consists of data records (data file) and log records (log file). Native backup comes with different [types](https://docs.microsoft.com/en-us/sql/t-sql/statements/backup-transact-sql?view=sql-server-ver16) like full backup, differential backup, transactional backup, partial backups, or file group backup. Depending on the [recovery model](https://docs.microsoft.com/en-us/sql/relational-databases/backup-restore/recovery-models-sql-server?view=sql-server-ver16) set for a database, you can achieve point in time recovery. The extension of the backup file is usually denoted as ```.bak```. There are various ways through which we can take a backup of the database, the most common are using SQL Server Management Studio (SSMS) or the command line.\n\nNative backup has the following advantages:\n\n- A full backup takes a complete copy of a database\n- A full backup is transactionally consistent\n- You can take a portion of the database, like backing up a particular file group, data file, or log file\n- It provides point in time recovery with full recovery mode\n- You can stripe the backup files for a large database to improve speed and stripe the files across different disks for any storage concerns on a particular disk\n- A full backup provides the flexibility to minimize the downtime window on cutover. For more information, refer to [Migrating SQL Server to Amazon RDS using native backup and restore](https://aws.amazon.com/blogs/database/migrating-sql-server-to-amazon-rds-using-native-backup-and-restore/)\n- A full backup can be compressed in SQL Server Editions of Enterprise, Standard, and Developer. This [compressed backup](https://aws.amazon.com/blogs/database/migrating-sql-server-to-amazon-rds-using-native-backup-and-restore/) file occupies less space and can be done faster\n\nHowever, native backup has the following challenges:\n\n- The size of the ```.bak```file is larger compared to the ```.bacpac```file because it takes a full copy of data contained on the database files\n- Azure SQL Database doesn’t allow you to take native backups (```.bak```) as it uses a different technology for backups. Refer to [Automated backups](https://docs.microsoft.com/en-us/azure/azure-sql/database/automated-backups-overview?view=azuresql&tabs=single-database) to learn more\n- Azure SQL Database doesn’t support taking backups from higher SQL Server versions and restoring to an earlier version.\n\n#### **BACPAC files**\n\nA BACPAC (backup package) file consists of copied metadata and the actual data compressed to a file. The extension of this file is usually denoted as ```.bacpac```. This is the best choice for a small database size (approximately 200 GB). We can take a```.bacpac```file through an Export\\Import data-tier application (GUI) or through a SQLPackage.exe (command line) utility.\n\nThis method has the following advantages:\n\n- You can migrate data from different sources to targets with different SQL versions with few compatibility issues\n- The ```.bacpac```size is comparatively smaller than a ```.bak```file because it takes the metadata and data copy. It doesn’t take a backup of indexes; instead, it generates scripts to create indexes on the target\n\nHowever, this method has the following challenges:\n\n- It’s not transactionally consistent. You must take a snapshot and export the database to make it transactionally consistent\n- You can’t take a portion of the database, like backing up a particular file group, data file, or log file\n- It doesn’t provide point in time recovery\n- It is recommended for smaller databases (within 200 GB) because it may be time-consuming to build the database\n\n### **Solution overview**\n\nThe most common method to migrate SQL Server databases from any source to [Amazon RDS](https://aws.amazon.com/cn/rds/?trk=cndc-detail) for SQL Server is to take the native backup from the SQL Server at the source and copy the backup files to an [Amazon Simple Storage Service](http://aws.amazon.com/s3) ([Amazon S3](https://aws.amazon.com/cn/s3/?trk=cndc-detail)) bucket. Then, you restore the backup files to [Amazon RDS](https://aws.amazon.com/cn/rds/?trk=cndc-detail) for SQL Server. For more information, see [Setting up for native backup and restore.](https://docs.aws.amazon.com/AmazonRDS/latest/UserGuide/SQLServer.Procedural.Importing.html#SQLServer.Procedural.Importing.Native.Enabling)\n\nFor migrating SQL databases from the Azure cloud platform to [Amazon RDS](https://aws.amazon.com/cn/rds/?trk=cndc-detail) for SQL Server using the native backup and restore method, we can either use ```.bak```(native backups) or ```.bacpac```(backup package). The following figures illustrate the supportability of backup options for the three offerings in Azure for SQL Server databases.\n\nThe first figure shows how ```.bak```and ```.bacpac```files are supported in SQL Server on a virtual machine and SQL managed instance offerings.\n\n\n\nThe following figure shows how Azure SQL database supports only ```.bacpac```file and not ```.bak```.\n\n\n\nThe following table summarizes the backup options for migrating SQL Server databases from different offerings of Azure (Azure SQL on VMs, Azure SQL Managed Instance, Azure SQL Database) using ```.bacpac```and ```.bak```files, which are taken to either Azure Storage or physical disks.\n\n\n\n### **Perform a backup using .bak**\n\nThis procedure involves performing a full backup of the SQL database hosted in Azure (supported in Azure SQL on VMs and Azure SQL Managed Instance offerings only), followed by differential and log backups, and then restoring the same backups to the target RDS for SQL Server instance, with the help of differential and log backups. Following this process can reduce application cutover time during the migration process. For more information, refer to [Migrating SQL Server to Amazon RDS using native backup and restore.](https://aws.amazon.com/blogs/database/migrating-sql-server-to-amazon-rds-using-native-backup-and-restore/)\n\n### **Perform a backup using .bacpac**\n\nThe following is the detailed procedure to migrate an Azure SQL database (PaaS solution) to [Amazon RDS](https://aws.amazon.com/cn/rds/?trk=cndc-detail) for SQL Server using the ```.bacpac ```option. We walk through the steps for taking a ```.bacpac```file from an Azure SQL database using an export data-tier application and restoring the ```.bacpac```file using SQLPackage.exe on [Amazon RDS](https://aws.amazon.com/cn/rds/?trk=cndc-detail) for SQL Server.\n\nWe can export a ```.bacpac```file from Azure Portal using an Export data-tier application or through the command line utility SQLPackage.exe to available storage. Then, copy the ```.bacpac```file to [Amazon Elastic Compute Cloud](http://aws.amazon.com/ec2) (Amazon EC2). From there, you can connect to the target RDS for SQL Server database, preferably in the same region and availability zone.\n\n[Amazon RDS](https://aws.amazon.com/cn/rds/?trk=cndc-detail) for SQL Server doesn’t support importing the ```.bacpac```file using a data-tier application. The only way to import the ```.bacpac```file is to use ```sqlpackage.exe```.\n\nIn order for the ```.bacpac```file to be transactionally consistent, make sure that no write activity is occurring during the export or exporting a transactionally consistent copy. Take a snapshot and then export the ```.bacpac```to make it transactionally consistent, then delete the snapshot after a successful export of ```.bacpac```.\n\nThe following are the high-level steps to export and import ```.bacpac```files from your Azure SQL database to [Amazon RDS](https://aws.amazon.com/cn/rds/?trk=cndc-detail) for SQL Server:\n\n1. Export the ```.bacpac```file from your Azure SQL database\n2. Copy the ```.bacpac```file from Azure Storage to [Amazon EC2 ](https://aws.amazon.com/cn/ec2/?trk=cndc-detail)EBS storage\n3. Import the ```.bacpac```file to [Amazon RDS](https://aws.amazon.com/cn/rds/?trk=cndc-detail) for SQL Server\n\nThe following diagram illustrates this process.\n\n\n\n### **Export the .bacpac file from the Azure SQL database**\n\nYou can choose from the following methods to export the ```.bacpac```file and save it to storage (either to Azure Storage or a physical file system):\n\n- Azure Portal\n- SQLPackage.exe\n- Export data-tier application\n\n**Azure Portal**\n\nTo use the Azure Portal, complete the following steps:\n\n1. Open the Azure Portal and search for the Azure SQL database that you want to migrate\n2. Choose **Export**\nFigure 1, highlights the export option available on the toolbar of Azure Portal.\n\n\n\nFigure 1: Highlights the Export utility in Azure Portal\n\nYou’re redirected to the next page to save the ```.bacpac```file to Azure Storage as shown in Figure 2.\n\n3. For **File name**, leave the default file name or enter a new name\n4. Choose an existing Azure Storage account to export the ```.bacpac```file\n5. Provide the appropriate credentials to access the source database (the SQL Server admin login and password you used to connect to the Azure SQL database)\n6. Choose **OK**\n\n\n\nFigure 2: Export database wizard in Azure Portal\n\nYou’re redirected to the next page where you can download the ```.bacpac```file.\n\n7. Select the ```.bacpac```file\n8. On the options menu, choose **Download**\nFigure 3, highlights the download option available on clicking the three dots.\n\n\n\nFigure 3: Highlights Download Option\n\n**SQLPacakge.exe**\n\nSQLPackage is a command line utility to manage the data-tier application. It helps extract SQL objects from your database to a file. To export a SQL database using the [SQLPackage](https://docs.microsoft.com/en-us/sql/tools/sqlpackage) command-line utility, download the latest version of the [SQLPackage](https://docs.microsoft.com/en-us/sql/tools/sqlpackage/sqlpackage-download) utility to a path and run the following command from a CMD shell to export the ```.bacpac```file and copy this file to Amazon EC2, where the target RDS for SQL Server database is connected. For more information about SQLPackage Export parameters, refer to [SqlPackage Export parameters and properties.](https://docs.microsoft.com/en-us/sql/tools/sqlpackage/sqlpackage-export?view=sql-server-ver16)\n\nSee the following code:\n\n```\\nsqlpackage.exe /Action:Export /ssn:”tcp:ServerName” /sdn:DatabaseName /su:UserName /sp:Password /tf:”TargetFile” /p:Storage=File\\n```\nFor example:\n\n```\\nsqlpackage.exe /Action:Export /ssn:”tcp:azuresqldbxx.database.windows.net,1433”/sdn:testdb /su:AdminUser /sp:AdminPassword1 /tf:”C:\\\\testdb.bacpac” /p:Storage=File\\n```\n\n**Export data-tier application**\n\nSQL Server Management Studio (SSMS) has a wizard to export a database in an Azure SQL database to a BACPAC file.\n\n1. Open SSMS and connect to the Azure SQL database\n2. Choose the database (right-click) and on the **Tasks** menu, choose **Export Data-tier Application** as highlighted in Figure 4.\n\n\n\nFigure 4: Export Data-tier Application\n\n3. Provide the target location to which the ```.bacpac```file will be saved. Figure 5, highlights the path provided.\n\n\n\nFigure 5: Export setting wizard\n\n4. Copy this file to Amazon EC2, where the target RDS for SQL Server database is connected.\n\n#### **Copy the .bacpac file from Azure Storage to [Amazon EC2 ](https://aws.amazon.com/cn/ec2/?trk=cndc-detail)EBS storage**\n\nAfter the export is successfully saved to the physical disk, copy the ```.bacpac```file from the saved location to [Amazon EC2 ](https://aws.amazon.com/cn/ec2/?trk=cndc-detail)from where the target RDS for SQL Server database is connected. [Use S3 with Amazon EC2](https://docs.aws.amazon.com/AWSEC2/latest/UserGuide/AmazonS3.html) to copy files. You need to import the ```.bacpac```file to restore the database on [Amazon RDS](https://aws.amazon.com/cn/rds/?trk=cndc-detail) for SQL Server.\n\n#### **Import the .bacpac file to [Amazon RDS](https://aws.amazon.com/cn/rds/?trk=cndc-detail) for SQL Server**\n\n[Amazon RDS](https://aws.amazon.com/cn/rds/?trk=cndc-detail) for SQL Server supports SQLPackage.exe to import the ```.bacpac```file. The GUI version using the Import data-tier application is not supported and fails with sqladmin permission error. Use the [SQLPackage.exe](https://docs.microsoft.com/en-us/sql/tools/sqlpackage) utility and run the following command from a CMD shell from [Amazon EC2 ](https://aws.amazon.com/cn/ec2/?trk=cndc-detail)to import the ```.bacpac```file into the target RDS for SQL Server database.\n\n```\\nsqlpackage.exe /Action:Import /sf:”Path to bacpac file” /tsn:”RDS for SQL Server Endpoint” /tdn:Target DatabaseName /tu:UserName /tp:Password\\n```\nFor example:\n\n```\\nsqlpackage /a:Import /sf:”c:\\\\sql\\\\testdbaz.bacpac” /tsn:”rdstestxxxx.xxxxxxxxxxxx.useast-1.rds.amazon.com” /tdn:AZTESTDBXX /tu:sqladmin /tp:xxxxxxxxxxxx\\n```\n\nFigure 6, shows a successful import of our Azure SQL database using SQLPackage.exe.\n\n\n\nFigure 6: SQLPackage.exe sample execution\n\nFor more information, refer to [Import a BACPAC File to Create a New User Database](https://docs.microsoft.com/en-us/sql/relational-databases/data-tier-applications/import-a-bacpac-file-to-create-a-new-user-database?view=sql-server-ver15). To learn more about SQLPackage Import parameters, refer to [SqlPackage Import parameters and properties.](https://docs.microsoft.com/en-us/sql/tools/sqlpackage/sqlpackage-import?view=sql-server-ver16)\n\n### **Summary**\n\nIn this post, we covered how you can migrate Azure SQL Server databases to [Amazon RDS](https://aws.amazon.com/cn/rds/?trk=cndc-detail) for SQL Server using the ```.bacpac```file, SQLPackage, and a data-tier application. With a successful migration to [Amazon RDS](https://aws.amazon.com/cn/rds/?trk=cndc-detail) for SQL Server, you can focus on what matters to your organization’s business, such as optimizing database design, capacity sizing, and performance tuning.\n\nIf you have any questions or suggestions, leave a comment.\n\n\n#### **About the authors**\n\n\n\n**InduTeja Aligeti** is a Lead Database Consultant at Amazon Web Services. She has 16+years of experience working with Microsoft Technologies with a specialization in SQL Server. She focuses on helping customers to build high-available, cost-effective database solutions and migrate their large scale SQL Server databases to Amazon Web Services.\n\n\n\n**Yogi Barot** is Microsoft Specialist Principal Solutions Architect at Amazon Web Services, she has 22 years of experience working with different Microsoft technologies, her specialty is in SQL Server and different database technologies. Yogi has in depth Amazon Web Services knowledge and expertise in running Microsoft workload on Amazon Web Services.","render":"<p>Customers choose <a href=\\"https://aws.amazon.com/rds/sqlserver/\\" target=\\"_blank\\">Amazon Relational Database Service (Amazon RDS) for SQL Server</a> because it manages time-consuming database administration tasks including provisioning, backups, software patching, monitoring, and hardware scaling. With [Amazon RDS](https://aws.amazon.com/cn/rds/?trk=cndc-detail) for SQL Server, you can enable single-click high availability by enabling the <a href=\\"https://aws.amazon.com/rds/features/multi-az/\\" target=\\"_blank\\">Multi-AZ</a> flag in the <a href=\\"https://console.aws.amazon.com/rds/home\\" target=\\"_blank\\">RDS Console</a>. With Multi-AZ, we replicate data synchronously across different availability zones. In case the primary node crashes, your database automatically fails over to the secondary and Amazon Web Services automatically re-builds the secondary. Increasingly, customers are looking to migrate their databases to [Amazon RDS](https://aws.amazon.com/cn/rds/?trk=cndc-detail) for SQL Server to take advantage of its benefits. Industry analyst firm Gartner has published the <a href=\\"https://aws.amazon.com/resources/analyst-reports/gartner-solution-scorecard-amazon-rds-2022/\\" target=\\"_blank\\">Solution Scorecard for Amazon Relational Database Service</a>, with Amazon Web Services earning a 95 rating, including 100% of required criteria by Gartner for an operational database platform as a service (dbPaaS). You can check details of the report <a href=\\"https://aws.amazon.com/blogs/database/gartner-recognizes-amazon-rds-in-new-report/\\" target=\\"_blank\\">here</a>. Although there are several ways to migrate SQL Server databases, using SQL Server backup files is the simplest and most reliable method.</p>\\n<p>In this post, we demonstrate the process to migrate Azure SQL databases to <a href=\\"https://aws.amazon.com/rds/sqlserver/\\" target=\\"_blank\\">Amazon RDS for SQL Server</a>. Specifically, we show how to take the <code>.bacpac</code>file from your Azure SQL database and restore the database to an [Amazon RDS](https://aws.amazon.com/cn/rds/?trk=cndc-detail) for SQL Server database.</p>\\n<h3><a id=\\"Migration_options_4\\"></a><strong>Migration options</strong></h3>\\n<p>Microsoft Azure provides three distinct SQL Server offerings:</p>\n<ul>\\n<li>SQL Server on Azure VMs</li>\n<li>Azure SQL Managed Instance, which is a managed database service that is compatible with the latest SQL Server database engine</li>\n<li>Azure SQL Database, which is a cloud-based managed service that provides features such as serverless compute and Hyperscale storage</li>\n</ul>\\n<p>The standard methodology of migrating a database from source to target servers consists of using the native backup and restore mechanism. SQL Server comes with two common database backup options: the native backup (<code>.bak</code>) file and the data-tier application backup package file (<code>.bacpac</code>). Both options have their pros and cons, which we will discuss in this section.</p>\\n<p>For more information about how to export database to <code>.bacpac</code>file, refer to <a href=\\"https://docs.microsoft.com/en-us/azure/azure-sql/database/database-export?view=azuresql\\" target=\\"_blank\\">exporting database to .bacpac file.</a></p>\\n<h4><a id=\\"Native_backup_files_bak_16\\"></a><strong>Native backup files (.bak)</strong></h4>\\n<p>A native backup is a copy of a database that consists of data records (data file) and log records (log file). Native backup comes with different <a href=\\"https://docs.microsoft.com/en-us/sql/t-sql/statements/backup-transact-sql?view=sql-server-ver16\\" target=\\"_blank\\">types</a> like full backup, differential backup, transactional backup, partial backups, or file group backup. Depending on the <a href=\\"https://docs.microsoft.com/en-us/sql/relational-databases/backup-restore/recovery-models-sql-server?view=sql-server-ver16\\" target=\\"_blank\\">recovery model</a> set for a database, you can achieve point in time recovery. The extension of the backup file is usually denoted as <code>.bak</code>. There are various ways through which we can take a backup of the database, the most common are using SQL Server Management Studio (SSMS) or the command line.</p>\\n<p>Native backup has the following advantages:</p>\n<ul>\\n<li>A full backup takes a complete copy of a database</li>\n<li>A full backup is transactionally consistent</li>\n<li>You can take a portion of the database, like backing up a particular file group, data file, or log file</li>\n<li>It provides point in time recovery with full recovery mode</li>\n<li>You can stripe the backup files for a large database to improve speed and stripe the files across different disks for any storage concerns on a particular disk</li>\n<li>A full backup provides the flexibility to minimize the downtime window on cutover. For more information, refer to <a href=\\"https://aws.amazon.com/blogs/database/migrating-sql-server-to-amazon-rds-using-native-backup-and-restore/\\" target=\\"_blank\\">Migrating SQL Server to Amazon RDS using native backup and restore</a></li>\\n<li>A full backup can be compressed in SQL Server Editions of Enterprise, Standard, and Developer. This <a href=\\"https://aws.amazon.com/blogs/database/migrating-sql-server-to-amazon-rds-using-native-backup-and-restore/\\" target=\\"_blank\\">compressed backup</a> file occupies less space and can be done faster</li>\\n</ul>\n<p>However, native backup has the following challenges:</p>\n<ul>\\n<li>The size of the <code>.bak</code>file is larger compared to the <code>.bacpac</code>file because it takes a full copy of data contained on the database files</li>\\n<li>Azure SQL Database doesn’t allow you to take native backups (<code>.bak</code>) as it uses a different technology for backups. Refer to <a href=\\"https://docs.microsoft.com/en-us/azure/azure-sql/database/automated-backups-overview?view=azuresql&amp;tabs=single-database\\" target=\\"_blank\\">Automated backups</a> to learn more</li>\\n<li>Azure SQL Database doesn’t support taking backups from higher SQL Server versions and restoring to an earlier version.</li>\n</ul>\\n<h4><a id=\\"BACPAC_files_36\\"></a><strong>BACPAC files</strong></h4>\\n<p>A BACPAC (backup package) file consists of copied metadata and the actual data compressed to a file. The extension of this file is usually denoted as <code>.bacpac</code>. This is the best choice for a small database size (approximately 200 GB). We can take a<code>.bacpac</code>file through an Export\\Import data-tier application (GUI) or through a SQLPackage.exe (command line) utility.</p>\\n<p>This method has the following advantages:</p>\n<ul>\\n<li>You can migrate data from different sources to targets with different SQL versions with few compatibility issues</li>\n<li>The <code>.bacpac</code>size is comparatively smaller than a <code>.bak</code>file because it takes the metadata and data copy. It doesn’t take a backup of indexes; instead, it generates scripts to create indexes on the target</li>\\n</ul>\n<p>However, this method has the following challenges:</p>\n<ul>\\n<li>It’s not transactionally consistent. You must take a snapshot and export the database to make it transactionally consistent</li>\n<li>You can’t take a portion of the database, like backing up a particular file group, data file, or log file</li>\n<li>It doesn’t provide point in time recovery</li>\n<li>It is recommended for smaller databases (within 200 GB) because it may be time-consuming to build the database</li>\n</ul>\\n<h3><a id=\\"Solution_overview_52\\"></a><strong>Solution overview</strong></h3>\\n<p>The most common method to migrate SQL Server databases from any source to Amazon RDS for SQL Server is to take the native backup from the SQL Server at the source and copy the backup files to an <a href=\\"http://aws.amazon.com/s3\\" target=\\"_blank\\">Amazon Simple Storage Service</a> ([Amazon S3](https://aws.amazon.com/cn/s3/?trk=cndc-detail)) bucket. Then, you restore the backup files to [Amazon RDS](https://aws.amazon.com/cn/rds/?trk=cndc-detail) for SQL Server. For more information, see <a href=\\"https://docs.aws.amazon.com/AmazonRDS/latest/UserGuide/SQLServer.Procedural.Importing.html#SQLServer.Procedural.Importing.Native.Enabling\\" target=\\"_blank\\">Setting up for native backup and restore.</a></p>\\n<p>For migrating SQL databases from the Azure cloud platform to Amazon RDS for SQL Server using the native backup and restore method, we can either use <code>.bak</code>(native backups) or <code>.bacpac</code>(backup package). The following figures illustrate the supportability of backup options for the three offerings in Azure for SQL Server databases.</p>\\n<p>The first figure shows how <code>.bak</code>and <code>.bacpac</code>files are supported in SQL Server on a virtual machine and SQL managed instance offerings.</p>\\n<p><img src=\\"https://dev-media.amazoncloud.cn/8ce0c466a9d045189c19675f5766e7b3_image.png\\" alt=\\"image.png\\" /></p>\n<p>The following figure shows how Azure SQL database supports only <code>.bacpac</code>file and not <code>.bak</code>.</p>\\n<p><img src=\\"https://dev-media.amazoncloud.cn/4da5e545c3b6405cac6256fc1ebe7dff_image.png\\" alt=\\"image.png\\" /></p>\n<p>The following table summarizes the backup options for migrating SQL Server databases from different offerings of Azure (Azure SQL on VMs, Azure SQL Managed Instance, Azure SQL Database) using <code>.bacpac</code>and <code>.bak</code>files, which are taken to either Azure Storage or physical disks.</p>\\n<p><img src=\\"https://dev-media.amazoncloud.cn/015f2dba446d4a2ebf6c71f4b5818319_image.png\\" alt=\\"image.png\\" /></p>\n<h3><a id=\\"Perform_a_backup_using_bak_70\\"></a><strong>Perform a backup using .bak</strong></h3>\\n<p>This procedure involves performing a full backup of the SQL database hosted in Azure (supported in Azure SQL on VMs and Azure SQL Managed Instance offerings only), followed by differential and log backups, and then restoring the same backups to the target RDS for SQL Server instance, with the help of differential and log backups. Following this process can reduce application cutover time during the migration process. For more information, refer to <a href=\\"https://aws.amazon.com/blogs/database/migrating-sql-server-to-amazon-rds-using-native-backup-and-restore/\\" target=\\"_blank\\">Migrating SQL Server to Amazon RDS using native backup and restore.</a></p>\\n<h3><a id=\\"Perform_a_backup_using_bacpac_74\\"></a><strong>Perform a backup using .bacpac</strong></h3>\\n<p>The following is the detailed procedure to migrate an Azure SQL database (PaaS solution) to Amazon RDS for SQL Server using the <code>.bacpac </code>option. We walk through the steps for taking a <code>.bacpac</code>file from an Azure SQL database using an export data-tier application and restoring the <code>.bacpac</code>file using SQLPackage.exe on [Amazon RDS](https://aws.amazon.com/cn/rds/?trk=cndc-detail) for SQL Server.</p>\\n<p>We can export a <code>.bacpac</code>file from Azure Portal using an Export data-tier application or through the command line utility SQLPackage.exe to available storage. Then, copy the <code>.bacpac</code>file to <a href=\\"http://aws.amazon.com/ec2\\" target=\\"_blank\\">Amazon Elastic Compute Cloud</a> (Amazon EC2). From there, you can connect to the target RDS for SQL Server database, preferably in the same region and availability zone.</p>\\n<p>Amazon RDS for SQL Server doesn’t support importing the <code>.bacpac</code>file using a data-tier application. The only way to import the <code>.bacpac</code>file is to use <code>sqlpackage.exe</code>.</p>\\n<p>In order for the <code>.bacpac</code>file to be transactionally consistent, make sure that no write activity is occurring during the export or exporting a transactionally consistent copy. Take a snapshot and then export the <code>.bacpac</code>to make it transactionally consistent, then delete the snapshot after a successful export of <code>.bacpac</code>.</p>\\n<p>The following are the high-level steps to export and import <code>.bacpac</code>files from your Azure SQL database to [Amazon RDS](https://aws.amazon.com/cn/rds/?trk=cndc-detail) for SQL Server:</p>\\n<ol>\\n<li>Export the <code>.bacpac</code>file from your Azure SQL database</li>\\n<li>Copy the <code>.bacpac</code>file from Azure Storage to [Amazon EC2 ](https://aws.amazon.com/cn/ec2/?trk=cndc-detail)EBS storage</li>\\n<li>Import the <code>.bacpac</code>file to [Amazon RDS](https://aws.amazon.com/cn/rds/?trk=cndc-detail) for SQL Server</li>\\n</ol>\n<p>The following diagram illustrates this process.</p>\n<p><img src=\\"https://dev-media.amazoncloud.cn/da8313457f7242ec8e2b65dc9ae39c32_image.png\\" alt=\\"image.png\\" /></p>\n<h3><a id=\\"Export_the_bacpac_file_from_the_Azure_SQL_database_94\\"></a><strong>Export the .bacpac file from the Azure SQL database</strong></h3>\\n<p>You can choose from the following methods to export the <code>.bacpac</code>file and save it to storage (either to Azure Storage or a physical file system):</p>\\n<ul>\\n<li>Azure Portal</li>\n<li>SQLPackage.exe</li>\n<li>Export data-tier application</li>\n</ul>\\n<p><strong>Azure Portal</strong></p>\\n<p>To use the Azure Portal, complete the following steps:</p>\n<ol>\\n<li>Open the Azure Portal and search for the Azure SQL database that you want to migrate</li>\n<li>Choose <strong>Export</strong><br />\\nFigure 1, highlights the export option available on the toolbar of Azure Portal.</li>\n</ol>\\n<p><img src=\\"https://dev-media.amazoncloud.cn/37bd598661d246af9b784164f2c69e44_image.png\\" alt=\\"image.png\\" /></p>\n<p>Figure 1: Highlights the Export utility in Azure Portal</p>\n<p>You’re redirected to the next page to save the <code>.bacpac</code>file to Azure Storage as shown in Figure 2.</p>\\n<ol start=\\"3\\">\\n<li>For <strong>File name</strong>, leave the default file name or enter a new name</li>\\n<li>Choose an existing Azure Storage account to export the <code>.bacpac</code>file</li>\\n<li>Provide the appropriate credentials to access the source database (the SQL Server admin login and password you used to connect to the Azure SQL database)</li>\n<li>Choose <strong>OK</strong></li>\\n</ol>\n<p><img src=\\"https://dev-media.amazoncloud.cn/473d63a910e242929a746dd69c048705_image.png\\" alt=\\"image.png\\" /></p>\n<p>Figure 2: Export database wizard in Azure Portal</p>\n<p>You’re redirected to the next page where you can download the <code>.bacpac</code>file.</p>\\n<ol start=\\"7\\">\\n<li>Select the <code>.bacpac</code>file</li>\\n<li>On the options menu, choose <strong>Download</strong><br />\\nFigure 3, highlights the download option available on clicking the three dots.</li>\n</ol>\\n<p><img src=\\"https://dev-media.amazoncloud.cn/3b15b650998048abbee9ad14b8322764_image.png\\" alt=\\"image.png\\" /></p>\n<p>Figure 3: Highlights Download Option</p>\n<p><strong>SQLPacakge.exe</strong></p>\\n<p>SQLPackage is a command line utility to manage the data-tier application. It helps extract SQL objects from your database to a file. To export a SQL database using the <a href=\\"https://docs.microsoft.com/en-us/sql/tools/sqlpackage\\" target=\\"_blank\\">SQLPackage</a> command-line utility, download the latest version of the <a href=\\"https://docs.microsoft.com/en-us/sql/tools/sqlpackage/sqlpackage-download\\" target=\\"_blank\\">SQLPackage</a> utility to a path and run the following command from a CMD shell to export the <code>.bacpac</code>file and copy this file to Amazon EC2, where the target RDS for SQL Server database is connected. For more information about SQLPackage Export parameters, refer to <a href=\\"https://docs.microsoft.com/en-us/sql/tools/sqlpackage/sqlpackage-export?view=sql-server-ver16\\" target=\\"_blank\\">SqlPackage Export parameters and properties.</a></p>\\n<p>See the following code:</p>\n<pre><code class=\\"lang-\\">sqlpackage.exe /Action:Export /ssn:”tcp:ServerName” /sdn:DatabaseName /su:UserName /sp:Password /tf:”TargetFile” /p:Storage=File\\n</code></pre>\\n<p>For example:</p>\n<pre><code class=\\"lang-\\">sqlpackage.exe /Action:Export /ssn:”tcp:azuresqldbxx.database.windows.net,1433”/sdn:testdb /su:AdminUser /sp:AdminPassword1 /tf:”C:\\\\testdb.bacpac” /p:Storage=File\\n</code></pre>\\n<p><strong>Export data-tier application</strong></p>\\n<p>SQL Server Management Studio (SSMS) has a wizard to export a database in an Azure SQL database to a BACPAC file.</p>\n<ol>\\n<li>Open SSMS and connect to the Azure SQL database</li>\n<li>Choose the database (right-click) and on the <strong>Tasks</strong> menu, choose <strong>Export Data-tier Application</strong> as highlighted in Figure 4.</li>\\n</ol>\n<p><img src=\\"https://dev-media.amazoncloud.cn/5ead87bf557649b8a984b2182b47761a_image.png\\" alt=\\"image.png\\" /></p>\n<p>Figure 4: Export Data-tier Application</p>\n<ol start=\\"3\\">\\n<li>Provide the target location to which the <code>.bacpac</code>file will be saved. Figure 5, highlights the path provided.</li>\\n</ol>\n<p><img src=\\"https://dev-media.amazoncloud.cn/11978c446ba740c7b8523b12f1ef0d93_image.png\\" alt=\\"image.png\\" /></p>\n<p>Figure 5: Export setting wizard</p>\n<ol start=\\"4\\">\\n<li>Copy this file to Amazon EC2, where the target RDS for SQL Server database is connected.</li>\n</ol>\\n<h4><a id=\\"Copy_the_bacpac_file_from_Azure_Storage_to_Amazon_EC2_EBS_storage_169\\"></a><strong>Copy the .bacpac file from Azure Storage to Amazon EC2 EBS storage</strong></h4>\\n<p>After the export is successfully saved to the physical disk, copy the <code>.bacpac</code>file from the saved location to [Amazon EC2 ](https://aws.amazon.com/cn/ec2/?trk=cndc-detail)from where the target RDS for SQL Server database is connected. <a href=\\"https://docs.aws.amazon.com/AWSEC2/latest/UserGuide/AmazonS3.html\\" target=\\"_blank\\">Use S3 with Amazon EC2</a> to copy files. You need to import the <code>.bacpac</code>file to restore the database on [Amazon RDS](https://aws.amazon.com/cn/rds/?trk=cndc-detail) for SQL Server.</p>\\n<h4><a id=\\"Import_the_bacpac_file_to_Amazon_RDS_for_SQL_Server_173\\"></a><strong>Import the .bacpac file to Amazon RDS for SQL Server</strong></h4>\\n<p>Amazon RDS for SQL Server supports SQLPackage.exe to import the <code>.bacpac</code>file. The GUI version using the Import data-tier application is not supported and fails with sqladmin permission error. Use the <a href=\\"https://docs.microsoft.com/en-us/sql/tools/sqlpackage\\" target=\\"_blank\\">SQLPackage.exe</a> utility and run the following command from a CMD shell from [Amazon EC2 ](https://aws.amazon.com/cn/ec2/?trk=cndc-detail)to import the <code>.bacpac</code>file into the target RDS for SQL Server database.</p>\\n<pre><code class=\\"lang-\\">sqlpackage.exe /Action:Import /sf:”Path to bacpac file” /tsn:”RDS for SQL Server Endpoint” /tdn:Target DatabaseName /tu:UserName /tp:Password\\n</code></pre>\\n<p>For example:</p>\n<pre><code class=\\"lang-\\">sqlpackage /a:Import /sf:”c:\\\\sql\\\\testdbaz.bacpac” /tsn:”rdstestxxxx.xxxxxxxxxxxx.useast-1.rds.amazon.com” /tdn:AZTESTDBXX /tu:sqladmin /tp:xxxxxxxxxxxx\\n</code></pre>\\n<p>Figure 6, shows a successful import of our Azure SQL database using SQLPackage.exe.</p>\n<p><img src=\\"https://dev-media.amazoncloud.cn/791c3c98b56c4481ba32873e28ed5ada_image.png\\" alt=\\"image.png\\" /></p>\n<p>Figure 6: SQLPackage.exe sample execution</p>\n<p>For more information, refer to <a href=\\"https://docs.microsoft.com/en-us/sql/relational-databases/data-tier-applications/import-a-bacpac-file-to-create-a-new-user-database?view=sql-server-ver15\\" target=\\"_blank\\">Import a BACPAC File to Create a New User Database</a>. To learn more about SQLPackage Import parameters, refer to <a href=\\"https://docs.microsoft.com/en-us/sql/tools/sqlpackage/sqlpackage-import?view=sql-server-ver16\\" target=\\"_blank\\">SqlPackage Import parameters and properties.</a></p>\\n<h3><a id=\\"Summary_194\\"></a><strong>Summary</strong></h3>\\n<p>In this post, we covered how you can migrate Azure SQL Server databases to Amazon RDS for SQL Server using the <code>.bacpac</code>file, SQLPackage, and a data-tier application. With a successful migration to [Amazon RDS](https://aws.amazon.com/cn/rds/?trk=cndc-detail) for SQL Server, you can focus on what matters to your organization’s business, such as optimizing database design, capacity sizing, and performance tuning.</p>\\n<p>If you have any questions or suggestions, leave a comment.</p>\n<h4><a id=\\"About_the_authors_201\\"></a><strong>About the authors</strong></h4>\\n<p><img src=\\"https://dev-media.amazoncloud.cn/55c839d69c754a63a5fab55a9e015083_image.png\\" alt=\\"image.png\\" /></p>\n<p><strong>InduTeja Aligeti</strong> is a Lead Database Consultant at Amazon Web Services. She has 16+years of experience working with Microsoft Technologies with a specialization in SQL Server. She focuses on helping customers to build high-available, cost-effective database solutions and migrate their large scale SQL Server databases to Amazon Web Services.</p>\\n<p><img src=\\"https://dev-media.amazoncloud.cn/5bc4d007ee1a47b1add9c48ef54109c7_image.png\\" alt=\\"image.png\\" /></p>\n<p><strong>Yogi Barot</strong> is Microsoft Specialist Principal Solutions Architect at Amazon Web Services, she has 22 years of experience working with different Microsoft technologies, her specialty is in SQL Server and different database technologies. Yogi has in depth Amazon Web Services knowledge and expertise in running Microsoft workload on Amazon Web Services.</p>\n"}