Using microsoft azure for SQL database backup and restore.

msSQL2014 has a feature to backup local database into azure storage container.

Quick steps:

1. Create a microsoft azure account, login, go to storage, create a storage, inside the storage create a container.
2. Create a credential in SQL server for azure using azure storage account and access key.
3. Backup database to storage url

In detail:

1. Create a azure storage container.

1. Get the azure access key.

Note from above we see the following parameters:( this is for my test installing and surely would be different for yours)

azure url : https://mssql2014bck.blob.core.windows.net/sqlbackup-1
azure storage account name : mssql2014bck
azure access key: Hu0Sm0Qd7QhU/eSZL+E1VrpNEh7szJFodTb434YGq2+q7qur+zY7XscWw953CTee29aKKV89v1amgfsT++tHzg==

1. create local credential by the following SQL

CREATE CREDENTIAL azure_bckup
WITH IDENTITY= ‘mssql2014bck’
, SECRET = ‘Hu0Sm0Qd7QhU/eSZL+E1VrpNEh7szJFodTb434YGq2+q7qur+zY7XscWw953CTee29aKKV89v1amgfsT++tHzg==’
GO

1. using management studio backup database to azure.

the same backup can be taken using SQL as follows

BACKUP DATABASE [azureDB]
TO URL = ‘https://mssql2014bck.blob.core.windows.net/sqlbackup-1/azuredb.bak’
WITH CREDENTIAL = ‘azure_bckup’;
GO

1. Go to azure interface to check the backup

The database can be downloaded into a local copy. which can be used to restore this database into another server or directly from azure this database can be restored into any SQL database having azure credential and URL.

In any SQL server create azure credential

CREATE CREDENTIAL azure_bckup
WITH IDENTITY= ‘mssql2014bck’
, SECRET = ‘Hu0Sm0Qd7QhU/eSZL+E1VrpNEh7szJFodTb434YGq2+q7qur+zY7XscWw953CTee29aKKV89v1amgfsT++tHzg==’
GO

using azure interface the backup file URL can be copied as follows:

then restore from azure by the following:

RESTORE DATABASE azureDB
FROM URL = ‘https://mssql2014bck.blob.core.windows.net/sqlbackup-1/dbbck.bak/azureDB_backup_2015_01_22_095246.bak’
WITH CREDENTIAL = ‘azure_bckup’;
GO

Once restore is successful you can see the database into management studio.

using azure container for backup and restore for SQL database is a very easy and cheap option to be considered.

However as this backup is unencrypted so security is a major concern and it is highly recommended to encrypet the database and then do the backup.

Also once a full backup is take  then differential backup in periodic interval can assure better point in time recovery as well as less storage utilization alone with less unlink bandwidth usage.

One of the other benefit of using azure storage container is, the backup database in azure can be very easily imported into a SQL server in azure, which can be considered as a fault tolerance idea or disaster recovery mechanism.

If you have backup a encrypted database into azure then dont forget to backup the certificate and key. without the certificate and key, you cant restore the database.

I have done a encrypted database backup to azure using the procedure stated above and I have restored the database in another server using the T-SQL as follows:

1. first create a master key and then then import the certificate into the database server( has to be in master database). I have put the key and certificate in C:\ drive as an arbitrary name AA.

