More and more companies are using cloud infrastructure to host their applications and databases. In this article we will discuss the high level steps to move on-premises SQL Server database to Azure SQL database using Microsoft SQL Server Management Studio:

  1. Download and install the latest version of Microsoft SQL Server Management Studio, old version can give unexpected errors as now Azure SQL databases have almost same set of features as on-premises databases can have.
  2. Right click on

 ...  read more

Hosting providers unload your application after certain amount of time if the application is idle. Microsoft Azure provide Always ON option (with some extra cost) to keep the application always running. You can enable these settings from Azure Portal as show below:

However there is an issue if your application is running on HTTPS and you have rewrite rules to redirect HTTP traffic to HTTPS then Always ON settings in Azure will not work.

You can fix it in many ways, here is one of them. Add the following rewrite rule in your web.config, it should be first rule (if you have other rules in web.config):

<system.webServer>   
    <rewrite>
      <rules>
        <rule name="Top Rules" enabled="true" stopProcessing="true">
          <!-- Please always keep this rule on the top of list -->
          <match url=".*" />
          <conditions logicalGrouping="MatchAny">
            <add input="{HTTP_USER_AGENT}" pattern="Initialization" ignoreCase="true"/>
            <add input="{HTTP_USER_AGENT}" pattern="AppInit" 		ignoreCase="true" />
            <add input="{HTTP_USER_AGENT}" pattern="WarmUp" 		ignoreCase="true"/>
            <add input="{HTTP_USER_AGENT}" pattern="AlwaysOn" 		ignoreCase="true"/>
          </conditions>
          <action type="Rewrite" url="{URL}" />
        </rule>
 
      </rules>
    </rewrite>
  </system.webServer>

DateTime.Now returns the time by taking into account server’s time zone on which application is hosted. As Azure web apps has time zone set to UTC even its location is somewhere else say in central US.

When we try to access the server’s time using DateTime.Now, it returns UTC time.

You can fix this issue by adding the following application settings using Azure Portal:
Key: WEBSITE_TIME_ZONE
Value: Time zone of the application where it is hosted in Azure say Central Standard Time

You can get the name of time zones from here

It will save you from modifying your code base, you just have to set this key in one place.

In this article we will see how to take Microsoft SQL Server database backup to Azure storage. At high level, we have to do the following to achieve this:

  1. Create storage account in Microsoft Azure
  2. Create container in Azure storage account
  3. Create SQL Server credential using information from Microsoft Azure storage account
  4. Taking backup using Microsoft SQL Server Management Studio
  5. Taking backup using SQL code

1. Create storage account in Microsoft Azure

Login to Azure Portal. Create a storage account. To add a storage account, click on the Storage accounts on left and then click on +Add option on top.

It will show the following options, fill in all the details but remember to select General purpose under Account kind, it is circled in the image, otherwise it will give error while taking backup.

2. Create container in Azure storage account

Once storage account is created, we need to create a Container which will keep the database backup files. To add a container, click on Storage accounts, all the storage accounts in your Azure account will be shown. Click on the newly created storage account to open its properties. Click on Containers then on +Container, give the name and click OK as shown in the image below:

3. Create SQL Server credential using information from Microsoft Azure storage account

Use the following SQL code to create credential to use while taking backup.

use master;
CREATE CREDENTIAL AzureBlobStorageCredential WITH IDENTITY = 'storageaccountname'
, SECRET = 'storage account access key';

AzureBlobStorageCredential: Name of this credential, you can name it any.
IDENTITY: It is the Azure storage account’s name as shown in the image below.
SECRET: It is the storage account’s access key as shown in the image below.

4. Taking backup using Microsoft SQL Server Management Studio

Right click on the desired database, click on Tasks and the click on Back Up…
Make the selections/changes on Back Up Database dialog as shown below:

5. Taking backup using SQL code

Use the following SQL code to take database back up to Azure Storage

 Declare @BackupUrl AS NVARCHAR(max) = N'https://stoarageaccountname.blob.core.windows.net/containername/backupfilename.bak';

 ...  read more