Jul 29, 2011

Encrypt and Decrypt databases

/* Create Database  */
USE master
GO
CREATE DATABASE EncryptTest
ON PRIMARY ( NAME = N'EncryptTest', FILENAME = N'C:\EncryptTest.mdf')
LOG ON ( NAME = N'EncryptTest_log', FILENAME = N'C:\EncryptTest_log.ldf')
GO

/* Create table and insert data in the table */
USE EncryptTest
GO
CREATE TABLE TestTable (FirstCol INT, SecondCol VARCHAR(50))
GO
INSERT INTO TestTable (FirstCol, SecondCol)
SELECT 1,'First'
UNION ALL
SELECT 2,'Second'
UNION ALL
SELECT 3,'Third'
UNION ALL
SELECT 4,'Fourth'
UNION ALL
SELECT 5,'Fifth'
GO


/* Check the content of the TestTable */
USE EncryptTest
GO
SELECT *
FROM TestTable
GO


/* Create Database Master Key */
USE EncryptTest
GO
CREATE MASTER KEY ENCRYPTION
BY PASSWORD = 'SQLAuthority'
GO


/* Create Encryption Certificate */
USE EncryptTest
GO
CREATE CERTIFICATE EncryptTestCert
WITH SUBJECT = 'SQLAuthority'
GO

/* Create Symmetric Key */
USE EncryptTest
GO
CREATE SYMMETRIC KEY TestTableKey
WITH ALGORITHM = TRIPLE_DES ENCRYPTION
BY CERTIFICATE EncryptTestCert
GO

/*  Encrypt Data using Key and Certificate
Add Columns which will hold the encrypted data in binary */
USE EncryptTest
GO
ALTER TABLE TestTable
ADD EncryptSecondCol VARBINARY(256)
GO


/* Update binary column with encrypted data created by certificate and key */
USE EncryptTest
GO
OPEN SYMMETRIC KEY TestTableKey DECRYPTION
BY CERTIFICATE EncryptTestCert
UPDATE TestTable
SET EncryptSecondCol = ENCRYPTBYKEY(KEY_GUID('TestTableKey'),SecondCol)
GO


/* DROP original column which was encrypted for protect the data */
USE EncryptTest
GO
ALTER TABLE TestTable
DROP COLUMN SecondCol
GO


/* Check the content of the TestTable */
USE EncryptTest
GO
SELECT *
FROM TestTable
GO


/* Decrypt the data of the SecondCol  */
USE EncryptTest
GO
OPEN SYMMETRIC KEY TestTableKey DECRYPTION
BY CERTIFICATE EncryptTestCert
SELECT CONVERT(VARCHAR(50),DECRYPTBYKEY(EncryptSecondCol)) AS DecryptSecondCol
FROM TestTable
GO


/* Clean up database  */
USE EncryptTest
GO
CLOSE SYMMETRIC KEY TestTableKey
GO
DROP SYMMETRIC KEY TestTableKey
GO
DROP CERTIFICATE EncryptTestCert
GO
DROP MASTER KEY
GO
USE [master]
GO
DROP DATABASE [EncryptTest]
GO



 

Jul 13, 2011

SSIS Transform – The Pivot Transform

Pivoting is a common business practice to gain a better visualization of company performance.  Basically the purpose of pivoting is to changing rows into columns.  So if you want to display sales across all months you would use pivoting to turn a single date column with the month into 12 columns with all the months listed.  You can accomplish this in TSQL or using the Pivot Transform in SSIS.
When you first try using the Pivot Transform you may be a little intimidated.  This transform is not as easy to configure as many of the other Data Flow Transforms.  It sends you straight to an Advanced Editor and you can't just click a couple boxes to complete it's configuration. 
My goal is to hopefully demystify using the Pivot Transform so those who have a real need to pivot data can accomplish that inside a SSIS package. Here, for this example we will be using the AdventureWorksDW2008 database.
Example Overview

 


  •   Use an OLE DB Source to bring in data from AdventureWorksDW2008 database
  •   Data Viewer between source and Pivot Transform to see data before pivot.
  •   Pivot Transform to pivot on day of week column
  •   Another Data Viewer to see data after Pivot Transform has perform it's operation
  •   Union All is used just to test and not actually send data anywhere.  Just a trash destination.
