Feeds:
Posts
Comments

SQL Server backup to URL is a new feature shipped with SQL Server 2014. Now, you do not need to manage backup tapes, tape drives, storage etc etc. All you need to do is to create a Windows Azure account, a storage and a container, remaining stuff Azure will handle your backup ;)

Pre-Requisite :

Let me now demonstrate in few easy steps how to take backup to URL.

Step 1 – Create Account, storage & container :
First of all you need to create a windows Azure account, a storage and a container as explained in this article.

Step 2 – Create Credential :
Once you have the details, you need to create the credential using given below script. But if you notice, this script requires two parameters namely  IDENTITY & SECRET. So how will I get them? Actually IDENTITY is the storage name that we created in the previous article (Step 4) namely ‘raresql‘ and SECRET is the primary access key that we also picked up in the previous article (Step 5). Lets create a credential.

USE master
GO
CREATE CREDENTIAL [My_Credential]
     WITH IDENTITY ='raresql'
    ,SECRET = '/ByNUTZqJ6EcJR/VQcNmNj+zSu++iCfbcxlyWye6Ok9uY3L5nw3XkndmAnDjiKn'
GO

Step 3 – Take backup to URL :
Once you created the credential, lets take a backup on URL using given below script, which requires two parameters, one is credential that we created in the above step and the second is the URL that we created in the previous article (Step 10).

BACKUP DATABASE AdventureWorks2012
TO URL = 'https://raresql.blob.core.windows.net/sql-backup/AdventureWorks2012.bak'
      WITH CREDENTIAL = 'My_Credential'
     ,STATS = 5;
GO
--OUTPUT

How to  take backup to URL.1.1

Step 4 – Verify backup to URL :
Lets go to the container inside the storage and check if the backup is available there or NOT. Because, we saw the confirmation report in the above step.

How to  take backup to URL.1.2

I am sure, you will be more than excited to try this solution, as this will eliminate your headache of maintaining the tape, its drives and last but not the least, its issues.

Recently, I came across a situation, where I had to place my SQL Server back on Microsoft Azure. Before placing the backup on Azure, there are some pre-requisites required, for example a valid account, a storage and a container on Azure. Given below is a step by step approach,  demonstrating how to create them using simple steps.

Step 1 :
First of all, you need to create a Microsoft Azure account (It is free for one month ONLY, however it asks the credit card details for verification ONLY).

Free tial account of Azure.1.1

Step 2 :
Once you create the account and Sign in to Microsoft Azure, you will be in the Microsoft Azure portal as shown below. Here you can see a lot of different exciting features, however, you need to select storage to create a NEW storage.

Azure Storage 1.2

Step 3 :
Now you are in the storage screen and Azure is showing that you have no storage because you have a new account, so you should create a storage as shown below. If you already have a storage you can utilize it.

create storage 1.3

Step 4 :
Once you click on the new storage account, Azure will take you to the storage creation screen and require a unique URL name that you can reference later. You should leave the remaining info as it is and click on create storage account button at the bottom as shown below. In my case I named the storage as ‘raresql‘.

create storage 1.4

Once your storage has been created, it would look like this :

create storage 1.4.1png

Step 5 :
Now, you have created the storage. The next step is to take the access keys that you require later to keep anything in this particular storage. In order to get the access keys, you need to click on the manage access keys button as shown below.

Azure Storage 1.5.2

Once you click on manage access keys, it will show the storage name, primary and secondary access keys.

Azure Storage 1.5.3

Step 6 :
The next step is to create the container, where you can keep your backups. Just click on raresql (storage)  in order to create container as shown below.

Azure Storage 1.5

Step 7 :

Once you click on the storage, it will take the storage dashboard and you will find a lot of exciting features here as well, however, you just need to select the container tab as shown below.

Azure Storage 1.5.1

Step 8 :
Once you click on the container tab, it will take you to the container creation screen as shown below. Here you need to click on create a container to proceed.

