The data archive mechanism with Azure SQL Database generally will be an export from Azure SQL Database. Export creates a non–transactionally consistent copy of some or all database tables using the BACPAC format (schema and data). This BACPAC file can be stored in Azure blog storage and/or downloaded to an on-premises environment and imported when desired to either Azure SQL Database or SQL Server.
To obtain a transactionally consistent export (without using third-party tools), you must either ensure that no writes occur on the source database during the export or create a database copy (which is guaranteed to be transactionally consistent).
Microsoft provides a number of options for you to use to export your data to a BACPAC file. These are as follows:
Using the Azure portal
Using the Export Data Tier Application Wizard in Microsoft SQL Server Management Studio, version 13.0.11000 or higher (Download SQL Server Management Studio)
Using the SQLPackage.exe command-line utility
Using the Start-AzureSqlDatabaseExport PowerShell cmdlet, which can be combined with the Start-AzureSqlDatabaseCopy PowerShell cmdlet
Generally, for databases smaller than 200 GB, using the Azure portal is the simplest method. If the operation goes over 20 hours, it may be cancelled. If the export does not have a clustered index, it may fail if it takes longer than 6 to 12 hours.
Source of Information : Migrating SQL Server Databases to Azure
Showing posts with label Azure SQL Server. Show all posts
Showing posts with label Azure SQL Server. Show all posts
Data archiving with SQL Server in an Azure virtual machine
The data archive mechanism with SQL Server in an Azure virtual machine generally will be a full SQL Server backup that you store for the length of time required. If you are managing your own backups, you will want to store your full database backups in Azure blob storage standard storage (or archive outside Azure). If you are not already using backup to URL to store your backups in Azure blob storage, you will want to copy from your attached disks in premium storage to Azure blob storage using standard storage.
If you are using managed backup and want to archive a full database backup for longer than 30 days, you will need to copy a full backup to a new storage location or periodically perform a full database backup (to URL) on a scheduled basis and store it in standard storage for the length of time required.
If you are using file-snapshot backup, you have another option to archive your database: you periodically can copy the backup snapshots for a given backup set to long-term storage. At any point in time, you can choose to restore to a new database when needed and create a traditional (streaming) SQL Server backup for long-term storage.
Source of Information : Migrating SQL Server Databases to Azure
If you are using managed backup and want to archive a full database backup for longer than 30 days, you will need to copy a full backup to a new storage location or periodically perform a full database backup (to URL) on a scheduled basis and store it in standard storage for the length of time required.
If you are using file-snapshot backup, you have another option to archive your database: you periodically can copy the backup snapshots for a given backup set to long-term storage. At any point in time, you can choose to restore to a new database when needed and create a traditional (streaming) SQL Server backup for long-term storage.
Source of Information : Migrating SQL Server Databases to Azure
Database backups with Azure SQL Database
Database backups with Azure SQL Database are performed for you automatically, and you have no option to perform manual database backups. To protect your data and enable point-in-time restore services, SQL Database takes full backups every week, multiple differential backups every day, and log backups every five minutes. Backup files are stored in geo-redundant storage with read access (RA-GRS) to ensure backups’ availability for disaster recovery purposes. The first full backup is scheduled immediately after a database is created. After the first full backup, all backups are scheduled automatically and managed silently in the background. The exact timing of full and differential backups is determined by the system to balance overall load.
SQL Database provides a point-in-time restore self-service feature for all databases regardless of service tier, but with different restore points as follows:
Basic: Any restore point within 7 days
Standard: Any restore point within 14 days
Premium: Any restore point within 35 days
Point-in-time restore (geo-restore) is built on top of the SQL Database automated backup system and enables you to restore an existing or deleted database to a new database as of a specified point in time. You can restore using the Azure portal, PowerShell, or the REST API. The walk-through below will demonstrate restoring a database to a point in time using the Azure portal.
When restoring an existing database, you can restore to the same logical server or to a different server. When restoring to the same server, you create a new database. Unlike SQL Server, you cannot restore over an existing database. As a result, you will have additional storage costs while both databases exist—unless you choose to delete the existing database before restoring from the backups.
Deleting a logical server also deletes all of its databases and their database backups, after which they cannot be restored regardless of the retention period discussed previously for the automated backups. To store data for longer than the period of time provided by your service tier, you will need to export data to a BACPAC file.
Source of Information : Migrating SQL Server Databases to Azure
SQL Database provides a point-in-time restore self-service feature for all databases regardless of service tier, but with different restore points as follows:
Basic: Any restore point within 7 days
Standard: Any restore point within 14 days
Premium: Any restore point within 35 days
Point-in-time restore (geo-restore) is built on top of the SQL Database automated backup system and enables you to restore an existing or deleted database to a new database as of a specified point in time. You can restore using the Azure portal, PowerShell, or the REST API. The walk-through below will demonstrate restoring a database to a point in time using the Azure portal.
When restoring an existing database, you can restore to the same logical server or to a different server. When restoring to the same server, you create a new database. Unlike SQL Server, you cannot restore over an existing database. As a result, you will have additional storage costs while both databases exist—unless you choose to delete the existing database before restoring from the backups.
Deleting a logical server also deletes all of its databases and their database backups, after which they cannot be restored regardless of the retention period discussed previously for the automated backups. To store data for longer than the period of time provided by your service tier, you will need to export data to a BACPAC file.
Source of Information : Migrating SQL Server Databases to Azure
SQL Server Managed Backup to Windows Azure
You can use SQL Server Managed Backup to Windows Azure and have SQL Server manage and automate SQL Server backups to Azure blob storage. With this option, you specify the blob storage location and the retention period and optionally customize backup at the database level. The maximum backup size is 12.8 TB. Managed backup supports point-in-time restore during the retention period. By default, managed backups are enabled at the instance level, with the same retention period for all databases (including newly added databases). You can change the retention period on an individual database, create a custom backup schedule, or disable managed backup for an individual database using Transact-SQL. You also have the option to encrypt the database backups for additional security.
Managed backup was introduced with SQL Server 2014, and the functionality increased substantially with SQL Server 2016. For the purposes of this ebook, I will focus on SQL Server 2016.
With SQL Server 2016, if you do not specify a custom schedule, the type of backups scheduled and the backup frequency is determined based on the workload of the database.
A full database for a database is taken in the following instances:
Managed backup was introduced with SQL Server 2014, and the functionality increased substantially with SQL Server 2016. For the purposes of this ebook, I will focus on SQL Server 2016.
With SQL Server 2016, if you do not specify a custom schedule, the type of backups scheduled and the backup frequency is determined based on the workload of the database.
A full database for a database is taken in the following instances:
- When managed backup is enabled for the first time
- Log growth since the last full backup is 1 GB or more
- One week has passed since the last full backup
- The log chain is broken, such as through a manual backup
A transaction log backup is taken in the following instances:
- No log backup history can be found. This usually is true when SQL Server Managed Backup to Windows Azure is enabled for the first time.
- The transaction log space used is 5 MB or larger.
- The maximum time interval of two hours since the last log backup is reached.
- Anytime the transaction log backup is lagging behind a full database backup. The goal is to keep the log chain ahead of full backup.
To use SQL Server managed backup, you create the following prerequisites:
Create an Azure storage account.
Create a container within the storage account.
Generate a Shared Access policy and apply the Shared Access Signature (SAS) token to the Azure blob container.
Create a SQL Server credential based on the SAS token.
CREATE CREDENTIAL WITH IDENTITY = 'SHARED ACCESS SIGNATURE', SECRET = '' ;
After satisfying these prerequisites, you use Transact-SQL to interact with SQL Server Managed Backup to Windows Azure. There are 13 system stored procedures and functions for enabling, configuring, and monitoring SQL Server Managed Backup to Windows Azure. System functions are used to retrieve existing configuration settings, parameter values, and backup file information. Each of these system stored procedures and functions provides code examples for you.
Extended events are used to surface errors and warnings. Alert mechanisms are enabled through SQL Agent jobs and SQL Server Policy-Based Management.
PowerShell cmdlets are also available to configure SQL Server Managed Backup to Windows Azure. SQL Server Management Studio supports restoring backups created by SQL Server Managed Backup to Windows Azure by using the Restore Database task.
Source of Information : Migrating SQL Server Databases to Azure
SQL Server backup to URL
With SQL Server backup to URL, you specify an Azure blob storage container as the destination for your full, differential, and transaction log backups, and you manage the backup process and the retention strategy. A primary reason to use SQL Server backup to URL is to avoid the cost of backing up to attached disks that use premium storage (although you always can back up to attached premium storage for performance and then archive to blob storage).
To use this backup mechanism, take the following steps:
Create an Azure storage account.
Create a container within the storage account to contain your backup files.
Generate a Shared Access policy and apply the Shared Access Signature (SAS) token to the Azure blob container.
Create a SQL Server credential based on the SAS token.
CREATE CREDENTIAL WITH IDENTITY = 'SHARED ACCESS SIGNATURE', SECRET = '' ;
Specify TO URL as an argument with your BACKUP DATABASE and BACKUP LOG commands.
BACKUP DATABASE AdventureWorks2016 TO URL = 'https://.blob.core.windows.net//AdventureWorks2016.bak;
Backup to URL is a strategy you will want to use to save money when your Azure virtual machine is using premium storage because SQL Server backup to URL uses standard storage. The maximum size for a single file using a SAS token is 200 GB, and the maximum backup size using a striped backup set is 12.8 terabytes (TB).
Note You can back up to a single backup with a maximum size of 1 TB using the legacy SQL Server 2014 syntax of WITH CREDENTIAL that utilizes the storage account access key rather than a SAS key. This syntax is being deprecated.
Source of Information : Migrating SQL Server Databases to Azure
To use this backup mechanism, take the following steps:
Create an Azure storage account.
Create a container within the storage account to contain your backup files.
Generate a Shared Access policy and apply the Shared Access Signature (SAS) token to the Azure blob container.
Create a SQL Server credential based on the SAS token.
CREATE CREDENTIAL
Specify TO URL as an argument with your BACKUP DATABASE and BACKUP LOG commands.
BACKUP DATABASE AdventureWorks2016 TO URL = 'https://
Backup to URL is a strategy you will want to use to save money when your Azure virtual machine is using premium storage because SQL Server backup to URL uses standard storage. The maximum size for a single file using a SAS token is 200 GB, and the maximum backup size using a striped backup set is 12.8 terabytes (TB).
Note You can back up to a single backup with a maximum size of 1 TB using the legacy SQL Server 2014 syntax of WITH CREDENTIAL that utilizes the storage account access key rather than a SAS key. This syntax is being deprecated.
Source of Information : Migrating SQL Server Databases to Azure
User authentication with SQL Server in an Azure virtual machine
The default authentication mode for SQL Server in an Azure virtual machine (and in a SQL Server on-premises installation) is Windows Authentication mode. With this mode, users must connect and be authenticated using an Active Directory user account. In this mode, SQL Server Authentication is disabled and the SA login account is also disabled. You can change the SQL Server Authentication mode to mixed mode authentication. Mixed mode enables both Windows Authentication and SQL Server Authentication. Windows Authentication always is available and cannot be disabled.
User authentication using mixed mode
If you do not join your Azure virtual machine to an Active Directory domain, you generally will use SQL Server Authentication for user authentication. SQL Server Authentication is less secure than Active Directory authentication for the following reasons:
The encrypted SQL Server Authentication login password must be passed over the network at the time of the connection. Some applications that connect automatically will store the password at the client. These are additional attack points.
If a user is an Active Directory domain user who has a login and password, the user must provide SQL Server login and password to connect. Keeping track of multiple names and passwords is difficult for many users. Having to provide SQL Server credentials every time one connects to the database can be annoying.
SQL Server Authentication cannot use Kerberos security protocol.
However, SQL Server Authentication may be the best option for SQL Server in an Azure virtual machine in the following scenarios:
If the SQL Server database users do not have Active Directory user accounts and cannot be authenticated by an Active Directory domain
If the web-based applications accessing SQL Server in the virtual machine allow users to create their own identities
If the software applications accessing the SQL Server instance use a complex permission hierarchy based on known SQL Server logins
To use SQL Server Authentication, you must enable mixed mode authentication and restart the SQL Server instance. When you do so, take the following steps to increase the security for your SQL Server instance:
Use an account other than the SA login for SQL Server administration.
Enforce password expiration.
Enforce Windows password policies, including password length and complexity.
Require users to change their password at their next login.
When configuring SQL Server in an Azure virtual machine using the Resource Manager deployment model, you have the option to have Azure configure SQL Server for mixed authentication mode when SQL Server is configured on the Azure virtual machine. When you do so, the default SQL Server login account and password are the same as the local administrator account for the virtual machine, although you can define a different account. Password policies are enforced, and the SA login account is not enabled.
If you do not change the authentication mode during the initial configuration of the SQL Server instance from the SQL Server default of Windows Authentication mode to mixed mode, you can use Windows remote desktop to connect to the virtual machine and then use SQL Server Management Studio to connect to SQL Server locally and change the authentication mode if desired.
User authentication using Windows Authentication mode
If you join your Azure virtual machine to an Active Directory domain, you can leave SQL Server in Windows Authentication mode and require your users to connect and be authenticated using Active Directory authentication. When a user connects through an Active Directory user account, SQL Server validates the account name and password using the Active Directory principal token in the operating system. This means the user identity is confirmed by Active Directory. SQL Server does not ask for the password and does not perform the identity validation. Active Directory authentication uses Kerberos security protocol, provides password policy enforcement with regard to complexity validation for strong passwords, provides support for account lockout, and supports password expiration.
In addition, using Windows Authentication mode enables you to manage authentication using groups rather than individual user accounts and avoid the proliferation of user identities across database servers.
Source of Information : Migrating SQL Server Databases to Azure
User authentication using mixed mode
If you do not join your Azure virtual machine to an Active Directory domain, you generally will use SQL Server Authentication for user authentication. SQL Server Authentication is less secure than Active Directory authentication for the following reasons:
The encrypted SQL Server Authentication login password must be passed over the network at the time of the connection. Some applications that connect automatically will store the password at the client. These are additional attack points.
If a user is an Active Directory domain user who has a login and password, the user must provide SQL Server login and password to connect. Keeping track of multiple names and passwords is difficult for many users. Having to provide SQL Server credentials every time one connects to the database can be annoying.
SQL Server Authentication cannot use Kerberos security protocol.
However, SQL Server Authentication may be the best option for SQL Server in an Azure virtual machine in the following scenarios:
If the SQL Server database users do not have Active Directory user accounts and cannot be authenticated by an Active Directory domain
If the web-based applications accessing SQL Server in the virtual machine allow users to create their own identities
If the software applications accessing the SQL Server instance use a complex permission hierarchy based on known SQL Server logins
To use SQL Server Authentication, you must enable mixed mode authentication and restart the SQL Server instance. When you do so, take the following steps to increase the security for your SQL Server instance:
Use an account other than the SA login for SQL Server administration.
Enforce password expiration.
Enforce Windows password policies, including password length and complexity.
Require users to change their password at their next login.
When configuring SQL Server in an Azure virtual machine using the Resource Manager deployment model, you have the option to have Azure configure SQL Server for mixed authentication mode when SQL Server is configured on the Azure virtual machine. When you do so, the default SQL Server login account and password are the same as the local administrator account for the virtual machine, although you can define a different account. Password policies are enforced, and the SA login account is not enabled.
If you do not change the authentication mode during the initial configuration of the SQL Server instance from the SQL Server default of Windows Authentication mode to mixed mode, you can use Windows remote desktop to connect to the virtual machine and then use SQL Server Management Studio to connect to SQL Server locally and change the authentication mode if desired.
User authentication using Windows Authentication mode
If you join your Azure virtual machine to an Active Directory domain, you can leave SQL Server in Windows Authentication mode and require your users to connect and be authenticated using Active Directory authentication. When a user connects through an Active Directory user account, SQL Server validates the account name and password using the Active Directory principal token in the operating system. This means the user identity is confirmed by Active Directory. SQL Server does not ask for the password and does not perform the identity validation. Active Directory authentication uses Kerberos security protocol, provides password policy enforcement with regard to complexity validation for strong passwords, provides support for account lockout, and supports password expiration.
In addition, using Windows Authentication mode enables you to manage authentication using groups rather than individual user accounts and avoid the proliferation of user identities across database servers.
Source of Information : Migrating SQL Server Databases to Azure
Configuring and securing connections to Azure SQL Database
With Azure SQL Database, Azure configures the network for you and by default restricts all access using Azure SQL Database firewall rules, as discussed in Chapter 3, “Getting started with an Azure SQL Database.” If a firewall rule does not exist, Azure will reject all connection attempts from IP addresses that have not been whitelisted explicitly.
Use database-level firewall rules in conjunction with contained users (discussed below) whenever possible to make your database more portable.
Use server-level firewall rules when you have many databases that have the same access requirements and you don’t want to spend time configuring each database individually.
Additionally, Azure SQL Database requires encrypted connections at all times while data is “in transit” to and from the database. In your application’s connection string, you must specify parameters to encrypt the connection and not to trust the server certificate (this is done for you if you copy your connection string out of the Azure portal). If you do not, the connection will not verify the identity of the server and will be susceptible to “man-in-the-middle” attacks. For the ADO.NET driver, for instance, these connection string parameters are Encrypt=True and TrustServerCertificate=False. For more information, see Azure SQL Database Connection Encryption and Certificate Validation. The code block below shows a sample ADO.NET connection string.
Server=tcp:[your_sql_database_server_name_here].database.windows.net,1433;Database=[your_sql_database_name_here]>;User ID=[your_username_here]@[your_sql_database_server_name_here];Password={your_password_here};Encrypt=True;TrustServerCertificate=False;Connection Timeout=30;
Unlike SQL Server in an Azure virtual machine, with Azure SQL Database you do not have to secure the operating system itself. However, all Azure subscription administrators have access to the SQL Database instance, and you should limit subscription administrators.
Source of Information : Migrating SQL Server Databases to Azure
Use database-level firewall rules in conjunction with contained users (discussed below) whenever possible to make your database more portable.
Use server-level firewall rules when you have many databases that have the same access requirements and you don’t want to spend time configuring each database individually.
Additionally, Azure SQL Database requires encrypted connections at all times while data is “in transit” to and from the database. In your application’s connection string, you must specify parameters to encrypt the connection and not to trust the server certificate (this is done for you if you copy your connection string out of the Azure portal). If you do not, the connection will not verify the identity of the server and will be susceptible to “man-in-the-middle” attacks. For the ADO.NET driver, for instance, these connection string parameters are Encrypt=True and TrustServerCertificate=False. For more information, see Azure SQL Database Connection Encryption and Certificate Validation. The code block below shows a sample ADO.NET connection string.
Server=tcp:[your_sql_database_server_name_here].database.windows.net,1433;Database=[your_sql_database_name_here]>;User ID=[your_username_here]@[your_sql_database_server_name_here];Password={your_password_here};Encrypt=True;TrustServerCertificate=False;Connection Timeout=30;
Unlike SQL Server in an Azure virtual machine, with Azure SQL Database you do not have to secure the operating system itself. However, all Azure subscription administrators have access to the SQL Database instance, and you should limit subscription administrators.
Source of Information : Migrating SQL Server Databases to Azure
Configuring and securing connections to SQL Server in an Azure virtual machine
With an Azure virtual machine, you have several options to restrict and secure connections to your SQL Server instance. The virtual network for your Azure virtual machine is a logical isolation of the Azure cloud dedicated to your subscription. You can fully control the IP address blocks, DNS settings, security policies, and route tables within this network, similarly to how you use these mechanisms to control your on-premises network. You can also segment your virtual network into subnets to further control access to the virtual machines on your virtual network that host your SQL Server instances.
In addition, you can connect the virtual network to your on-premises network using one of the connectivity options available for Azure virtual machines. In essence, you can expand your on-premises network to your Azure virtual network, delivering the benefit of enterprise scale that Azure provides. Finally, you can set up and configure an Azure virtual machine as a domain controller and join your SQL Server virtual machine to this Azure domain controller. This Azure domain controller can be federated with Azure Active Directory, be federated with your on-premises Active Directory, or be a controller within your existing on-premises Active Directory. A full discussion of your options and best practices for configuring a secure connection to your on-premises network is beyond the scope of this ebook. If you do so, you can join your virtual machine to your on-premises Active Directory environment and use Windows user accounts for authentication.
In addition to configuring and securing the virtual network to which your Azure virtual network is connected, you should take these security steps:
Use a unique local administrator account for your virtual machine that does not have a name of Administrator.
Use complex strong passwords for all of your accounts, Windows and SQL Server. For more information about how to create a strong password, see the Create Strong Passwords article in the Safety and Security Center.
Enable encrypted connections for your SQL Server instance and configure your SQL Server instance with a signed certificate.
Use Windows firewall rules to control database engine access.
If your virtual machines should be accessed only from a specific network, use network security groups (NSGs) to control traffic and restrict access to certain IP addresses or network subnets. An NSG contains access control rules that allow or deny traffic based on traffic direction, protocol, source address and port, and destination address and port.
Source of Information : Migrating SQL Server Databases to Azure
In addition, you can connect the virtual network to your on-premises network using one of the connectivity options available for Azure virtual machines. In essence, you can expand your on-premises network to your Azure virtual network, delivering the benefit of enterprise scale that Azure provides. Finally, you can set up and configure an Azure virtual machine as a domain controller and join your SQL Server virtual machine to this Azure domain controller. This Azure domain controller can be federated with Azure Active Directory, be federated with your on-premises Active Directory, or be a controller within your existing on-premises Active Directory. A full discussion of your options and best practices for configuring a secure connection to your on-premises network is beyond the scope of this ebook. If you do so, you can join your virtual machine to your on-premises Active Directory environment and use Windows user accounts for authentication.
In addition to configuring and securing the virtual network to which your Azure virtual network is connected, you should take these security steps:
Use a unique local administrator account for your virtual machine that does not have a name of Administrator.
Use complex strong passwords for all of your accounts, Windows and SQL Server. For more information about how to create a strong password, see the Create Strong Passwords article in the Safety and Security Center.
Enable encrypted connections for your SQL Server instance and configure your SQL Server instance with a signed certificate.
Use Windows firewall rules to control database engine access.
If your virtual machines should be accessed only from a specific network, use network security groups (NSGs) to control traffic and restrict access to certain IP addresses or network subnets. An NSG contains access control rules that allow or deny traffic based on traffic direction, protocol, source address and port, and destination address and port.
Source of Information : Migrating SQL Server Databases to Azure
Migrating a compatible SQL Server database to SQL Database
To migrate a compatible SQL Server database to Azure SQL Database, Microsoft provides several migration methods for various scenarios. The method you choose depends upon your tolerance for downtime, the size of your SQL Server database, and the speed and quality of your connection to the Microsoft Azure cloud.
If you can afford some downtime or you are performing a test migration of a production database for later migration, consider one of the following three methods:
SSMS Migration Wizard: For small to medium databases, migrating a compatible SQL Server 2005 or later database is as simple as running the Deploy Database to Microsoft Azure SQL Database Wizard in SQL Server Management Studio.
Export to BACPAC file and then Import from BACPAC file: If you have connectivity challenges (no connectivity, low bandwidth, or timeout issues) and for medium to large databases, use this manual method. With this method, you export the SQL Server schema and data to a BACPAC file and then import the BACPAC file into SQL Database using either the Deploy Database to Microsoft Azure SQL Database Wizard in SQL Server Management Studio or the SqlPackage command-prompt utility.
Use BACPAC and BCP together: Use a BACPAC file and BCP for very large databases to achieve greater parallelization for increased performance, albeit with greater complexity. With this method, migrate the schema and the data separately.
If you can afford some downtime or you are performing a test migration of a production database for later migration, consider one of the following three methods:
SSMS Migration Wizard: For small to medium databases, migrating a compatible SQL Server 2005 or later database is as simple as running the Deploy Database to Microsoft Azure SQL Database Wizard in SQL Server Management Studio.
Export to BACPAC file and then Import from BACPAC file: If you have connectivity challenges (no connectivity, low bandwidth, or timeout issues) and for medium to large databases, use this manual method. With this method, you export the SQL Server schema and data to a BACPAC file and then import the BACPAC file into SQL Database using either the Deploy Database to Microsoft Azure SQL Database Wizard in SQL Server Management Studio or the SqlPackage command-prompt utility.
Use BACPAC and BCP together: Use a BACPAC file and BCP for very large databases to achieve greater parallelization for increased performance, albeit with greater complexity. With this method, migrate the schema and the data separately.
- Export the schema only to a BACPAC file.
- Import the schema only from the BACPAC file into SQL Database.
- Use BCP to extract the data into flat files and then parallel load these files into Azure SQL Database.
Source of Information : Migrating SQL Server Databases to Azure
Determining and resolving Azure SQL Database V12 compatibility issues
To export to a BACPAC file, the database must be compatible with Azure SQL Database V12. The export will fail if the database is not compatible. With SQL Database V12, there are very few remaining compatibility issues other than server-level and cross-database operations. Databases and applications that rely on partially supported or unsupported functions will need some reengineering to fix these incompatibilities before the SQL Server database can be migrated. Review these partially supported or unsupported functions before migrating the SQL Database.
The tooling to detect and fix these incompatibilities is improving, but it still requires some manual intervention in many cases. The list below describes the tooling as it currently exists, including new tooling coming out in preview mode in May 2016.
The Microsoft SQL Server 2016 Upgrade Advisor preview (UA): This stand-alone tool that currently is in preview will detect and generate a report of SQL Database V12 incompatibilities. This tool does not yet have all of the most recent compatibility rules (but will soon). If no errors are detected, you can continue and complete the migration to SQL Database. If errors are detected, you must use another tool to fix any detected compatibility issues. SSDT is the recommended tool.
As mentioned previously, UA will be updated in late April or early May 2016 and become Data Migration Assistant (DMA). DMA will contain the most recent compatibility rules and will, over time, enable you to fix incompatibilities directly in the tool. This functionality is still coming online, so stay tuned.
SQL Server Data Tools for Visual Studio (SSDT): For Azure SQL Database V12, the best tool today is SSDT because it uses the most recent compatibility rules to detect SQL Database V12 incompatibilities. To use this option, download the newest version of SSDT. If incompatibilities are detected, you can fix detected issues directly in SSDT.
SqlPackage: SqlPackage is a command-prompt utility that tests for and, if found, generates a report containing detected compatibility issues. If you use this tool, make sure you use the most recent version (follow the link provided) to use the most recent compatibility rules. If errors are detected, you must use another tool to fix any detected compatibility issues. SSDT is the recommended tool.
The Export Data-tier Application Wizard in SQL Server Management Studio: This wizard will detect and report errors to the screen. If no errors are detected, you can continue and complete the migration to SQL Database. If errors are detected, you must use another tool to fix any detected compatibility issues. SSDT is the recommended tool.
SQL Azure Migration Wizard (SAMW): For Azure SQL Database V11, the best tool was SAMW. This is a community-supported CodePlex tool that has not been updated fully for Azure SQL Database V12 and generally is not the best method to use to migrate to Azure SQL Database V12.
Source of Information : Migrating SQL Server Databases to Azure
The tooling to detect and fix these incompatibilities is improving, but it still requires some manual intervention in many cases. The list below describes the tooling as it currently exists, including new tooling coming out in preview mode in May 2016.
The Microsoft SQL Server 2016 Upgrade Advisor preview (UA): This stand-alone tool that currently is in preview will detect and generate a report of SQL Database V12 incompatibilities. This tool does not yet have all of the most recent compatibility rules (but will soon). If no errors are detected, you can continue and complete the migration to SQL Database. If errors are detected, you must use another tool to fix any detected compatibility issues. SSDT is the recommended tool.
As mentioned previously, UA will be updated in late April or early May 2016 and become Data Migration Assistant (DMA). DMA will contain the most recent compatibility rules and will, over time, enable you to fix incompatibilities directly in the tool. This functionality is still coming online, so stay tuned.
SQL Server Data Tools for Visual Studio (SSDT): For Azure SQL Database V12, the best tool today is SSDT because it uses the most recent compatibility rules to detect SQL Database V12 incompatibilities. To use this option, download the newest version of SSDT. If incompatibilities are detected, you can fix detected issues directly in SSDT.
SqlPackage: SqlPackage is a command-prompt utility that tests for and, if found, generates a report containing detected compatibility issues. If you use this tool, make sure you use the most recent version (follow the link provided) to use the most recent compatibility rules. If errors are detected, you must use another tool to fix any detected compatibility issues. SSDT is the recommended tool.
The Export Data-tier Application Wizard in SQL Server Management Studio: This wizard will detect and report errors to the screen. If no errors are detected, you can continue and complete the migration to SQL Database. If errors are detected, you must use another tool to fix any detected compatibility issues. SSDT is the recommended tool.
SQL Azure Migration Wizard (SAMW): For Azure SQL Database V11, the best tool was SAMW. This is a community-supported CodePlex tool that has not been updated fully for Azure SQL Database V12 and generally is not the best method to use to migrate to Azure SQL Database V12.
Source of Information : Migrating SQL Server Databases to Azure
Migrating using full database and transaction log backups to minimize downtime
To minimize downtime involved in a migration using a full database backup, you can use a combination of a full database backup, a differential backup (manual method only), and multiple transaction log backups. By using this method, you will keep downtime to a minimum. This method commonly is used with medium to large databases where minimizing downtime is of paramount importance. To test your database in Azure before migrating a production system, either use the full database backup method or use this method to test and verify the migration steps before the actual production migration.
Manual method: Back up, copy, and then restore (without recovery) a full database backup, a differential backup (optional), and multiple transaction logs while keeping your production system operational. Continue taking and applying transaction log backups without recovery until you are ready to switch over. Apply the final transaction log backup with recovery when you are ready to switch your clients/applications over to connect to the SQL Server instance in the Azure virtual machine rather than the on-premises database.
AlwaysOn Availability Group method: If you have an on-premises AlwaysOn Availability Group, you can extend the availability group to SQL Server in your virtual machine. Configure your SQL Server instance in the Azure virtual machine as an AlwaysOn replica, seed the replica with a full database backup, keep the replica current with transaction log backups that are applied automatically, and then fail over to the Azure replica when you are ready to switch over your clients to the SQL Server instance in the Azure virtual machine (and turn off AlwaysOn in the source database).
Source of Information : Migrating SQL Server Databases to Azure
Manual method: Back up, copy, and then restore (without recovery) a full database backup, a differential backup (optional), and multiple transaction logs while keeping your production system operational. Continue taking and applying transaction log backups without recovery until you are ready to switch over. Apply the final transaction log backup with recovery when you are ready to switch your clients/applications over to connect to the SQL Server instance in the Azure virtual machine rather than the on-premises database.
AlwaysOn Availability Group method: If you have an on-premises AlwaysOn Availability Group, you can extend the availability group to SQL Server in your virtual machine. Configure your SQL Server instance in the Azure virtual machine as an AlwaysOn replica, seed the replica with a full database backup, keep the replica current with transaction log backups that are applied automatically, and then fail over to the Azure replica when you are ready to switch over your clients to the SQL Server instance in the Azure virtual machine (and turn off AlwaysOn in the source database).
Source of Information : Migrating SQL Server Databases to Azure
Migrating using full database and transaction log backups to minimize downtime
To minimize downtime involved in a migration using a full database backup, you can use a combination of a full database backup, a differential backup (manual method only), and multiple transaction log backups. By using this method, you will keep downtime to a minimum. This method commonly is used with medium to large databases where minimizing downtime is of paramount importance. To test your database in Azure before migrating a production system, either use the full database backup method or use this method to test and verify the migration steps before the actual production migration.
Manual method: Back up, copy, and then restore (without recovery) a full database backup, a differential backup (optional), and multiple transaction logs while keeping your production system operational. Continue taking and applying transaction log backups without recovery until you are ready to switch over. Apply the final transaction log backup with recovery when you are ready to switch your clients/applications over to connect to the SQL Server instance in the Azure virtual machine rather than the on-premises database.
AlwaysOn Availability Group method: If you have an on-premises AlwaysOn Availability Group, you can extend the availability group to SQL Server in your virtual machine. Configure your SQL Server instance in the Azure virtual machine as an AlwaysOn replica, seed the replica with a full database backup, keep the replica current with transaction log backups that are applied automatically, and then fail over to the Azure replica when you are ready to switch over your clients to the SQL Server instance in the Azure virtual machine (and turn off AlwaysOn in the source database).
Source of Information : Migrating SQL Server Databases to Azure
Manual method: Back up, copy, and then restore (without recovery) a full database backup, a differential backup (optional), and multiple transaction logs while keeping your production system operational. Continue taking and applying transaction log backups without recovery until you are ready to switch over. Apply the final transaction log backup with recovery when you are ready to switch your clients/applications over to connect to the SQL Server instance in the Azure virtual machine rather than the on-premises database.
AlwaysOn Availability Group method: If you have an on-premises AlwaysOn Availability Group, you can extend the availability group to SQL Server in your virtual machine. Configure your SQL Server instance in the Azure virtual machine as an AlwaysOn replica, seed the replica with a full database backup, keep the replica current with transaction log backups that are applied automatically, and then fail over to the Azure replica when you are ready to switch over your clients to the SQL Server instance in the Azure virtual machine (and turn off AlwaysOn in the source database).
Source of Information : Migrating SQL Server Databases to Azure
Migrating using full database backup for simplicity
To use the full database backup method with SQL Server, select one of the following mechanisms and do the following:
Manually back up your source database and then restore it to your SQL Server instance in your virtual machine using SQL Server Management Studio. You can back it up and restore it using one of these methods:
Use the Deploy Database to a Microsoft Azure VM Wizard in SQL Server 2016 SQL Server Management Studio to back up your source database and then restore it to your SQL Server instance in your virtual machine. This method currently is not recommended because this wizard has not yet been updated to support Azure Resource Manager (ARM)–deployed virtual machines.
Use the Microsoft SQL Server 2016 Upgrade Advisor to back up your source database and then restore it to your SQL Server instance in your virtual machine. As mentioned previously, the SQL Server 2016 Upgrade Advisor currently is in preview, and a new version called Data Migration Assistant (DMA) will be released in late April or early May 2016. As this tool matures, it will become the preferred method. Until Data Migration Assistant is available, this method is not recommended.
You also could detach your data and log files, copy those files into your virtual machine, and then attach those files. However, this method has no advantage over the database backup and restore method.
Source of Information : Migrating SQL Server Databases to Azure
Manually back up your source database and then restore it to your SQL Server instance in your virtual machine using SQL Server Management Studio. You can back it up and restore it using one of these methods:
- Back up to a local file, copy backup to your virtual machine (or to blob storage), and then restore your backup file into your SQL Server instance in your virtual machine. We will walk through this method at the end of this chapter.
- Backup to URL and then restore from URL. This method is supported only for SQL Server 2012 SP1 CU2 or newer.
Use the Deploy Database to a Microsoft Azure VM Wizard in SQL Server 2016 SQL Server Management Studio to back up your source database and then restore it to your SQL Server instance in your virtual machine. This method currently is not recommended because this wizard has not yet been updated to support Azure Resource Manager (ARM)–deployed virtual machines.
Use the Microsoft SQL Server 2016 Upgrade Advisor to back up your source database and then restore it to your SQL Server instance in your virtual machine. As mentioned previously, the SQL Server 2016 Upgrade Advisor currently is in preview, and a new version called Data Migration Assistant (DMA) will be released in late April or early May 2016. As this tool matures, it will become the preferred method. Until Data Migration Assistant is available, this method is not recommended.
You also could detach your data and log files, copy those files into your virtual machine, and then attach those files. However, this method has no advantage over the database backup and restore method.
Source of Information : Migrating SQL Server Databases to Azure
Migrating a SQL Server user database to a SQL Server instance in an Azure virtual machine
When migrating a SQL Server user database to a SQL Server instance in an Azure virtual machine, the underlying mechanism generally uses the SQL Server backup functionality (using transactional replication also is an option, and this option is discussed in its own section later in this chapter). The options that use a variation of the SQL Server backup technology are discussed in this section.
Using database backups, you can:
Use a full database backup. For simplicity, you can perform the user database migration using a SQL Server full database backup. This method is most appropriate for small databases where the downtime is short; for scenarios in which you can afford the amount of downtime required to back up, copy, and restore the database to the SQL Server instance in the Azure virtual machine; and for scenarios in which you just want to test SQL Server in an Azure virtual machine prior to an actual migration.
Use a combination of full database and transaction log backups to minimize downtime on your production system.
Use AlwaysOn Availability Groups and create a replica of your existing on-premises user database on your Azure virtual machine (or replicas of a group of user databases that must be migrated together).
To migrate a SQL Server 2000 database to a supported configuration in an Azure virtual machine, you must first upgrade the database to SQL Server 2005 SP4 or greater. When migrating using these methods, encrypt your backups and use encrypted connections to ensure your data is secure during the migration process.
Source of Information : Migrating SQL Server Databases to Azure
Using database backups, you can:
Use a full database backup. For simplicity, you can perform the user database migration using a SQL Server full database backup. This method is most appropriate for small databases where the downtime is short; for scenarios in which you can afford the amount of downtime required to back up, copy, and restore the database to the SQL Server instance in the Azure virtual machine; and for scenarios in which you just want to test SQL Server in an Azure virtual machine prior to an actual migration.
Use a combination of full database and transaction log backups to minimize downtime on your production system.
Use AlwaysOn Availability Groups and create a replica of your existing on-premises user database on your Azure virtual machine (or replicas of a group of user databases that must be migrated together).
To migrate a SQL Server 2000 database to a supported configuration in an Azure virtual machine, you must first upgrade the database to SQL Server 2005 SP4 or greater. When migrating using these methods, encrypt your backups and use encrypted connections to ensure your data is secure during the migration process.
Source of Information : Migrating SQL Server Databases to Azure
Migrating and managing metadata stored outside a user database
When you migrate a user database to an Azure environment, metadata that is stored outside the user database generally is not migrated as part of the database migration. In a typical SQL Server environment, an application has dependencies on the master and msdb databases (and sometimes multiple user databases) and on metadata in the user database itself. Anything stored outside a user database that is required for the correct functioning of that database must be made available on the destination server instance, or the application must be re-architected to find that metadata in the user database. For example, the logins for an application traditionally are stored as metadata in the master database, and they must be either re-created on the destination server or re-created as contained users. For issues with orphaned logins, see Troubleshoot Orphaned Users.
Migrating to SQL Server in an Azure VM: If you are migrating a SQL Server database to SQL Server in an Azure virtual machine (Azure VM), you can either migrate all of the user databases using backup and restore or re-create the needed metadata. For more information, see Manage Metadata When Making a Database Available on Another Server Instance (SQL Server). Although not typically done, you could also migrate all of the system databases.
Migrating to SQL Database: If you are migrating a SQL Server database to SQL Database, you will need to re-architect how your database and application are designed to work within the constraints of SQL Database. For example, SQL Server Agent jobs are not supported in SQL Database. If an application or database maintenance plan depends on SQL Server Agent jobs, you must use other Azure functionality to achieve these tasks, such as elastic database jobs, Azure scheduler, or Azure automation. Although elastic database jobs are still in preview, they generally should be your first option to replace SQL Server Agent jobs, such as using contained users.
Source of Information : Migrating SQL Server Databases to Azure
Migrating to SQL Server in an Azure VM: If you are migrating a SQL Server database to SQL Server in an Azure virtual machine (Azure VM), you can either migrate all of the user databases using backup and restore or re-create the needed metadata. For more information, see Manage Metadata When Making a Database Available on Another Server Instance (SQL Server). Although not typically done, you could also migrate all of the system databases.
Migrating to SQL Database: If you are migrating a SQL Server database to SQL Database, you will need to re-architect how your database and application are designed to work within the constraints of SQL Database. For example, SQL Server Agent jobs are not supported in SQL Database. If an application or database maintenance plan depends on SQL Server Agent jobs, you must use other Azure functionality to achieve these tasks, such as elastic database jobs, Azure scheduler, or Azure automation. Although elastic database jobs are still in preview, they generally should be your first option to replace SQL Server Agent jobs, such as using contained users.
Source of Information : Migrating SQL Server Databases to Azure
Managing breaking changes between SQL Server versions
When you migrate a database to a newer version of SQL Server or to SQL Database, you may encounter breaking changes from the version of SQL Server on which the database was running. These changes might break applications, scripts, or functionalities that are based on earlier versions of SQL Server. There are two types of breaking changes: changes that will be detected by the migration tool and prevent the migration and changes that are not detected but affect functionality. With respect to changes that affect functionality, running a migrated database at an older compatibility level is a short-term fix, but you will need to run your database at the highest compatibility level possible to take advantage of the newest capabilities of SQL Server or SQL Database.
Before you begin a migration, you should review the Microsoft documentation with respect to any breaking changes if you are migrating from an older version of SQL Server to a newer version of SQL Server. See the following links for details regarding breaking changes:
SQL Server 2016 Breaking Changes
SQL Server 2014 Breaking Changes
SQL Server 2012 Breaking Changes
SQL Server 2008 R2 Breaking Changes
SQL Server 2008 Breaking Changes
SQL Server 2005 Breaking Changes
Microsoft provides an upgrade advisor to help you detect and prepare for a version upgrade. These advisors will examine scripts, stored procedures, triggers, and trace files, but they will not analyze desktop applications or encrypted stored procedures. See the following links for information about how to install and use the upgrade advisor for the version of SQL Server to which you are upgrading and for information about where to find or install the upgrade advisor.
SQL Server 2016
SQL Server 2014
SQL Server 2012
SQL Server 2008 R2
SQL Server 2008
SQL Server 2005
With respect to issues you need to fix due to breaking changes, when you fix them depends on the source and the destination.
If you are migrating from SQL Server on-premises to SQL Server in a virtual machine, you can fix them in the source before the migration—in the source database (perhaps affecting the production system), in an on-premises copy as part of the migration, or after the database is restored. A SQL Server database backup can be restored to a newer version of SQL Server, even with issues that must be changed before the database will function properly (discontinued syntax, for example).
If you are migrating from a non–SQL Server source database or are migrating to SQL Database, you must fix the issues detected before the migration because the migration mechanisms do not permit you to fix them after the migration.
To fix issues detected, you can use either SQL Server Management Studio or SQL Server Data Tools for Visual Studio (SSDT). With SSDT, you create a database project from the database schema and then modify the schema within the project without affecting the source database. You then can merge the changes back into the source database or to a database copy and then synchronize the data.
The SQL Server 2016 Upgrade Advisor is currently in preview. It is a new and substantially enhanced version, with more features to come soon. In late April or early May 2016, a new version of the SQL Server 2016 Upgrade Advisor will be released and will be named the Data Migration Assistant (DMA), also as a preview. Among the enhancements is the ability not only to detect upgrade issues, but also to fix some issues and to perform the upgrade and migration for you using database backup functionality.
Source of Information : Migrating SQL Server Databases to Azure
Before you begin a migration, you should review the Microsoft documentation with respect to any breaking changes if you are migrating from an older version of SQL Server to a newer version of SQL Server. See the following links for details regarding breaking changes:
SQL Server 2016 Breaking Changes
SQL Server 2014 Breaking Changes
SQL Server 2012 Breaking Changes
SQL Server 2008 R2 Breaking Changes
SQL Server 2008 Breaking Changes
SQL Server 2005 Breaking Changes
Microsoft provides an upgrade advisor to help you detect and prepare for a version upgrade. These advisors will examine scripts, stored procedures, triggers, and trace files, but they will not analyze desktop applications or encrypted stored procedures. See the following links for information about how to install and use the upgrade advisor for the version of SQL Server to which you are upgrading and for information about where to find or install the upgrade advisor.
SQL Server 2016
SQL Server 2014
SQL Server 2012
SQL Server 2008 R2
SQL Server 2008
SQL Server 2005
With respect to issues you need to fix due to breaking changes, when you fix them depends on the source and the destination.
If you are migrating from SQL Server on-premises to SQL Server in a virtual machine, you can fix them in the source before the migration—in the source database (perhaps affecting the production system), in an on-premises copy as part of the migration, or after the database is restored. A SQL Server database backup can be restored to a newer version of SQL Server, even with issues that must be changed before the database will function properly (discontinued syntax, for example).
If you are migrating from a non–SQL Server source database or are migrating to SQL Database, you must fix the issues detected before the migration because the migration mechanisms do not permit you to fix them after the migration.
To fix issues detected, you can use either SQL Server Management Studio or SQL Server Data Tools for Visual Studio (SSDT). With SSDT, you create a database project from the database schema and then modify the schema within the project without affecting the source database. You then can merge the changes back into the source database or to a database copy and then synchronize the data.
The SQL Server 2016 Upgrade Advisor is currently in preview. It is a new and substantially enhanced version, with more features to come soon. In late April or early May 2016, a new version of the SQL Server 2016 Upgrade Advisor will be released and will be named the Data Migration Assistant (DMA), also as a preview. Among the enhancements is the ability not only to detect upgrade issues, but also to fix some issues and to perform the upgrade and migration for you using database backup functionality.
Source of Information : Migrating SQL Server Databases to Azure
Migrating a user database to Azure
SQL Server provides a variety of mechanisms to help you migrate some or all of the user objects and data from both Microsoft SQL Server and non-Microsoft SQL Server databases to an Azure environment:
Migrating from a SQL Server source database: You can migrate from a SQL Server 2005–2016 database to an Azure environment, but you may have compatibility issues to resolve either before the migration can be completed or after the migration but before the database is ready to use.
Migrating from a recent version of SQL Server to a supported version of SQL Server in an Azure virtual machine will require few if any changes at the user database level.
Migrating from an older version of SQL Server database to a newer version of SQL Server may have breaking changes that you will have to resolve before, during, or after the migration.
When you are migrating from a SQL Server database to Azure SQL Database, you may have objects that are not compatible that you will need to fix prior to the migration.
Microsoft provides a number of mechanisms to minimize downtime during the migration, depending upon both your source environment and your destination.
Migrating from a non–SQL Server source database: You can migrate from the following non–SQL Server source databases into an Azure environment:
When you are migrating from a non–SQL Server database, SQL Server provides mechanisms to convert a substantial percentage of the database objects automatically and will assist you in identifying the objects that cannot be converted, providing you guidance in how to convert those remaining objects. These changes will have to be made either before or during the migration. In most cases, you cannot complete the migration until you have established compatibility with the destination environment.
Regardless of the migration method, you will have to manage metadata that is stored outside the user databases, plan to minimize user downtime, and determine how to redirect users from the old database to the new database.
After the migration is complete, you will want to set the appropriate database compatibility level for the best performance and to take advantage of the newest database capabilities and provide partial backward compatibility with earlier versions of SQL Server until you have converted applications that use deprecated and discontinued functionality. By default, a migrated database retains its existing compatibility if it is at least equal to the minimum supported by the instance to which the database is being migrated.
Finally, how you perform the migration will be affected by your tolerance for downtime in your existing production environment. You will need to plan your migration to handle the amount of downtime required. For example, you may perform an initial migration of a copy of your database, perform the required testing and development work on the copy, and later migrate the production system by using one of the mechanisms discussed later in this chapter to minimize downtime. Or, you may discover that your database requires minimal or no changes and can be migrated during a scheduled maintenance window by using one of the simple migration methods.
Source of Information : Migrating SQL Server Databases to Azure
Migrating from a SQL Server source database: You can migrate from a SQL Server 2005–2016 database to an Azure environment, but you may have compatibility issues to resolve either before the migration can be completed or after the migration but before the database is ready to use.
Migrating from a recent version of SQL Server to a supported version of SQL Server in an Azure virtual machine will require few if any changes at the user database level.
Migrating from an older version of SQL Server database to a newer version of SQL Server may have breaking changes that you will have to resolve before, during, or after the migration.
When you are migrating from a SQL Server database to Azure SQL Database, you may have objects that are not compatible that you will need to fix prior to the migration.
Microsoft provides a number of mechanisms to minimize downtime during the migration, depending upon both your source environment and your destination.
Migrating from a non–SQL Server source database: You can migrate from the following non–SQL Server source databases into an Azure environment:
- Access
- DB2
- MySQL
- Oracle
- Sybase
When you are migrating from a non–SQL Server database, SQL Server provides mechanisms to convert a substantial percentage of the database objects automatically and will assist you in identifying the objects that cannot be converted, providing you guidance in how to convert those remaining objects. These changes will have to be made either before or during the migration. In most cases, you cannot complete the migration until you have established compatibility with the destination environment.
Regardless of the migration method, you will have to manage metadata that is stored outside the user databases, plan to minimize user downtime, and determine how to redirect users from the old database to the new database.
After the migration is complete, you will want to set the appropriate database compatibility level for the best performance and to take advantage of the newest database capabilities and provide partial backward compatibility with earlier versions of SQL Server until you have converted applications that use deprecated and discontinued functionality. By default, a migrated database retains its existing compatibility if it is at least equal to the minimum supported by the instance to which the database is being migrated.
Finally, how you perform the migration will be affected by your tolerance for downtime in your existing production environment. You will need to plan your migration to handle the amount of downtime required. For example, you may perform an initial migration of a copy of your database, perform the required testing and development work on the copy, and later migrate the production system by using one of the mechanisms discussed later in this chapter to minimize downtime. Or, you may discover that your database requires minimal or no changes and can be migrated during a scheduled maintenance window by using one of the simple migration methods.
Source of Information : Migrating SQL Server Databases to Azure
Service tiers and capabilities
SQL Database service tiers also differ based on capabilities related to business continuity and in-memory features.
Business continuity services
All service tiers provide mechanisms to enable a business to continue operating in the face of disruption to its computing infrastructure. The types of potential disruption scenarios from which these mechanisms protect you are:
Local hardware and software failures, such as disk failure
Data corruption and deletion, such as application bugs and user errors
Data center outage, such as a natural disaster
Upgrade or maintenance errors, such as unanticipated issues during an application upgrade requiring a rollback to a previous state
The next two sections discuss these business continuity mechanisms and how they differ across service tiers.
Automatic database backup and self-service geo-restore services
To protect your data and enable point-in-time restore services, SQL Database automatically takes full database backups every week, multiple differential backups every day, and log backups every five minutes. Backup files are stored in a geo-redundant storage account with read access (RA-GRS) to ensure backups’ availability for disaster recovery purposes in case of catastrophic disaster at any single data center. The first full backup is scheduled immediately after a database is created. After the first full backup, all further backups are scheduled automatically and managed silently in the background. The exact timing of full and differential backups is determined by the system to balance overall load.
SQL Database provides this service for all databases regardless of server tier, but they have different retention periods for the database backups as follows:
Basic: Any restore point within the past 7 days
Standard: Any restore point within the past 14 days
Premium: Any restore point within the past 35 days
SQL Database provides both database copy and point-in-time restore services on top of this automated backup system, which enable you to restore an existing or deleted database to a new database as of a specified point in time, including the current point in time, within the retention period supported by the service tier. This capability is offered within the current data center.
Storage geo-replication provides the ability to restore a database to another data center from the last geo-redundant backup to create a new database in any geographic region. This capability is called geo-restore. The geo-replication of the backup blobs from the automated backups guarantees that daily backups are available even after an outage or massive failure in the primary region.
Active geo-replication
In addition to automated backups and storage geo-replication, SQL Database offers active geo-replication for all databases regardless of service tier. Active geo-replication provides a mechanism to create and maintain asynchronous secondary replicas in up to four regions. These secondary replicas can be either offline or online and readable, and they can be in the same or different data centers.
readable secondary is priced at 1X price of the selected performance level. A non-readable secondary is charged at a discounted rate of 3/4X price of the selected performance level. To ensure that transactions being applied to the secondary do not bottleneck the primary, the secondary must be at the same or higher performance level than the primary. Non-readable secondaries are available for failover if needed due to any of the disaster scenarios discussed above. Readable online secondary replicas can be used for load balancing and/or low-latency read access to different geographic regions. When performing an application upgrade or maintenance operation, the continuous replication can be frozen immediately prior to the upgrade or maintenance operation so that you can easily fall back. In case of a data center outage, you can manually fail over to one of the secondary replicas. You also can design and deploy a small worker role application that monitors your primary database and triggers a failover if necessary. For a discussion of active replication topologies using a combination of readable and non-readable secondaries for both disaster recovery scenarios and application load-balancing scenarios.
In-memory services
In-memory services are available only in the Premium service tier, with different size limits for memory storage based on the performance level within the Premium tier. In-memory services encompass two features: in-memory OLTP and Operational Analytics.
In-memory OLTP is a feature that can greatly improve OLTP performance by storing selected OLTP tables in memory (5X–20X improvement) and by using compiled stored procedures (100X improvement). In-memory OLTP has been available in SQL Server beginning with SQL Server 2014 and has been enhanced significantly in SQL Server 2016. These enhancements also are available in SQL Database.
Operational Analytics provides the ability to run both analytics and OLTP workloads in the same database at the same time without a significant degradation in performance. Besides running analytics in real time, you can eliminate the need for loading data into a data warehouse. Operational Analytics is a feature introduced in SQL Server 2016, with the same feature available in SQL Database.
A two-minute video about the benefits of using the in-memory OLTP feature is available at Azure SQL Database - In-Memory Technologies.
Source of Information : Migrating SQL Server Databases to Azure
Business continuity services
All service tiers provide mechanisms to enable a business to continue operating in the face of disruption to its computing infrastructure. The types of potential disruption scenarios from which these mechanisms protect you are:
Local hardware and software failures, such as disk failure
Data corruption and deletion, such as application bugs and user errors
Data center outage, such as a natural disaster
Upgrade or maintenance errors, such as unanticipated issues during an application upgrade requiring a rollback to a previous state
The next two sections discuss these business continuity mechanisms and how they differ across service tiers.
Automatic database backup and self-service geo-restore services
To protect your data and enable point-in-time restore services, SQL Database automatically takes full database backups every week, multiple differential backups every day, and log backups every five minutes. Backup files are stored in a geo-redundant storage account with read access (RA-GRS) to ensure backups’ availability for disaster recovery purposes in case of catastrophic disaster at any single data center. The first full backup is scheduled immediately after a database is created. After the first full backup, all further backups are scheduled automatically and managed silently in the background. The exact timing of full and differential backups is determined by the system to balance overall load.
SQL Database provides this service for all databases regardless of server tier, but they have different retention periods for the database backups as follows:
Basic: Any restore point within the past 7 days
Standard: Any restore point within the past 14 days
Premium: Any restore point within the past 35 days
SQL Database provides both database copy and point-in-time restore services on top of this automated backup system, which enable you to restore an existing or deleted database to a new database as of a specified point in time, including the current point in time, within the retention period supported by the service tier. This capability is offered within the current data center.
Storage geo-replication provides the ability to restore a database to another data center from the last geo-redundant backup to create a new database in any geographic region. This capability is called geo-restore. The geo-replication of the backup blobs from the automated backups guarantees that daily backups are available even after an outage or massive failure in the primary region.
Active geo-replication
In addition to automated backups and storage geo-replication, SQL Database offers active geo-replication for all databases regardless of service tier. Active geo-replication provides a mechanism to create and maintain asynchronous secondary replicas in up to four regions. These secondary replicas can be either offline or online and readable, and they can be in the same or different data centers.
readable secondary is priced at 1X price of the selected performance level. A non-readable secondary is charged at a discounted rate of 3/4X price of the selected performance level. To ensure that transactions being applied to the secondary do not bottleneck the primary, the secondary must be at the same or higher performance level than the primary. Non-readable secondaries are available for failover if needed due to any of the disaster scenarios discussed above. Readable online secondary replicas can be used for load balancing and/or low-latency read access to different geographic regions. When performing an application upgrade or maintenance operation, the continuous replication can be frozen immediately prior to the upgrade or maintenance operation so that you can easily fall back. In case of a data center outage, you can manually fail over to one of the secondary replicas. You also can design and deploy a small worker role application that monitors your primary database and triggers a failover if necessary. For a discussion of active replication topologies using a combination of readable and non-readable secondaries for both disaster recovery scenarios and application load-balancing scenarios.
In-memory services
In-memory services are available only in the Premium service tier, with different size limits for memory storage based on the performance level within the Premium tier. In-memory services encompass two features: in-memory OLTP and Operational Analytics.
In-memory OLTP is a feature that can greatly improve OLTP performance by storing selected OLTP tables in memory (5X–20X improvement) and by using compiled stored procedures (100X improvement). In-memory OLTP has been available in SQL Server beginning with SQL Server 2014 and has been enhanced significantly in SQL Server 2016. These enhancements also are available in SQL Database.
Operational Analytics provides the ability to run both analytics and OLTP workloads in the same database at the same time without a significant degradation in performance. Besides running analytics in real time, you can eliminate the need for loading data into a data warehouse. Operational Analytics is a feature introduced in SQL Server 2016, with the same feature available in SQL Database.
A two-minute video about the benefits of using the in-memory OLTP feature is available at Azure SQL Database - In-Memory Technologies.
Source of Information : Migrating SQL Server Databases to Azure
Service tiers and performance levels
SQL Database service tiers differ based on performance levels. Within each service tier, performance levels are designed to have a database operate as if it is running in its own machine, isolated from other databases and services. SQL Database accomplishes this by using a resource governor for certain resources and hard resource limits for other resources.
Each database can have its own service tier with its own limits, independent of any other database. This option is best for databases with a consistent load. You also can place multiple databases in an elastic pool where resources can be shared among databases and minimum and maximum resource thresholds can be set for each database in the pool.
The resource governor enforces a maximum amount of resources for either a single database or an elastic pool of databases. If the aggregated resource utilization reaches the maximum available CPU, memory, log I/O, and data I/O resources assigned to the database, the resource governor will queue queries in execution and assign resources to the queued queries as resources become available.
To assist you in choosing the amount of resources you need for your database, Microsoft developed a unit of measure called a Database Transaction Unit (DTU). To develop this benchmark, Microsoft took a set of operations that are typical for an online transaction processing (OLTP) request and then measured how many transactions could be completed per second under fully loaded conditions within each performance level.
If you are migrating an existing SQL Server database, you can use a third-party tool, the Azure SQL Database DTU Calculator, to get an estimate of the performance level and service tier your database might require in SQL Database.
DTU resource limits
Single database: A single database can have between 5 and 1,750 DTUs. A Basic database has 5 DTUs, which means it can complete 5 “typical” transactions per second, while a Premium P11 database supports 1,750 DTUs.
Elastic pool database: A database can be a member of an elastic pool with between 100 and 1,500 DTUs. All databases within a pool share a common set of resources.
Other resource limits
SQL Database enforces limits on other resources by denying all new requests when limits are reached. These limits include the following:
Maximum database size: Between 5 GB and 1 terabyte (TB)
Max in-memory OLTP storage: Between not available and 10 GB
Max concurrent requests: Between 30 and 2,400
Max concurrent logins: Between 30 and 2,400
Max sessions: Between 300 and 32,000
Max databases using automated export: 10
Source of Information : Migrating SQL Server Databases to Azure
Each database can have its own service tier with its own limits, independent of any other database. This option is best for databases with a consistent load. You also can place multiple databases in an elastic pool where resources can be shared among databases and minimum and maximum resource thresholds can be set for each database in the pool.
The resource governor enforces a maximum amount of resources for either a single database or an elastic pool of databases. If the aggregated resource utilization reaches the maximum available CPU, memory, log I/O, and data I/O resources assigned to the database, the resource governor will queue queries in execution and assign resources to the queued queries as resources become available.
To assist you in choosing the amount of resources you need for your database, Microsoft developed a unit of measure called a Database Transaction Unit (DTU). To develop this benchmark, Microsoft took a set of operations that are typical for an online transaction processing (OLTP) request and then measured how many transactions could be completed per second under fully loaded conditions within each performance level.
If you are migrating an existing SQL Server database, you can use a third-party tool, the Azure SQL Database DTU Calculator, to get an estimate of the performance level and service tier your database might require in SQL Database.
DTU resource limits
Single database: A single database can have between 5 and 1,750 DTUs. A Basic database has 5 DTUs, which means it can complete 5 “typical” transactions per second, while a Premium P11 database supports 1,750 DTUs.
Elastic pool database: A database can be a member of an elastic pool with between 100 and 1,500 DTUs. All databases within a pool share a common set of resources.
Other resource limits
SQL Database enforces limits on other resources by denying all new requests when limits are reached. These limits include the following:
Maximum database size: Between 5 GB and 1 terabyte (TB)
Max in-memory OLTP storage: Between not available and 10 GB
Max concurrent requests: Between 30 and 2,400
Max concurrent logins: Between 30 and 2,400
Max sessions: Between 300 and 32,000
Max databases using automated export: 10
Source of Information : Migrating SQL Server Databases to Azure
Azure SQL Database
SQL Database is an Azure V12 SQL database running within the SQL Server platform service and associated with a logical server. An Azure V12 SQL database is similar in most respects to a SQL Server 2016 database, but there are a few differences. The SQL Server platform service provides the functionality to create and manage your database solution, freeing you up to focus on the solution rather than the underlying network, storage, and compute resources in addition to the SQL Server instance.
When creating your SQL database, you specify the amount of resources available to your new SQL database and associate it with either a new or an existing SQL Database logical server. The features and resource limits available to your new SQL database are determined by the service tier and performance level you select for the SQL database. The SQL Database service manages the resources available to each SQL database using a resource governance mechanism and hard resource limits (both of which are discussed below). A SQL database is portable and can be moved between logical servers.
To secure your SQL Database logical server and database instances, a SQL Database firewall limits connectivity to either Azure services or specific IP addresses. The SQL Database firewall operates at both the logical server and the database level.
Source of Information : Migrating SQL Server Databases to Azure
When creating your SQL database, you specify the amount of resources available to your new SQL database and associate it with either a new or an existing SQL Database logical server. The features and resource limits available to your new SQL database are determined by the service tier and performance level you select for the SQL database. The SQL Database service manages the resources available to each SQL database using a resource governance mechanism and hard resource limits (both of which are discussed below). A SQL database is portable and can be moved between logical servers.
To secure your SQL Database logical server and database instances, a SQL Database firewall limits connectivity to either Azure services or specific IP addresses. The SQL Database firewall operates at both the logical server and the database level.
Source of Information : Migrating SQL Server Databases to Azure
Subscribe to:
Posts (Atom)
Cloud storage is for blocks too, not just files
One of the misconceptions about cloud storage is that it is only useful for storing files. This assumption comes from the popularity of file...
-
Smss is like any other user-mode process except for two differences: First, Windows considers Smss a trusted part of the operating system. S...
-
When Winload calls Ntoskrnl, it passes a data structure called the loader parameter block that contains the system and boot partition paths,...
-
Many of the virus, adware, security, and crash problems with Windows occu when someone installs a driver of dubious origin. The driver suppo...