Step 1: Configure Source

 

  •   Use the following query to return the result set used for this demo:
SELECT p.EnglishProductName AS ProductName,
SUM(f.OrderQuantity) AS OrderQuantity,
d.EnglishDayNameOfWeek AS DayofWeek
FROM FactInternetSales f INNER JOIN
DimProduct p ON f.ProductKey = f.ProductKey INNER JOIN
DimDate d ON f.OrderDateKey = d.DateKey
GROUP BY p.EnglishProductName, d.EnglishDayNameOfWeek, d.DayNumberOfWeek
ORDER BY p.EnglishProductName, d.DayNumberOfWeek

Checkpoints in SSIS Packages

Checkpoints are a great tool in SSIS that many developers go years without even experimenting with. I hope to enlighten you on what Checkpoints are and why it is beneficial to use them. Also, I will walk you through a basic example package where they have been implemented.
What does it do?
With Checkpoints enabled on a package it will save the state of the package as it moves through each step or task and place it in a XML file upon failure of the package. If your package does fail you can correct the problem in your package and rerun from the point of the tasks that did not successfully run the first time. Once the package completes successfully the file is no longer needed and automatically discarded.
How does this benefit you?
Just imagine your package is loading a table with 10 million records. Your package passes the Data Flow that performs this huge load without any problem (Other than the fact that it took two hours to load). The next task in your package is a Send Mail Task and for some reason fails.
You correct the problem in the Send Mail Task, but without using Checkpoints your package would still have to run that Data Flow that loads the 10 million records again (taking another two hours) even though you’ve already done it once. If you had enable Checkpoints on this package you could simply correct the problem in the Send Mail Task and then run the package again starting at the Send Mail Task. Sounds great right?
How do I configure it?
This example will run you through very basic package using Checkpoints.
Example Overview

 

  •  Use Three Execute SQL Task using the AdventureWorks2009 (It can really be any database for this example) database as a connection manager.
  •  Configure the package to handle Checkpoints
  •  Configure the individual tasks to handle Checkpoints
Step 1: Configure Execute SQL Tasks
  •  Drag three Execute SQL Tasks on your Control Flow.
  •  Use any database for the Connection property on all three tasks
  •  Configure Execute SQL Task SQLStatement property: Select 1
  •  Configure Execute SQL Task 1 SQLStatement property: Select A (Set to intentionally fail)
  •  Configure Execute SQL Task 2 SQLStatement property: Select 1

SSRS Report to run SQL Agent Jobs



Data Warehouse latency is often a complaint ,generally heard, from end users when trying to access data via either Reporting Services reports or Excel. 
To create a report that could kick off the SQL Agent job that processed Data Warehouse load and Cube update.  It is a pretty simple report to create.  Here are the steps :
Step OneCreate a Data Source that points to MSDB on the server that the SQL Agent job that you want to run is located.
Step TwoCreate a DataSet that runs the system stored procedure sp_start_job with the name of the job.






Step Three
Add some text!  Let the user know what’s going on after they click on the report otherwise it will just show a blank report.  Drag a textbox over and add the appropriate text.


Deploy the report and test!
There are some circumstances where you would not want to use this method:
·         Running the job in the middle of the day could severely cripple a transactional system that the Data Warehouse load pulls from.
·         The job takes longer than just a few minutes to process.  Remember you are trying to improve latency.  You don’t want to expose a poorly performing load process (even if the performance time is due to the size of the load not bad code)
·         You haven’t trained your end users in what the report does.  You don’t want end users clicking this report over and over again because it is running a major process

Performance Tuning OLAP Reports

