Using Azure SQL Database with Commerce Server

Commerce Server can use an on-premise version of SQL Server or Azure SQL Database for the back-end datastore.

The experience for deploying to Azure SQL Database is similar to working with an on-premise version of SQL Server. However, there are some things to consider when working with Azure SQL Database:

Only v.12 of Azure SQL Database is supported.
The MSCS_CatalogScratch database no longer exists. The tables are now created in the appropriate *_productcatalog database.
There is no method to back up and restore existing on-premise Commerce Server databases and restore them on Azure SQL Database. All of the Commerce Server databases that are on Azure SQL Database must be created on Azure SQL Database. Once the Commerce Server databases are created, you can use Commerce Server Staging, CmdLets, or other tools to export and import data for each subsystem.

Prerequisites:

An appropriate license for Azure SQL Database.
An appropriate license for Commerce Server installer.

Use the following steps to set up the Commerce Server admin and site databases in Azure SQL Database:

Log in to the Azure Management Portal, and create or identify a v.12 Azure SQL Database instance to store your Commerce Server databases. Take note of the full Azure SQL Database server name, which is typically in the format SERVERNAME.database.windows.net .
Make sure that your current external IP address is added to the allowed IP address list on the Azure SQL Database server.
Install Commerce Server on a on-premise Azure Windows server.
Run the Commerce Server Configuration Wizard:
- Provide the full Azure SQL Database server name, such as SERVERNAME.database.windows.net .
- Use the default admin database name. For example, MSCS_Admin, or update to a name of your choosing.
- Change the authentication type to SQL Server Authentication, and add the Azure SQL Database account that can create databases on that server. Azure SQL Database does not support Windows Authentication, so you must use SQL Server Authentication.
- Follow the rest of the steps in the wizard and finish the set up.
There are two options to create your Commerce Server Site and Subsystem resource databases against Azure SQL Database:
1. PowerShell CmdLets
  - You can use all of the standard Commerce Server CmdLets for setting up your site and resources. By default, these CmdLets will try to create their databases on the same SQL instance as the admin database with 250GB Max Size, the S0 Pricing Tier, and Standard edition. All of these settings can be changed at a later time by using the Azure Management Portal.
  - Initialize-CSSite, Add-CSCatalogResource, Add-CSInventoryResource, Add-CSOrdersResource, Add-CSMarketingResource, Add-CSProfilesResource, and Add-CSResources are the only CmdLets that create resource databases, and the only CmdLets that allow you to target and set the size of a different database on Azure SQL Database during creation. Each of these CmdLets accepts the following Azure SQL specific parameters that allow you to control your database configuration during creation:
    -Name "MySite" -SqlUserName "adminuser" -SqlUserPassword "password" -Edition "standard" -MaxSize "2 GB" -ServiceObjective "S3" -ConnectionString "Server=tcp:server.database.windows.net,1433;Database=MySite_productcatalog;UserID=cataloguser@server;Password=password;Trusted_Connection=False;Encrypt=True;Connection Timeout=30;"
2. PuP package
  You can use existing PuP packages, but you cannot set Azure SQL database sizes or editions from the Site Packager UI. If you are automating an installation, you can specify size, edition, and service objectives in the ConnStrs section of your ini file using a AzureCreateDatabaseOptions* property. For an example, see the following. The various options for these parameters can be found at https://msdn.microsoft.com/en-us/library/dn268335.aspx .
  [ConnStrs]
  - ConnStrResourceName0=Biz Data Service
  - ConnStrPropertyName0=connstr_db_bds
  - ConnStrFriendlyResourceName0=Profiles
  - ConnStrFriendlyPropertyName0=connstr_db_bds
  - ConnStrValue0=*****
  AzureCreateDatabaseOptions0=MAXSIZE = 1 GB, EDITION = 'standard', SERVICE_OBJECTIVE = 'S3'

If you already have Commerce Server installed and configured on your server, you will not be able to run the Commerce Server Configuration Wizard. In order to run the Configuration Wizard you will need to run the following from the command line:

C:\Program Files (x86)\Commerce Server 11\csconfig.exe /u

Once this has finished, you will be able to run the Configuration Wizard and configure a Commerce Server instance on Azure SQL Database. You can change your server at any time to point to a different Commerce Server Admin database by clicking on the Administration Database link in Commerce Server Manager.

No matter what method you are using to set up your Commerce Server site and resources, you have the option to pre-create the required databases for each resource ahead of time in Azure SQL Database, as long as you select the default collation. A limitation is that you cannot create the MSDC_Admin database ahead of time. If you are using the CmdLets to set up your resources, use the –DatabaseName parameter to set the existing name. If you are using Site Packager, you need to select the database name from the drop-down list on each resource.

If you are configuring Commerce Server from on-premise, the process can be slow. One reason is that the SQL calls need to travel across the internet instead of your local network. Another reason is that the S0 Price Tier of Azure SQL Database is not very powerful. You can streamline setup by pre-creating all of the databases you need, except for MSCS_Admin, and temporarily set them with a higher Price Tier than you need. Alternately, you could pass in a higher Price Tier to the CmdLets or PuP ini. Increasing the Price Tier during configuration will allow the commands to finish more quickly and reduce setup time, and then reduce the Price Tier when you are finished set up.