![\"\"](\"https:\/\/www.dbi-services.com\/blog\/wp-content\/uploads\/sites\/2\/2022\/04\/2e1ax_default_entry_sqlserver2014_20130625-144612_1.jpg\")
<\/p>\n<\/p>\n
Deploying an SQL Server database to a Windows Azure virtual machine is a feature introduced with SQL Server 2014. It can be useful for an organization\u00a0 that wants to reduce its infrastucture management, simplify the deployment, or have a fast virtual machine generation.<\/p>\n
This new feature is a wizard which allows either to copy or migrate an On-Premise SQL Server database to a Windows Azure virtual machine. Obviously, the first requirement is an Azure account! To create an Azure account, go to the official Azure website<\/a>.<\/p>\n Once the Azure account has been set, a Windows Azure virtual machine has to be created.<\/p>\n There are two ways to create a Virtual Machine: by a “Quick Create” or by a “Custom Create”. It is recommended to perform a “Custom Create” (from gallery) because it offers more flexibility and control on the creation.<\/p>\n This example is done with \u201cFROM GALLERY\u201d. So the creation of the virtual machine will be performed with a wizard in four steps. The first step of the wizard is configured as follows:<\/p>\n For this example, default settings are used. Indeed the configuration of the virtual machine is not the main topic here, and can change depending on one\u2019s need.<\/p>\n The release date from June is selected, so the SP1 will be included for SQL Server 2014.<\/p>\n The “Standard Tier” is selected so load-balancer and auto-scaling will be included. See more details about Basic and Standard Tier<\/a> on the Microsoft Azure website.<\/p>\n The virtual machine will run with 2 cores and 3.5 GB memory, which will be enough for this demo. See more details about prices and sizes<\/a> on the Microsoft Azure website.<\/p>\n The virtual machine is named \u201cdbiservicesvm\u201d and the first (admin) user is \u201cdbiadmin\u201d. The creation of a virtual machine in Azure requires a cloud service, which is a container for virtual machines in Azure. See more details about Cloud Service<\/a> on the Microsoft Azure website.<\/p>\n Furthermore, a storage account (“dbiservices”) and an affinity group (“IT”) are also required to store the disk files of the virtual machine. To create a storage account and an affinity group, see the Azure Storage Account Creation <\/a>part from a previous blog.<\/p>\n The third step of the wizard is configured as follows:<\/p>\n This screen offers the possibility to install extensions for the virtual machine. Virtual machine extensions simplify the virtual machine management. By default, VM Agent is installed on the virtual machine.<\/p>\n The Azure VM Agent will be responsible for installing, configuring and managing VM extensions on the virtual machine.<\/p>\n For this example, VM extensions are not required at all, so nothing is selected.<\/p>\n The wizard can be found with a right-click on the targeted database, then “Tasks” and finally “Deploy Database to a Windows Azure VM\u2026”<\/p>\n In fact, it does not matter from which user databases the wizard is launched, because the wizard will ask to connect to an instance, and then it will ask to select a database.<\/p>\n The first step is an introduction of the wizard, which is quite without interests.<\/p>\n The second step needs three fields: the SQL Server instance (source instance), the SQL Server database\u00a0 (source database) and the folder to store the backup files.<\/p>\n The “SQL Server” field is the SQL Server instance hosting the SQL Server database which is planned to be deployed to Azure. After clicking on the Sign in\u2026 button, a pop-up will require the Administrator privilege from the Azure account. If several subscriptions are linked to the Azure account, select the correct subscription ID. In this example, there is only one subscription linked to the Azure account.<\/p>\n For more information, see what\u2019s the difference between an Azure account and a subscription<\/a> on TechNet. The cloud service (“dbiservicescloud”) needs to be selected. Then, the virtual machine (“dbiservicesvm”) can be selected. The SQL Server instance name and the database name need to be filled, as follows:<\/p>\n Finally, all information are filled for the wizard, the deployment can be launched. The deployment finished successfully!<\/p>\n First, the On-Premise database used for the deployment is still present on the SQL Server instance.<\/p>\n Then, the folder used to store the temporary file is still present. In fact, this is a “bak” file because the mechanism behind the wizard is a simple backup and restore of the database from an On-Premise SQL Server instance to an Azure SQL Server instance.<\/p>\n So do not forget to delete the bak file after the deployment because this file uncessarly fills your storage space for big databases. At the SQL Server level, an On-Premise SQL user with Backup Operator privilege is required on the targeted Azure database. Of course the user must be mapped with a SQL Server login to be able to connect to the SQL Server instance.<\/p>\n If the user does not have the Backup Operator privilege, the process will be blocked at the database backup creation step:<\/p>\n Moreover, an Azure account with Administrator Service privilege linked to the subscription containing the Windows Azure virtual machine is also required. Without this account, it is impossible to retrieve the list of Windows Azure virtual machines available on the Azure account, so the SQL Server Windows Azure virtual machine created previously.<\/p>\n Finally, the end point of the Cloud Adapter must be configured to access the virtual machine during the execution of the wizard. If not, the following error message will occur:<\/p>\n The Cloud Adapter is a service which allows the On-Premise SQL Server instance to communicate with the SQL Server Windows Azure virtual machine. More details about Cloud Adapter for SQL Server<\/a> on TechNet.<\/p>\n To configure the Cloud Adapter port, the Azure administrator needs to access to the Endpoints page in the SQL Server Windows Azure virtual machine on the Azure account. Then a new endpoint needs to be created as follows:<\/p>\n Now, the SQL Server Windows Azure virtual machine allows to connect to the Cloud Adapter port.<\/p>\n The credentials of a Windows administrator user are also required on the Windows Azure virtual machine to connect to the virtual machine in Azure. This administrator also needs a SQL Server login. If these two requirements are not met, the following error message will occur:<\/p>\n The login created in the SQL Server instance in Azure must also have the dbcreator server role, otherwise the following error message will occur:<\/p>\n The first limitation of this new feature is the database size of this operation: it cannot exceed 1 TB. The new feature does not allow to deploy all versions of SQL Server database. Indeed, only SQL Server 2008 database versions or higher are allowed to be deployed.<\/p>\n If this new feature, for any reason , does not meet the organization\u2019s need, two similar features exist.<\/p>\n This feature introduced with SQL Server 2012 is quite similar to the feature presented in this blog, because the SQL Server database and the Windows Azure virtual machine are hosted on Azure.<\/p>\n However, the management of the infrastructure is much more reduced: no virtual machine management nor sql server management. A good comparison between these two functionalities is available: Choosing between SQL Server in Windows Azure VM & Windows Azure SQL Database<\/a>. This is a new feature introduced with SQL Server 2014, which allows to store data files from the SQL Server database in Azure Blob storage, but the SQL Server instance runs On-Premise.<\/p>\n It simplifies the migration process, reduces the On-Premise space storage and management, and simplifies the High Availability and recovery solutions\u2026<\/p>\n
\nThe following schema explains the main process of the feature:
\n![\"PrtScr-capture_20.png\"](\"https:\/\/www.dbi-services.com\/blog\/wp-content\/uploads\/sites\/2\/2022\/04\/PrtScr-capture_20.png\" "\\"PrtScr-capture_20.png\\"")
<\/a>An existing SQL Server instance is present on an On-Premise machine of an organization, hosting one or several user databases.
\nOnce the new feature is used, a copy of the on-premise database will be available on the SQL Server instance in the Cloud.<\/p>\nPrerequisites<\/h3>\n
Azure Account Creation<\/h4>\n
Azure Virtual Machine Creation<\/h4>\n
<\/a><\/p>\n
\nThe first step of the wizard is the selection of the virtual machine type.
\nUnlike prejudices, Windows Azure offers a large panel of virtual machines, which do not only come from the Microsoft world.
\nSee more details about Virtual Machines <\/a>on the Microsoft Azure website.<\/p>\n<\/a>
\nThe targeted feature is only available on SQL Server 2014, so a virtual machine including SQL Server 2014 has to be created. This example will be made with a Standard edition to have the more restrictive edition of SQL Server 2014.<\/p>\n<\/a><\/p>\n
\nThe second step of the wizard is configured as follows:<\/p>\n<\/a><\/p>\n<\/a><\/p>\nDeploy a database to the Windows Azure VM wizard<\/h3>\n
Launch the wizard<\/h4>\n
<\/a><\/p>\nUse the wizard<\/h4>\n
<\/a><\/p>\n
\nThe “Select Database” field must obviously reference the database to deploy.
\nThe third step needs two fields: the authentication to the Azure account and the selection of the subscription.<\/p>\n<\/a><\/p>\n
\nAs soon as the credentials are entered to connect to the Azure account, a certificate will be generated.<\/p>\n
\nThe fourth step of the wizard is divided in several parts. First, there is the virtual machine selection, as follows:<\/p>\n<\/a><\/p>\n
\nCredentials to connect to the virtual machine must be provided, as follows:<\/p>\n<\/a><\/p>\n<\/a><\/p>\nAfter the deployment<\/h4>\n
<\/a><\/p>\n<\/a><\/p>\n
\nFinally, the deployed database can be found on the Azure SQL Server instance!<\/p>\n<\/a><\/p>\nStandard Errors<\/h4>\n
<\/a><\/p>\n<\/a><\/p>\n<\/a><\/p>\n<\/a><\/p>\n<\/a><\/p>\nLimitations<\/h4>\n
\nMoreover, it does not support hosted services that are associated with an Affinity Group.<\/p>\nSimilar features<\/h3>\n
SQL database in Azure<\/h4>\n
\nMore details about SQL Database<\/a> on Azure.<\/p>\nSQL Server data files in Azure<\/h4>\n