Azure Storage 1.6

Step 9 :
Once you click on create a container button, it will pop up the container creation screen. Here you need to enter the container name and its security and click the tick button at the right bottom. By default the security is private as shown below.

Azure Storage 1.7

Step 10 :
Once you click the tick, your container will be created as shown below. However, please make a note of your container name and its URL. This will help you to reference your container back.

Azure Storage 1.8

Let me know your comments if you face any problem during the creation of storage and container.

I came across this scenario many times when you need to find the largest tables in the database, specially at the time of maintenance or support. Fortunately we do have a solution in the shape of different scripts that you can easily find in many blogs and sites. In fact, I have written a solution earlier. In this article, I will discuss how to find the largest object without writing a single line of script.

Let me explain it step by step by step.

Step 1 :
First, open SQL Server Management Studio (SSMS) and select the target database (where you need to find the largest object) and right click on it as shown below.

Find largest table 01

Step 2 :
Once you right click on the target database you will see a lot of options. Select reports \ standard reports \ Disk Usage by Top Tables as shown below. There are other very useful reports that give you the statistical data about your SQL Server.

Find largest table 02

Step 3 :
Once you click on reports \ standard reports \ Disk Usage by Top Tables, SSMS will open a report showing which tables are using more Disk spaces, reserved space & having highest record counts etc as shown below.

Find largest table 03

Step 4 :
Now you have the report and by default it is sorted by Reserved (KB), but you can sort it with any column (# Records (Actually record count), Reserved(KB), Data(KB), Indexes(KB), Unused(KB)) by clicking on up and down sign as shown below.

Find largest table 04

The beauty of this feature (Standard reports) is that you can generate the statistical report within few seconds, just by click of a menu. In addition you can export / print this report in a nice format without any extra effort as shown below.

Note : Please do not forget to refresh this report before export or print ;).

Let me know if you tried this solution and how did you like it ?

Today I had a very informative session in SQL Server community meet up at Microsoft Office in Dubai, UAE. This usually occurs every last Tuesday of the month. I must appreciate the SQL Server User group (www.uaessug.com) who have been organizing it very well for the last one year. This meet up usually consists of two comprehensive and informative sessions that focus on the SQL Server latest technology, best practice, user experience, tips & tricks. I usually attend this meet up because I get an opportunity to network with SQL Server experts face to face instead of Facebook chat :). I was the speaker in today’s meet up along with Mr. Mohammed Owais (CTO at CAZAR).

Following topics were covered in today’s session:

  • UDFs are evil and other execution plan nightmares presented by Mr. Mohammed Owais
  • The easy path to compliance with SQL Server Audit presented by me

We focused on delivering the latest technology enhancements, best practices and troubleshooting (the scariest part) throughout the event and hands on with live demos to demonstrate the real world challenges and its solution.

I hope today’s session was informative and believe that everybody got to add something to their knowledge-village :)
Our next meetup will be in January 2015.

Some pictures from today’s sessions.

Mr. Mohammed Owais busy explaining UDFs execution plans :

IMG_9153

Me presenting SQL Server Auditing :

IMG_9156

Group discussions :

IMG_9152

IMG_9154

IMG_9155

IMG_9157

IMG_9158

Mr. Waqar discussing BI with Mr. Kevin :
IMG_9159

Thank you guys.

Recently, I was developing a report for one of my customers, who migrated their database from SQL Server 2005 to SQL Server 2012 Enterprise Edition. The database belongs to a legacy system , so I found couple of conversion issues, however the major issue I faced in developing the report was the date format. The required date format was Mon-yyyy and the data available in the table was in the format of yyyymm.

Before proceeding with the solution, let me create a sample to demonstrate the problem.

