casinos

Archive for the ‘Technology’ Category

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


 

Use existing connections in PowerPivot and Visual Studio April 1st, 2014

Vinod Kumar

I have been fortunate enough to work with a lot of data which involve using of PowerPivot or customers using Tabular model using BISM. Though there are striking similarities in building both the tasks, the mistakes made in both these models are almost the same. Let me talk you through this for a moment.

Whenever we work with PowerPivot or BISM model and we are importing data from a data source, the initial tendency is to use the “From Database” or “From Other Sources” tab and we start building the model. This is a fair step and in doing a data mashup, we bring data from multiple sources.

Many a times we might connect to the same data source using the above step. And to our irony we will encounter the following in our existing connections. In the example below, I have made 3 different query to the same data source and it is now pointing to the same source – 3 times. Isn’t it inefficient? I have seen customers having close to 20-25 connections to the same source but they are bringing some 20-25 different tables. I have observed the performance during “refresh” gets severely hampered by this approach.

So how to get around this? The steps are simple. Whenever you want to connect to the same source – click on the “Existing Connections” button.

Select the source that you want to reuse as a connection. And now select “Open”.

We will be presented with the same wizard as we normally do with “New Connection” Wizard.

The steps are similar when using the same in Visual Studio too when developing BISM models.

Continue reading...