casinos

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...


 

Don’t ruin your beautiful life April 10th, 2014

Vinod Kumar

Worrying is like a rocking chair, it gives you something to do, but it gets you nowhere – Glenn Turner

I always say, life is full of surprises and everything happens for a reason. There is so much we learn from each other that the experience we get must be for a lifetime. If we don’t run behind our dreams we are never going to get it. If we don’t ask the right questions, we will always be presented with wrong answers. You will eventually be run over even if you are sitting idle on the right path. Understanding life’s choices is a critical activity, for a fact, life is just a choice we make – Period.

Here are some of the choices I wish we never make these mistakes in our lifetime:

  • Thinking about the past and worrying.
  • Fearing change and resisting it.
  • Dream big and not take any positive step towards it.
  • Taking everything personally and seriously.
  • Put needs of your loved ones in the back burner.

Today is the tomorrow we worried about yesterday.

  • Making others feel bad about themselves.
  • Not speaking out what is in your mind.
  • Thinking about lost opportunities and whining.
  • Trying to do everything at once. If everything is important, then nothing is.
  • Trusting no one.

If you want to test your memory, try to recall what you were worrying about one year ago today. ~E. Joseph Cossman

  • Shy to ask for help.
  • Failing to learn from mistakes.
  • Blaming others and not taking ownership.
  • Question your own abilities and being with people who pull you down always.
  • Never explored new ways of doing things and doing the same thing expecting different results.

Worry is a misuse of imagination. ~Dan Zadra

  • Not being true to yourself.
  • Being jealous of people around you.
  • Making small issues a bigger problem to solve.
  • Keep complaining of problems and not taking actions.
  • Running behind money and forgetting the inner passion you once had.

Worry never robs tomorrow of its sorrow, it only saps today of its joy. ~Leo Buscaglia

I personally think there is lots more to write on things I wish we dont do in our lifetime. Do let me know if these resonate a bell in your mind and have you experienced these in your lifetime?

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...