USE tempdb
GO
DROP TABLE tbl_Sample
GO
CREATE TABLE tbl_Sample
(
 [ID] INT,
 [Date] varchar(6)
)
GO
INSERT INTO tbl_Sample VALUES (1,'201401')
INSERT INTO tbl_Sample VALUES (2,'201402')
INSERT INTO tbl_Sample VALUES (3,'201403')
INSERT INTO tbl_Sample VALUES (4,'201404')
INSERT INTO tbl_Sample VALUES (5,'201405')
INSERT INTO tbl_Sample VALUES (6,'201406')
INSERT INTO tbl_Sample VALUES (7,'201407')
INSERT INTO tbl_Sample VALUES (8,'201408')
INSERT INTO tbl_Sample VALUES (9,'201409')
INSERT INTO tbl_Sample VALUES (10,'201410')
INSERT INTO tbl_Sample VALUES (11,'201411')
INSERT INTO tbl_Sample VALUES (12,'201412')
GO
SELECT * FROM tbl_Sample
GO

Convert YYYYmm 1.1

Solution 1 : Traditional Method
This is the old traditional method, which is compatible with SQL Server 2005 and above. In this method, you need to use many functions (LEFT, DATENAME, CONVERT, YEAR) to convert yyyymm date format to Mon-yyyy. Given below is the script.

USE tempdb
GO
SELECT
LEFT(DATENAME(MM,CONVERT(DATE,[Date]+'01')),3)
+ '-'
+ CONVERT(VARCHAR(4),YEAR(CONVERT(DATE,[Date]+'01'))) As [Mon-Year]
FROM tbl_Sample
GO

Convert YYYYmm 1.2

Solution 2 : New Method
This is the new method, which is compatible with SQL Server 2012 and above. In this script, I will use a newly shipped (in SQL Server 2012) function namely Format to reduce the line of script in order to convert yyyymm to Mon-yyyy. Given below is the script.

USE tempdb
GO
SELECT FORMAT(CONVERT(DATE,[Date]+'01'),'MMM-yyyy') As [Mon-Year]
FROM tbl_Sample
GO

Convert YYYYmm 1.2

Conversion of Select statement result set into Insert statement is a very frequent activity that a DBA/Developer needs to create, mostly when they need to migrate small amount of data from one instance to another or from one environment to another. I recently created one of my customer’s new branch database from other branches database and came across this scenario. Fortunately, we do have a solution since SQL Server 2005 but it was very complicated specially when you need to do it for the tables as they have numerous columns. The reason I am writing this solution is that you can do it in few clicks in SQL Server 2012 and above.
Before proceeding with the solution, I would like to create a sample to demonstrate the solution.

Sample :
Given below is a select statement in which I modified the result set to demonstrate.

USE [AdventureWorks2012]
GO
SELECT [DepartmentID]
      ,[Name] + ' Department' As [Name]
      ,[GroupName]
      ,Getdate() As [ModifiedDate]
   FROM [HumanResources].[Department]
GO

Convert select.1.1

Given below are the two solutions, one of them is traditional solution and another one you can use it in SQL Server 2012 and above.

Solution 1 : Using String concatenation (Traditional Method)
In this solution, you need to concatenate the result set of the Select statement in order to convert into Insert statement (with some modifications in the data). You need to make sure that single quotes(‘) are in proper locations. In addition, if the data in the result set does not belong to string data type you must convert into string data type to concatenate. In case, the table is having identity column, you must pass the column name in the INSERT STATEMENT as well with SET IDENTITY_INSERT. The reason why I DO NOT recommend this solution is because if you have more number of columns in the table, it takes more time for the development and debug as well.

USE [AdventureWorks2012]
GO
SELECT
'INSERT INTO tbl_sample (
 [DepartmentID],[Name],[GroupName],[ModifiedDate])
VALUES(' + CONVERT(VARCHAR(50),[DepartmentID])
+ ',''' + [Name] + ' Department'' ,'
+ ''''+ [GroupName] + ''','
+ ''''+ CONVERT(VARCHAR(50),GETDATE(),120) + ''')'
  FROM [HumanResources].[Department]