Reports that use an Analysis Services cube as a data source can often have performance problems during pre-execution.  Before you even see the classic Report is being generated sign the report is slow to start.

The most common reason I’ve found for this performance lag has to do with parameters that are filtering the main dataset.  For example, you have a report that uses a sales cube and you want the user to be able to select the date range that the report will return results for.  This would mean you would add a filter to the dataset using a date dimension and check the box to make it a parameter.
The reason this can be a performance problem is because you’ve now added a parameter that includes every date that exist in your date dimension.  So before the report can even begin to generate it has to populate all the records in the parameter first.  Depending on how many dates you’re storing in the dimension this could be a major issue.  Not to mention if you are using cascading parameters that depend on the previous parameters results. 
It’s likely the end users of the report aren’t interested in seeing 70 years worth of data.  It’s much more likely that the user would want a rolling years worth of dates to choose from.  So here’s the solution:
Step One
Show Hidden Datasets by right clicking on the report Data Source and clicking Show Hidden Datasets.  When you check to include parameters on a dataset it creates hidden datasets behind the scenes. 
 
Step Two
Open the properties to the newly shown dataset that is used for the date parameter and select Query Designer.  Add the following MDX where clause to the query.  This query may vary greatly depending on the design of your cube.  This will just give you a starting point.
 
Now when I view this report I will only have the past years worth of dates available in the date parameter.  Lag(366) is bringing back all dates more than a year old to Lag(0), which is the current date.  Again this could be very different for you depending on how the date dimension is setup in your cube

After installing SP2 on SQL Server 2005 x64, when trying to open existing SSIS package or try to add data component to a new SSIS package, is giving error saying that designer could not be initialised.

After installing SP2 on SQL Server 2005 x64, when trying to open an existing SSIS package or trying to add data component to a new SSIS package, you may receive an error saying that the designer could not be initialised.
This problem occurs because the installer unregisters the 32-bit Dts.dll file when the installer installs the 64-bit Dts.dll file. The 64-bit Dts.dll file is installed together with SQL Server 2005 SP2. Additionally, the 64-bit Dts.dll file may be installed together with any SSIS hotfix package that contains the 64-bit Dts.dll file.
Solution 1: Un-install SQL Server 2005 client tools and reinstall it again.
Solution 2: Register dts.dll
%windir%\syswow64\regsvr32 "%ProgramFiles(x86)%\Microsoft SQL Server\90\dts\binn\dts.dll"

Convert Number to Words in SSRS

A question that is often asked is how to convert a number to words in SSRS. For example 1 => One.
Let's take a look at an example:
SELECT Sales.SalesPerson.SalesPersonID AS SalesPerson,
SUM(Sales.SalesOrderDetail.OrderQty * Sales.SalesOrderDetail.UnitPrice) AS Amount
FROM Sales.SalesOrderDetail
INNER JOIN Sales.SalesOrderHeader ON Sales.SalesOrderDetail.SalesOrderID = Sales.SalesOrderHeader.SalesOrderID
INNER JOIN Sales.SalesPerson ON Sales.SalesOrderHeader.SalesPersonID = Sales.SalesPerson.SalesPersonID
GROUP BY Sales.SalesPerson.SalesPersonID
For the sake of example, let us assume that we need to display sales person id along with the sales amount and the amount in words.
Listed below are the basic steps to create the report:
1. Create a SSRS project
2. Add new report the project
3. Add a data source in which database is pointed to adventureworks
4. Create a data set with above querys
5. Drag and drop a table to layout from the toolbox and drag and drop the fields to the table
The next step is to create a function to convert the numeric values into words.
 Go to Report menu option and select report properties. Select the Code tab in the dialog box and copy and paste the following code.

How to schedule SSAS Database backup?

