casinos

Posts Tagged ‘ITPro’

SQL Server 2014: Backup to Azure Blob April 14th, 2014

Vinod Kumar

SQL Server 2014 has this new capability which allows us to take a backup to a URL. Infact, it is available in SQL 2012 SP1 CU2 itself – I lied to you in my first statement :). Anyways, I thought it is worth a mention and a blog for itself.

The concepts of accessing the keys have been mentioned also at: Viewing Windows Azure Storage using SSMS.

Copy the keys and we are ready.

Now that you have these key’s handy. We will take an encrypted backup as explained in SQL Server 2014: Encrypted Backups and upload it into Azure Blob storage rather than our conventional disk subsystem. So here are the steps to follow.

Create your credential

The create credential command has been there since SQL Server 2005 but I never found a better scenario as this to use the same. So here I am sort of creating my proxy name to access the blob storage account.

CREATE CREDENTIAL mycredential

WITH IDENTITY= ‘mtcvinod’

, SECRET = ‘<< INSERT YOUR KEY FROM PORTAL >>’

GO

Backup your database to Azure

The next step is to backup your database. This command is ALMOST similar to a normal backup statement with the ONLY change where we point to a URL rather than a disk location. Otherwise everything is same.

BACKUP DATABASE[MySecureDB]

TO URL = ‘http://<< TODO: ENTER YOUR CONTAINER URL >>/backup/04-mySecureDB_compress_encrypt.bak’

WITH CREDENTIAL = ‘mycredential’,

  NOFORMAT, NOINIT,

  NAME = N’mySecureDB – compressed, encrypted’,

  SKIP, NOREWIND, NOUNLOAD, COMPRESSION,

  ENCRYPTION (

   ALGORITHM = AES_256,

   SERVER CERTIFICATE = BackupCert

   ),

  STATS = 10;

GO

If you are playing around with this script again and again, make sure you delete the file that got uploaded the previous time. Else we will be presented with the below error 3271. Alternatively you can add the WITH FORMAT option too.

Msg 3271, Level 16, State 1, Line 7
A nonrecoverable I/O error occurred on file http://vinod.blob.core.windows.net/backup/04-mySecureDB_compress_encrypt.bak: The file http://vinod.blob.core.windows.net/backup/04-mySecureDB_compress_encrypt.bak exists on the remote endpoint, and WITH FORMAT was not specified. Backup cannot proceed..
Msg 3013, Level 16, State 1, Line 7
BACKUP DATABASE is terminating abnormally.

Restore from URL

Just like we did a backup, now we can also do a restore using a similar command with the change being the URL instead of a Disk location.

RESTORE DATABASE [MySecureDB]

FROM URL = ‘http://<< TODO: ENTER YOUR CONTAINER URL >>/backup/04-mySecureDB_compress_encrypt.bak’

WITH CREDENTIAL = ‘mycredential’

, STATS = 5

GO

To Viewing Windows Azure Storage using SSMS, read that blogpost or feel free to use other Azure Blob tools like the one I have used here is Cloudberry or any others of your choice.

There are a number of other error messages which are self-explanatory which need a mention here. If you are going to restore from this backup on a database which is running currently, you might get the below error:

Msg 3159, Level 16, State 1, Line 21
The tail of the log for the database "MySecureDB" has not been backed up. Use BACKUP LOG WITH NORECOVERY to backup the log if it contains work you do not want to lose. Use the WITH REPLACE or WITH STOPAT clause of the RESTORE statement to just overwrite the contents of the log.

If the backup URL mentioned doesnt exist in the given location then we will get the blow error:

Msg 3271, Level 16, State 1, Line 21
A nonrecoverable I/O error occurred on file "http://vinod.blob.core.windows.net/backup/04-mySecureDB_compress_encrypt.bak:" Backup to URL received an exception from the remote endpoint. Exception Message: Error occurred while referencing Blob in remote storage.

PS: Note, the above example uses encrypted backups – but the same can be achieved using normal backups too.

In my subsequent blogs, let me also bring out some of the other errors one might get by using Encrypted Backups.

Continue reading...


 

SQL Server 2014: Restoring Encrypted Backups April 9th, 2014

Vinod Kumar

What is the point in taking encrypted backup (SQL Server 2014: Encrypted Backups), if we are not sure how to restore them. This blog is about taking a cue from the previous post and we will detail on the steps to restore the same.

Please follow the steps we used in the previous blog – SQL Server 2014: Encrypted Backups as we plan to continue using the same files for restore process in this post.

Restore an Encrypted Backup – Error 33111

If you take an encrypted backup and restore onto a new separate server, it will Error with the following message.

Msg 33111, Level 16, State 3, Line 2
Cannot find server certificate with thumbprint ’0x210E6D33163FE11C581911873F04857C8EBFC0BD’.
Msg 3013, Level 16, State 1, Line 2
RESTORE DATABASE is terminating abnormally.

As the error suggests, this is a classic case of the certificates missing on this new server. Remember the step of taking a backup of our certificates in our previous article. Now it is going to help us.

Restore the Certificate

The command to restore the same is:

Use [master]

CREATE CERTIFICATE BackupCert

    FROM FILE = ‘D:\SQLFiles\Backup\BackupCert.cert’

    WITH PRIVATE KEY ( FILE = ‘D:\SQLFiles\Backup\BackupCert.privatekey’ ,

    DECRYPTION BY PASSWORD = ‘MyCertPassword-2014′ );

GO

restore Encrypted Backup

The restore statement is same irrespective of encryption or not. The command in our case would look like:

USE [master]

GO

RESTORE DATABASE [MySecureDB]

FROM DISK = N’D:\SQLFiles\Backup\03-mySecureDB_nocompress_encrypt.bak’

WITH FILE = 1,