use master
go
CREATE MASTER KEY ENCRYPTION BY PASSWORD = ‘V3RyC0mplicated#K3Y’;
go
CREATE CERTIFICATE cryptdbcert
FROM FILE = ‘c:\AA.crt’
WITH PRIVATE KEY (FILE = ‘c:\AA.key’,
DECRYPTION BY PASSWORD = ‘V3RyC0mplicated#K3Y’);
GO

then restore database from azure, first copy the azure container URL.

CREATE CREDENTIAL azure_backup
WITH IDENTITY= ‘mssql2014bck’
, SECRET = ‘Hu0Sm0Qd7QhU/eSZL+E1VrpNEh7szJFodTb434YGq2+q7qur+zY7XscWw953CTee29aKKV89v1amgfsT++tHzg==’
GO

RESTORE DATABASE azureDB
FROM URL = ‘https://mssql2014bck.blob.core.windows.net/sqlbackup-1/dbbck.bak/azureDB_backup_2015_01_22_095246.bak’
WITH CREDENTIAL = ‘azure_backup’;
GO

As we can see both non encrypted and encrypted databases can be backup and restore using azure storage container.

Cheers
Jamal Ahmed
jahmed@zoonetix.com

msSQL2014 AlwaysON transparent data encryption(TDE)

Quick note:

SQL management studio dont allow encrypted database to be added from the GUI. So we have the following work around to have encrypted database in msSQL2014 AlwaysON.

Briefly the procedure is as follows:

Primary node : add master key, create a certificate, backup key and certificate.

Primary node : create a dummy database, create the main database which will be encrypted.

Primary node : encrypt main database, backup the database.

Secondary node: import the key and certificate

Secondary node: restore encrypted database.

Primary node : create AG with the dummy database and both node as asycn, manual failover.

Primary node : add the main encrypted database using SQL command.

Secondary node: add the main database using SQL.

Primary node : change failover and sync mode to auto and sync.

In detail the procedure is described in sequence:

Primary node:

/* create master key*/

USE master;
GO
CREATE MASTER KEY ENCRYPTION BY PASSWORD = ‘V3RyC0mplicated#K3Y’;

/* create certificate*/
Use Master
GO
CREATE CERTIFICATE cryptdbcert
WITH SUBJECT = ‘crype db certificate’;
GO

/* database encryption */

USE cryptdb
GO
CREATE DATABASE ENCRYPTION KEY
WITH ALGORITHM = AES_128
ENCRYPTION BY SERVER CERTIFICATE [cryptdbcert]
GO
ALTER DATABASE [cryptdb] SET ENCRYPTION ON
GO

then take a backup of the master key and the certificate. keep the files in a secure place and copy securely to secondary node.( which will be imported into the secondary database)

/* backup master key*/
backup master key
to file = ‘c:cryptdb_master_key.key’
encryption by password = ‘V3RyC0mplicated#K3Y’;
go

/* backup certificate*/
backup certificate prod1cert
to file = ‘c:cryptdbcert.crt’;
go

/* backup the database */

BACKUP DATABASE cryptdb
TO DISK = ‘c:cryptdb-encrypted backup.trn’
With compression,
ENCRYPTION
(
ALGORITHM = AES_128,
SERVER CERTIFICATE = [cryptdbcert]
);
GO

BACKUP LOG cryptdb
TO DISK = ‘c:cryptdb-log.bak’
WITH INIT, STATS ;
GO

After this step create a dummy database which will be used to create availability group.

once AG is created add the encrypted database into the AG by the following SQL:

ALTER AVAILABILITY GROUP tde ADD DATABASE cryptdb

On the secondary node:

Copy the certificate and key that was backup in primary node to secondary node. Then import the certificate by the following( assuming the files are copied in C: drive)

/* import certificate */

CREATE CERTIFICATE cryptdbcert
FROM FILE = ‘C:cryptdbcert.crt’
WITH PRIVATE KEY
( FILE = ‘c:cryptdb_master_key.key’,
DECRYPTION BY PASSWORD = ‘V3RyC0mplicated#K3Y’);
GO

Now import encrypted database using management studio:

<< this is very important “restore with no recovery”

the join cryptdb in secondary node into AG by simply right clicking it.!

Now you can check AG status from the dashboard.

Should you have any query don’t hesitate to ask me.

Cheers
Jamal Ahmed
jahmed@zoonetix.com

msSQL2014 encrypted backup

msSQL2014 offers encrypted backup into local or share or cloud(azure) backup.

Quick notes:

1. Create a database master key
2. Create a certificate
3. select a new media set, certificate and location to take the backup in local/share disk

Detail steps:

1. Create a database master key and certificate using the following SQL

USE master;
GO
CREATE MASTER KEY ENCRYPTION BY PASSWORD = ‘C0mplicated#K3Y’;
Use Master
GO
CREATE CERTIFICATE prod1cert
WITH SUBJECT = ‘Production1 Backup Encryption Certificate’;

2. first do a database encryption management then take backup using the certificate.

    

Done! Backup is taken in encrypted file with server certificate named prod2dbCERT.

To restore this backup, administrator would required to use the same certificate and master Key, hence backing up this certificate is very important along with the master key. you can only restore a encrypted database if you have the certificate and master key which was used to take the encrypted backup.

Cheers
Jamal Ahmed
jahmed@zoonetix.com

msSQL2014 AlwaysON solution design diagram

msSQL2014 AlwaysON support automatic failover( either on node failure or network failure or service failure) to secondary node without any data loss in 3-6 sec.

It also does a sync commit on each database commit in primary database to  2 additional database. Among these 2 additional database 1 is the auto failover node and another one is manual failover node, manual failover database allowed planned activity to be carried node by node basis without any downtime.

msSQL2014 also have capability to have async commit, which assures additional high availability of database and data.

A recommended installation is 3 node cluster, however 4 node cluster offers primary and secondary site complete replica and in an event of primary site unavailability the secondary site will have full functionality to run production environment.

 

Cheers
Jamal Ahmed
jahmed@zoonetix.com

msSQL2014 AlwaysON Troubleshooting tips

You may get error or warnings are different levels of configuration and installation. Case by case basis the error reasons can be identified. You may contact me to know my experiences however Prime troubleshooting points I have listed below.

1.Check Firewall setting to allow 1433 and 5022 ports. try to telnet from each other host to verify that ports are opened.

1. Check if windows logon user has grant CONNECT permission on the replica end points. if not then create using the following SQL

USE master;
GO
CREATE LOGIN [domain/primary_node$] FROM WINDOWS;
GO
GRANT CONNECT on ENDPOINT::Hadr_endpoint TO [domain/primary_node$];
GO

1. check if replica end point is listening to the 5022 port or not.

• If endpoint is not available then then create the port using following SQL

create endpoint [Hadr_endpoint]
state=started
as tcp (listener_port = 5022, listener_ip = all)
for database_mirroring (role = all, authentication = windows negotiate, encryption = required algorithm aes)
GO

• If endpoint is not started then start using the following SQL

alter endpoint [Hadr_endpoint] state = started

1. check error description using the following SQL

select r.replica_server_name, r.endpoint_url,
rs.connected_state_desc, rs.last_connect_error_description,
rs.last_connect_error_number, rs.last_connect_error_timestamp
from sys.dm_hadr_availability_replica_states rs join sys.availability_replicas r
on rs.replica_id=r.replica_id
where rs.is_local=1

in the output, in column “error description” you will find significant important information to troubleshot.

1. sometime AlwaysON databases might get out of sync and may show as suspect or Not syncronizing

Run the following SQL to resume it.

ALTER DATABASE [database name] SET HADR RESUME

1. AG is in resolving state

it could be due to network fluctuation or node going into high CPU utilization. to fix increase the timeout by the following SQL

AVAILABILITY GROUP AG1 SET (HEALTH_CHECK_TIMEOUT = 40000);