SQL Server Analysis Services (SSAS) database backups can be taken using SQL Server Management Studio (SSMS). However, you cannot schedule a SSAS database backup from the SSAS backup option. To achieve this, you can create SQL Server Agent Job and schedule that job as per your requirement.
1.  Right click the Job folder of the SQL Agent node in SSMS.
2.  Select the New Job… option.
3.  Enter a Job name to identify the job
4.  Go to Steps page
5.  Click New… button
6.  After entering the Step Name, select SQL Server Analysis Services Command as the job type.
7.  Enter the server name.
8.  Enter the following command. You will need to change the SSAS database name and the backup name.
<Backup xmlns="http://schemas.microsoft.com/analysisservices/2003/engine">
  <Object>
    <DatabaseID>AdventureWorksDW</DatabaseID>
  </Object>
  <File>AdventureWorksDW.abf</File>
  <AllowOverwrite>true</AllowOverwrite>
  <Password>password</Password>
</Backup>
9.  Click the OK button.
10. Go to the Schedule page and create a schedule as per your requirements.

How to Display Server Name or IP Address in a Reporting Services Report

It is possible that users can receive miss-leading data when you are running several reporting severs. You may have a test and a production environment and you do not know from which server a report has been generated.
An obvious way you can tackle this issue is by displaying the report server name in the report. Unfortunately, there is no direct function or a method to retrieve the server name in Reporting Services. However there is a workaround.
In Reporting Services there is a global parameter called ReportServerUrl which will display the report URL.  The initial part of this value is taken from the RSReportServer.config file in the C:\Program Files\Microsoft SQL Server\MSSQL.#\Reporting Services\ReportServer folder. In this file, there is tab called URlRoot.  It can be either <UrlRoot>http://<IPAddress>/reportserver</UrlRoot> or <UrlRoot>http://<Server Name>/reportserver</UrlRoot>.  You need to find out a way to extract IPAddress or ServerName which you can do by following expression.
Mid(Globals!ReportServerUrl,8,InStr(9,Globals!ReportServerUrl,"/") - 8)

What this expression does is, is it will extract the start from the next character position of // to the next character position of /, which will be either the server name or the IP address of the server.

Users are able to connect to report manager but not able to connect to report server.

You need to register IIS before installing SQL Server Reporting Services. However, there can be situations where you need to re-register it.  In order to re-register ISI follow the steps below:
1. Open the Command Prompt
2. Change directory as follows:
For 32-bit machines change to:
<WindowsDir>\Microsoft.NET\Framework\<version number>\
For 64-bit machines change to:
<WindowsDir\Microsoft.NET\Framework64\<version number>\
3. Then run the command ‘aspnet_regiis.exe -i’ and press enter

SQL Server Reporting Server (SSRS) service is failing to start

After a server reboots the SQL Server Reporting Server (SSRS) service may fail to start and the following errors are logged in the event log.
The SQL Server Reporting Services (MSSQLSERVER) service failed to start due to the following error:
The service did not respond to the start or control request in a timely fashion.
Timeout (30000 milliseconds) waiting for the SQL Server Reporting Services (MSSQLSERVER) service to connect.
This issue may occur if the service times out before it starts successfully. This issue is more likely to occur if your computer is heavily loaded.
To resolve this issue, increase the time-out value for service startup process. When you increase this value, the service has more time to load when the computer starts. To increase the service startup time, create the following registry entry:
Subkey: HKEY_LOCAL_MACHINE\SYSTEM\CurrentControlSet\Control
Name: ServicesPipeTimeout
Type: REG_DWORD
Data: The number of milliseconds that you want to give the services to start in
Typically, a data value of 35,000 is sufficient to keep the service from timing out. However, you can reduce or increase this value according to your specific startup requirements. For example, to use a time-out value of 60 seconds, assign a data value of 60,000 to the ServicesPipeTimeout registry entry. A larger data value does not decrease your computer's performance. To create this registry entry, follow these steps:
1. Click Start, click Run, type regedit, and then click OK.
2. Locate and then click the following registry subkey:
HKEY_LOCAL_MACHINE\SYSTEM\CurrentControlSet\Control
3. Right-click Control, point to New, and then click DWORD Value.
4. In the New Value #1 box, type ServicesPipeTimeout, and then press ENTER.
5. Right-click ServicesPipeTimeout, and then click Modify.
6. Click Decimal, type the number of milliseconds that you want to wait until the service times out, and then click OK.
For example, to wait 60 seconds before the service times out, type 60000.