GO

Convert select.1.2

Solution 2 : Using Generate Script (New Method)
This method is applicable to SQL Server 2012 and above and you will find it quite simple. Let me explain this method using two simple steps.

Step 1 :
First of all, you need to develop a select statement like I did it in the sample based on your requirements and INSERT INTO A TABLE as shown below.

 USE [AdventureWorks2012]
GO
SELECT [DepartmentID]
      ,[Name] + ' Department' As [Name]
      ,[GroupName]
      ,Getdate() As [ModifiedDate]
  INTO [tbl_Department_Sample]  -- Result set inserted in a table
  FROM [HumanResources].[Department]
GO

Convert select.1.3

Step 2 :
Your select statement result set has been inserted into the table([tbl_Department_Sample]). Now, you just need to generate the script (data only) of the table ([tbl_Department_Sample]) using Generate Script feature in SQL Server 2012 and above.

Let me know if you come across these scenarios and their solutions.

Change Data Capture (CDC) is one of the frequently used features in SQL Server 2008 and above, it records any (CDC enabled) table’s changes and stores in audit tables. Recently, I upgraded one of my client’s database from SQL Server 2005 database to SQL Server 2012 and one of the key reasons to upgrade is to utilize the new features in the upgraded version. Once I started enabling CDC feature in few tables of the database it gave me given below error.

Message Number: 22939

Severity : 16

Error Message: The parameter @supports_net_changes is set to 1, but the source table does not have a primary key defined and no alternate unique index has been specified.

Error Generation:
I presume that CDC has been enabled on this particular database. Let me create a sample table to demonstrate this error.

USE AdventureWorks2012
GO
--Create Sample Table
CREATE TABLE tbl_Sample
(
 [ID] INT NOT NULL,
 [NAME] VARCHAR(50)
)
GO

-- Enable CDC feature on this table with net changes support parameter.
 USE AdventureWorks2012
 GO
 EXEC sys.sp_cdc_enable_table
 @source_schema = N'dbo',
 @source_name = N'tbl_sample',
 @role_name = NULL,
 @supports_net_changes = 1
GO
--OUTPUT

Error Message 22939.1

Msg 22939, Level 16, State 1, Procedure sp_cdc_enable_table_internal, Line 194
The parameter @supports_net_changes is set to 1, but the source table does not have a primary key defined and no alternate unique index has been specified.

Ooopps…… I am NOT able to enable CDC on sample table.

Resolution:
The reason behind this error is that you do not have either primary key or unique index on the sample table and you want to enable net changes support in CDC. Before proceeding with the resolution, you should check whether you can create a Primary Key or Unique Index on the table to avoid such error.
Given below is the simple script to add Primary Key OR Unique Index in the sample table.

--Create Primary Key
USE AdventureWorks2012
GO
ALTER TABLE dbo.tbl_Sample ADD PRIMARY KEY (ID)
GO

-- Create Unique Index
USE AdventureWorks2012
GO
ALTER TABLE tbl_Sample
ADD CONSTRAINT UX_Constraint UNIQUE (ID)
GO
--OUTPUT

You can execute any one of the above scripts and can create Primary Key or Unique Index in the sample table.

Once you executed the above script, you can easily enabled the CDC with supports_net_changes as shown below.

 USE AdventureWorks2012
 GO
 EXEC sys.sp_cdc_enable_table
 @source_schema = N'dbo',
 @source_name = N'tbl_sample',
 @role_name = NULL,
 @supports_net_changes = 1
 GO
--OUTPUT

Command(s) completed successfully.

Error Message 22939.2

Conclusion :
Remember, whenever you need to enable CDC with supports_net_changes, make sure that the particular table HAS either Primary Key or Unique Index in order to avoid this error.

Follow

Get every new post delivered to your Inbox.

Join 281 other followers