NOUNLOAD,

STATS = 10

GO

It is as simple as it gets. One of the other enhancement that I mentioned before about messages while restore or backup can also be read – SQL Server 2014: Backup Informational messages.

Continue reading...


 

SQL Server 2014: Encrypted Backups April 8th, 2014

Vinod Kumar

The learning on SQL Server 2014 continues and in this blog, I will walk through TSQL on what are the steps involved in creating an Encrypted backup using SQL Server 2014. The steps involved are:

  1. Create or identify your database (with data)
  2. Setup a Master Key
  3. Create a Certificate which we will use
  4. Backup your Certificate as a best practice
  5. Take the backup using the Certificate

Step 1: Create or identify your database (with data)

In my example, I am just creating a dummy database for testing.

CREATE DATABASE [MySecureDB];

If you want, go ahead and create a table with some values for later reference when we use restore scripts.

USE MySecureDB

GO

CREATE TABLE client_data(name NVARCHAR(30), CreditCard CHAR(20))

GO

INSERT INTO client_data VALUES(‘Pinal’, ’123-01-1234′)

          ,(‘Balmukund’, ’123-01-1235′)

          ,(‘Vinod’, ’123-01-1236′);

GO

SELECT * from client_data

GO

Step 2: Setup a Master Key

The next logical step is to create a Master Key.

USE [master];

GO

CREATE MASTER KEY ENCRYPTION BY PASSWORD = ‘MyComplexPassword-2014′

GO

Step 3: Create a Certificate which we will use

Next step is to create a certificate which we will use for encryption requirements.

USE [master];

GO

CREATE CERTIFICATE BackupCert

   WITH SUBJECT = ‘My Backup Encryption’;

GO

Step 4: Backup your Certificate as a best practice

This is an important and critical step, always DONOT forget to backup your certificates.

Use [master]

BACKUP CERTIFICATE BackupCert TO FILE = ‘D:\SQLFiles\Backup\BackupCert.cert’

    WITH PRIVATE KEY ( FILE = ‘D:\SQLFiles\Backup\BackupCert.privatekey’ ,

    ENCRYPTION BY PASSWORD = ‘MyCertPassword-2014′);

GO

Step 5: Take the backup using the Certificate

The next step is the easiest step of backing up your database using an certificate.

BACKUP DATABASE [MySecureDB]

TO DISK = N’D:\SQLFiles\Backup\03-mySecureDB_nocompress_encrypt.bak’

WITH

  NOFORMAT, NOINIT,

  NAME = N’mySecureDB – uncompressed, encrypted’,

  SKIP, NOREWIND, NOUNLOAD, NO_COMPRESSION,

  ENCRYPTION (

   ALGORITHM = AES_256,

   SERVER CERTIFICATE = BackupCert

   ),

  STATS = 10

GO

As a bonus step, you can also compress an encrypted backup. From the above statement, just change from NO_COMPRESSION to COMPRESSION to check its effect on your backups. To look at a UI option to enable the same from my friend Balmukund’s post on the same topic – SQL 2014 Learning Series # 3 – New Feature – Backup Encryption.

In future posts, I will walk through some more scenarios of Restore, Failures and troubleshooting while restoring encrypted backups.

Continue reading...


 

SQL Server 2014: CREATE INDEX in CREATE TABLE construct April 2nd, 2014

Vinod Kumar

This is one of the simplest post that I could think for the mid-week post :). There is nothing big about this feature to talk but surely worth a mention. I was reading the documentation of CREATE TABLE in SQL Server 2014 and saw an extension now available to create indexes as part of the construct inline with the same command. I know for folks who use keyboard and not the UI, this is a great boon.

A simple construct will look like:

CREATE TABLE dbo.myTab1 (

col1 int not null,

col2 int not null,

INDEX myTab1_col1_col2 NONCLUSTERED (col1, col2)

);

GO

This for me was a nice T-SQL extension and worth a note. Do check the CREATE TABLE documentation for more information and options available.

Continue reading...


 

SQL Server 2014: New function sys.fn_hadr_is_primary_replica March 25th, 2014

Vinod Kumar

My latest explorations have been to look at what are the fine print details that got added with SQL Server 2014. Especially, I have been exploring what has fundamentally changed when it comes to SQL Server 2014 – AlwaysOn capabilities. Though at the outset this looks simple and basic, some of these enhancements are pretty cool and needs a mention. Hence you are getting these bunch of blogposts inspired by this learning.

In SQL Server 2012, we had this neat little function – sys.fn_hadr_backup_is_preferred_replica which allowed us to be used during the backup operation if the replica we are running backup against is the preferred replica as per the AlwaysOn configuration. Though it was a neat addition there was no way to figure out if the node on which the Maintenance plan was running was ever the Primary replica. To illustrate the enhancement, I am having the following configuration as shown in the figure below.

As you can see I have two servers SQLALWAYSON01 and SQLALWAYSON02 servers and the -01 server is the PRIMARY. With SQL Server 2014, there is a new function that gets introduced – sys.fn_hadr_is_primary_replica. This nifty function when passed with the database name will return 1 if it is run on the PRIMARY. Else it will return you 0, if invoked from the secondary replicas.

In my opinion this is a critical and nifty option because you might want to do certain operations ONLY on the Primary like index rebuilding, full backups or whatever. Apart from these values, if you by any chance pass a non-AG database to the function it will return you NULL as shown in the figure below. Do let me know if you will use this function and what are the scenario’s you will find this useful. It can surely be a great learning for me too to listen from you.

This post is inspired from Book content: SQL Server 2012 AlwaysOn Joes 2 Pros®: A Tutorial for Implementing High Availability and Disaster Recovery using AlwaysOn Availability Groups” (Paperback, Kindle).

Continue reading...