How to unzip a File in SSIS?

For SSIS packages, most of the times you will get zip, rar or tar etc files and need to extract them during the extraction operation in SSIS. Let us see how we can do this directly from SSIS.
You can use the Execute Process Task in the Control Flow task. In BIDS Simply drag and drop an Execute Process task to the control flow and configure as shown below:


In the Execute Process, you need to perform three configurations:

1.    Executable:  This is the path of the application you are going to use. This specific example has used Winrar.
2.    Arguments: In this you need to supply the arguments to extract the zipped files. In the particular example e stands for Extract files to current directory. Then the full path name of the zipped file. –o+ is a parameter to say overwrite files if they exist.
3.    Working Directory: This is the current directory for the process. In the given example test1.rar will be extracted to the directory given in the Working Directory attribute.
 However, Most of the time there will be several zip files to extract and the path may not be defined this clearly. For this you can us the For Each container and include the process task inside it as shown below:

In the For Each loop container, the enumerator is Foreach File Enumerator which is the default enumerator. In the Execute process task all the configurations are the same as above except the arguments were set to "e " +@[User::currentFile] + " -o+". 

Why can't I install multiple instances of SQL Server Integration Services components on the same physical server?

The SQL Server Integration Services (SSIS) component of SQL Server 2005 runs as a service, and only one instance of SSIS can run on a single physical server at a time. If you are working with one instance of SQL Sever on a single physical box, the SSIS service will by default use the default instance to store packages (if you chose to store them within SQL sever). SSIS is shared by all instances of SQL Server running on the same physical server, but no matter which instance you use to create a SSIS package, the package will, by default, be stored by the default instance of SQL Server.
If you don’t have a default instance, or if out want to use a different SQL Server instance to store your packages on the same physical server, you must alter the SSIS configuration file to change the ServerName attribute.
This file is located in the folder, Program Files\Microsoft SQL Server\90\DTS\Binn, with the file name of MsDtsSrvr.ini.xml, and it looks like what you see below.

<?xml version="1.0" encoding="utf-8"?>
<DtsServiceConfiguration xmlns:xsd="
http://www.w3.org/2001/XMLSchema" xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance">
  <StopExecutingPackagesOnShutdown>true</StopExecutingPackagesOnShutdown>
  <TopLevelFolders>
    <Folder xsi:type="SqlServerFolder">
      <Name>MSDB</Name>
      <ServerName>.</ServerName>
    </Folder>
    <Folder xsi:type="FileSystemFolder">
      <Name>File System</Name>
      <StorePath>..\Packages</StorePath>
    </Folder>
  </TopLevelFolders> 
</DtsServiceConfiguration>
While SSIS does not support multiple instances on a single physical server, it is aware of all SQL Server instances and can be configured to reference any particular instance by modifying the above configuration file.

Jul 11, 2011

Create INDEX

Create Relational Index
CREATE [ UNIQUE ] [ CLUSTERED | NONCLUSTERED ] INDEX index_name
ON <object> ( column [ ASC | DESC ] [ ,...n ] )
[ INCLUDE ( column_name [ ,...n ] ) ]
[ WITH ( <relational_index_option> [ ,...n ] ) ]
[ ON { partition_scheme_name ( column_name )
| filegroup_name
| default
}
]
[ ; ]

<object> ::=
{
[ database_name. [ schema_name ] . | schema_name. ]
table_or_view_name
}

<relational_index_option> ::=
{
PAD_INDEX = { ON | OFF }
| FILLFACTOR = fillfactor
| SORT_IN_TEMPDB = { ON | OFF }
| IGNORE_DUP_KEY = { ON | OFF }
| STATISTICS_NORECOMPUTE = { ON | OFF }
| DROP_EXISTING = { ON | OFF }
| ONLINE = { ON | OFF }
| ALLOW_ROW_LOCKS = { ON | OFF }
| ALLOW_PAGE_LOCKS = { ON | OFF }
| MAXDOP = max_degree_of_parallelism
}

Create XML Index
CREATE [ PRIMARY ] XML INDEX index_name
ON <object> ( xml_column_name )
[ USING XML INDEX xml_index_name
[ FOR { VALUE | PATH | PROPERTY } ] ]
[ WITH ( <xml_index_option> [ ,...n ] ) ]
[ ; ]

<object> ::=
{
[ database_name. [ schema_name ] . | schema_name. ]
table_name
}

<xml_index_option> ::=
{
PAD_INDEX = { ON | OFF }
| FILLFACTOR = fillfactor
| SORT_IN_TEMPDB = { ON | OFF }
| STATISTICS_NORECOMPUTE = { ON | OFF }
| DROP_EXISTING = { ON | OFF }
| ALLOW_ROW_LOCKS = { ON | OFF }
| ALLOW_PAGE_LOCKS = { ON | OFF }
| MAXDOP = max_degree_of_parallelism
}

Backward Compatible Relational Index
Important The backward compatible relational index syntax structure will be removed in a future version of SQL Server. Avoid using this syntax structure in new development work, and plan to modify applications that currently use the feature. Use the syntax structure specified in <relational_index_option> instead.

CREATE [ UNIQUE ] [ CLUSTERED | NONCLUSTERED ] INDEX index_name
ON <object> ( column_name [ ASC | DESC ] [ ,...n ] )
[ WITH <backward_compatible_index_option> [ ,...n ] ]
[ ON { filegroup_name | "default" } ]

<object> ::=
{
[ database_name. [ owner_name ] . | owner_name. ]
table_or_view_name
}

<backward_compatible_index_option> ::=
{
PAD_INDEX
| FILLFACTOR = fillfactor
| SORT_IN_TEMPDB
| IGNORE_DUP_KEY
| STATISTICS_NORECOMPUTE
| DROP_EXISTING
}

Jul 8, 2011

To remove duplicate records from a table using SQL?

There may be quite a few possible ways of doing this, but the two most common ways are:-

Simpler and Faster approach - In this approach we simply create anothet table having only the distinct rows of the original table, drop the original table, and finally rename the new table to the original table. Voila! We're done. But, do remember that dropping a table will drop all the indexes and priviledges as well. So, you'll need to create them again.

CREATE TABLE NEW_TABLE AS SELECT DISTINCT * FROM ORIGINAL_TABLE;
DROP TABLE ORIGINAL_TABLE;
RENAME NEW_TABLE TO ORIGINAL_TABLE;

...create indexes/privileges on ORIGINAL_TABLE now...

The standard ROWID approach - it's the same approach where we simply compare the ROWID of the records having the same key values (duplicate records) and select only one of the duplicate rows - the one having either the min or the max ROWID. Don't worry, these ROWIDs are system generated and will never be duplicate, so you won't be having more than one min (or max).

DELETE FROM ORIGINAL_TABLE T1 WHERE ROWID > (SELECT MIN(ROWID) FROM ORIGINAL_TABLE T2WHERE T1.KEY = T2.KEY);

OR

DELETE FROM ORIGINAL_TABLE T1 WHERE ROWID < (SELECT MAX(ROWID) FROM ORIGINAL_TABLE T2WHERE T1.KEY = T2.KEY);

Here KEY represents the set of columns based on which we're deciding the duplicates.

Finding Duplicates with SQL

Suppose you want to find all email addresses in a table that exist more than once:
SELECT email, 
 COUNT(email) AS NumOccurrences
FROM users
GROUP BY email
HAVING ( COUNT(email) > 1 )

You could also use this technique to find rows that occur exactly once:
SELECT email
FROM users
GROUP BY email
HAVING ( COUNT(email) = 1 )