Tag Archives: SQL Server

SQL Server Integration Services – Packages, Datasources and Connections

In this article we will see how we can create a SSIS project via visual studio and then create a package and linking the packages to data sources along with connections. For this demo i will be using SQL Server 2008. The steps remain same for SQL Server 2008 R2 and SQL Server 2012.

creating a SSIS project…

Step 1: Open SQL Server Business Intelligence Development Studio(BIDS). Go to File -> New -> Project
IS1

 

 

 

 

 

 

 

Step 2 : ON the new Project dialog box choose Integration Services Project. Give the Project a name and location as shown below. Click on OK. The  Project will appear on the Solution explorer. By default a package is already created named Package.dtsx
IS2IS3

Step 3: let us rename this package as MyPackage1. Right click on the Package and select rename. Change the name to MyPackage.dtsx. Click Yes on the confirmation dialog box.
IS4IS5
Step 4: We will create two more packages named DC.dtsx and DP.dtsx. Right click on SSIS package on solution explorer and click on New SSIS Package. A package would be automatically added with default name Package(n).dtsx. We will rename the package as DC.dtsx. Repeat the same steps and create another package named DP.dtsx. Click on save all button to save all the changes made.
IS6 IS7So this is how we will create a SSIS project with packages. In the next section we will create data sources for the project.

 creating data sources..

Step 1: Open the Project in BIDS. Right click on Data Sources and select New Data Sources. On the Data source wizard click on new and select a server name and database name. In this case we will choose AdventureWorks as our database.
IS8 IS9 IS10Step 2: Click on OK. Click Next and click on Finish. The new data source will now appear under Data Sources
IS11 IS12 In the next section we will create new package connections from the Data Sources we created.

Creating Connections

Step1 : Click any one of the packages. Right Click on the connection managers window and select New Connection from Data Source. Select the Data source we created name AdventureWorks. Click on OK.
IS13 IS14The new connection connection will now appear in the connection managers window for the package. We need to create the same for the rest of the two packages.
IS15

 

Extended Events in SQL Server 2012 – Part 1

In SQL Server 2008, Microsoft introduced a feature called Extended Events. Extended Events helped in collecting and analyzing event driven data about the SQL Server instance. Extended Events introduced lesser load than trace or profiler events on the server. But in SQL Server 2008 there was no GUI that allowed direct communication with the events. Complex T-SQL had to be written to fetch the information which was returned in xml format.

In SQL Server 2012, MS has introduced a GUI in the Management Studio (SSMS)  that allows working with Extended Events and viewing event related data a much simpler process. In this article we will understand and see step by step how we can create and manage Extended events via the GUI that is built in the SSMS.

Step 1: Connect to SQL Server 2012 instance -> Open Management Tab -> Open Extended Events -> Right Click on Sessions -> New Session
Ee1
Step 2 -> In the new session dialog box type in a name of the session. In this case we will use ‘Session1’. For now we will not choose any template.
EE2

Step 3 -> Click on Events tab -> On the below window you will find many Events listed from which we need to pick an event or multiple events that we want to capture. Once selected we need to click on the button to add it. For this article we will use the Events
a) query_post_execution_showplan and b) sql_statement_completed
EE3AEE4Step 4 -> Click on Data Storage -> Here you specify how you can capture data so you can view it later. Here we will save it as Event File. Mention the path where we would save the file -> Click on OK.
EE5Step 5 -> Go to management studio -> Under sessions you will find New session listed. It would be in a stopped state as this is the default behavior. Right Click on the session and click on ‘Start Session
EE6EE7EE8Step 6 -> Now right click on the created session and select ‘Watch Live Data’. If there is no activity on the instance you will not see any data.
EE9EE10Step 7 -> Let us fire few queries on the AdventureWorks2012 database and watch the window. Following are queries that we would run on the database for testing. After running the queries go back and open the ‘Watch Live Data’ window again. Click on the Event data and observe the below.

USE AdventureWorks2012
go
SELECT * FROM Production.Product pp
WHERE pp.ProductID > 40
go
SELECT * FROM Production.BillOfMaterials Pb
WHERE pb.BillOfMaterialsID > 100
go

EE11EE12EE13In this article we have seen how we can start a new event trace from SSMS. In my next article we will explore more into the new GUI feature that MS has added to SQL Server 2012.


Resources in a SQL Server Failover Cluster

In this article I will discuss about the resources that are part of a SQL Server failover cluster.

A SQL Server failover cluster instance consists of 2 kinds of resources.

1)Local Resources: Local resources are those components that are installed on each possible owner node. They remain on that node and do not move over during a failover.
2)The shared resources: Those resources that fail over between the owner nodes.

Let us now go through the resources that are part of a resource group in a SQL Server failover cluster.
a) SQL Server Database Engine\Agent service : The database engine and SQL Server agent resources are always part of any SQL Server failover cluster’s resource group where the database engine has been installed. If SQL server analysis service is installed, then it will be a part of the resource group. From an availability and performance perspective it is better if both are installed on separate resource groups.

b) IP Address and Network name: The IP address and network name component ensures an interface that clients can use to connect to the SQL Server instance  irrespective of the fact that which node is currently serving the cluster. During a failover event,  the network name and IP addresses is registered to redirect to the new node that is serving the SQL Server failover cluster instance. To the clients that are connecting to the SQL Server, this is a absolutely transparent process.

c) Shared disks:A SQL Server shared disk contains all of the system and user data including databases, logs for the SQL Server failover cluster instance. When a failover occurs, the disks are mounted on the new node. When SQL Server instance is started on the new node, it goes through recovery as part of the SQL Server startup and maintains access to the same database files that existed when it was running on the previous node.The shared disks are the single point of failure for a failover cluster instance. Windows Server and SQL Server failover clustering provides redundancy for machines, operating systems, and SQL Server binaries, but requires reliable storage to assure availability of the shared storage.

How to restore a table from a Full backup using Quest Litespeed without restoring the entire database

In this article I will demonstrate how we can restore a table from a SQL Server database backup file which has been taken by Quest Litespeed tool. This feature called Object level recovery in the tool is extremely helpful as you need not restore the entire database and yet retrieve data from the backup. Follow the below mentioned steps and understand how this can be accomplished. I will be using a trial copy of Quest Litespeed along with SQL server 2012 and the database we would use is Adventureworks2012. However you can use SQL server 2008 also.

Step 1: Create a database and take a full backup of the database
Let us create a database for this purpose

CREATE DATABASE MyDB
GO
USE mydb
GO
CREATE TABLE Mytable1
( id INT,FNAME NVARCHAR(50) )
GO
INSERT INTO Mytable1 VALUES (1,'Sanchayan')
GO
INSERT INTO Mytable1 VALUES (2,'Abhishek')
go
CREATE TABLE Mytable2
( id INT,LNAME NVARCHAR(50) )
GO
INSERT INTO Mytable2 VALUES (1,'Pandit')
GO
INSERT INTO Mytable2 VALUES (2,'Agrawal')
go

Now take a Full Backup of the database.

exec master.dbo.xp_backup_database
@database = N'Mydb',
@compressionlevel = 3,
@filename = N'D:\MSSQL\SQLBackup\Mydb.bak',
@init = 0,
@OLRMAP = 1--This is important as this marks the backup for object level recovery
GO

Step 2: Now run the following code to find the Tables available in the backup

Use Master
go
exec xp_objectrecovery_viewcontents
@FileName = N'D:\MSSQL\SQLBackup\mydb.bak',
@FileNumber = 1,
@Type = 'All',
@DisableLogProcessing = 1

olr1
Step 3: Now select the table that you wish to restore. In this demo we would restore MyTable2.
We would need the below predefined:
a) The database where we would restore the table.
b) A temp directory location which should be large enough for the table to be restored initially.

Use master
go
exec xp_objectrecovery
@FileName = N'D:\MSSQL\SQLBackup\Mydb.bak',
@FileNumber = 1,
@ObjectName = N'dbo.Mytable2',
@DestinationTable = N'[dbo].[Mytable2]',
@DestinationDatabase = N'MyTempDB',
@DestinationServer = N'GN-A-MOOKBO-7L\sql2012',
@TempDirectory = N'D:\MSSQL\SQLBackup',
@DisableLogProcessing = 1

The output of the above code would be as below
olr2To verify if the data you can query the table that we restored on mytempdb database
olr3I hope this article was helpful in understanding how we can use ‘Object level recovery’ to fetch data from a full backup file that has been taken using Quest Litespeed for SQL Server tool.

Technical reference: http://www.quest.com/litespeed-for-sql-server/

How to increase the number of SQL Server logs

In this article I would like to demonstrate how we can increase the number of logs in SQL Server for the engine. The SQL Server error log contains events and errors related to SQL Server engine and services. You can use this error log to troubleshoot problems related to SQL Server. The default value for the maximum number of log files is 6. However we can increase that. The maximum number of logs can be up to 99.

Let us now see how we can increase the number of SQL Server logs via SSMS:
Step 1: Connect to the SQL Server where you want to perform this
Step 2: Expand the management folder
Step 3: Right Click on SQL Server Logs
Step 4: Click on Configure
EL5Step 5: When you click on configure the ‘Configure SQL Server Error Logs’  box opens. Change the Maximum number of error logs to the desired number.
Step 6: Click on OK.
EL6We can also run the below query to attain the same:

USE [master]
GO
EXEC xp_instance_regwrite N'HKEY_LOCAL_MACHINE',
N'Software\Microsoft\MSSQLServer\MSSQLServer',
N'NumErrorLogs',
REG_DWORD, 14
GO

How to Recycle SQL Server Agent Error Logs

The SQL Server Agent Error Log is a log maintained by SQL Server to record all error messages related to SQL server agent. This record is maintained since the last time the log was initialized or the agent was restarted.
EL1In a highly available OLTP environment the SQL Server runs for a long span of time without restarts. This may cause the logs to to grow by a huge margin and it might be difficult to open the log while troubleshooting an issue.

To counter this situation we can do the following:

1) Run the following SP on a regular basis:

USE msdb
go
EXEC dbo.sp_cycle_agent_errorlog
go

This will re-initialize the current log and start a new log with the current date time stamp
EL2
2) We can also use the ssms to accomplish the same:
a) Right click on the Error logs:
EL3
b) Click on OK
EL4

3) The third way is to create a job and invoke the mentioned stored procedure in step 1 and schedule it on weekly basis.

How to Partition a table in SQL Server

In this article we will see how we can partition a table in a database using T-SQL. We will first understand the basics of table partitioning in SQL Server and then go through the scripts that we will use to build a partitioned table. Let us start with the basics of partitioning.

What is table partitioning in SQL Server?
Partitioning is dividing a large table and its indexes into smaller fragments called partitions.

Some of the Benefits?
Its helps performing maintenance operation fragment by fragment basis rather that performing on the entire table. Apart from that SQL queries can be redirected to proper partitions directly rather than scanning the entire table.SQL queries that are properly filtered on the partition column can perform better by making use of partition elimination and parallelism. Archiving data is another major benefit of partitioning. We will not deep dive in this article on the benefits of partitioning.

Components of Partitioning:
1) Partition Function(PF): PF defines which rows goes into what partition in a partitioned table based on a range.
2) Partition Scheme(PS):The partition scheme defines how partitions will be stored on filegroups. Creating a partition scheme assumes that your database already has filegroups.

Let us now understand with an example how we can partition a table in SQL server.
1) Creating a database:

USE master
GO
CREATE DATABASE PartitionDB
ON PRIMARY (NAME = N'PartitionDB'
,FILENAME = N'D:\MSSQL\Data\PartitionDB.mdf'
,SIZE = 50MB, FILEGROWTH = 150MB)
LOG ON (
NAME = N'PartitionDB_log'
,FILENAME = N'D:\MSSQL\Logs\PartitionDB_log.ldf'
,SIZE = 10MB, FILEGROWTH = 100MB);
GO

2) Adding 4 new filegroups to the PartitionDB database.

ALTER DATABASE PartitionDB ADD FILEGROUP PartitionFG1;
GO
ALTER DATABASE PartitionDB ADD FILEGROUP PartitionFG2;
GO
ALTER DATABASE PartitionDB ADD FILEGROUP PartitionFG3;
GO
ALTER DATABASE PartitionDB ADD FILEGROUP PartitionFG4;
GO

3) Adding files to the database filegroups.

 ALTER DATABASE PartitionDB
    ADD FILE
    (
        NAME = PartitionFile1,
        FILENAME = 'D:\MSSQL\Data\PartitionFile1.ndf',
        SIZE = 20MB, MAXSIZE = 50MB, FILEGROWTH = 5MB
    )
    TO FILEGROUP PartitionFG1;
GO
 ALTER DATABASE PartitionDB
    ADD FILE
    (
        NAME = PartitionFile2,
        FILENAME = 'D:\MSSQL\Data\PartitionFile2.ndf',
        SIZE = 20MB, MAXSIZE = 50MB, FILEGROWTH = 5MB
    )
    TO FILEGROUP PartitionFG2;
GO
 ALTER DATABASE PartitionDB
    ADD FILE
    (
        NAME = PartitionFile3,
        FILENAME = 'D:\MSSQL\Data\PartitionFile3.ndf',
        SIZE = 20MB, MAXSIZE = 50MB, FILEGROWTH = 5MB
    )
    TO FILEGROUP PartitionFG3;
GO

 

 ALTER DATABASE PartitionDB
    ADD FILE
    (
        NAME = PartitionFile4,
        FILENAME = 'D:\MSSQL\Data\PartitionFile4.ndf',
        SIZE = 20MB, MAXSIZE = 50MB, FILEGROWTH = 5MB
    )
    TO FILEGROUP PartitionFG4;
GO

4) Creating a Partition Function(PF):

 CREATE PARTITION FUNCTION PartFunc1 (int)
    AS RANGE LEFT FOR VALUES (10, 20, 30);
GO

5) Creating a Partition Scheme(PS):

CREATE PARTITION SCHEME PartScheme1
    AS PARTITION PartFunc1
    TO (PartitionFG1, PartitionFG2,PartitionFG3,PartitionFG4);
GO

6) Create a Table:–

USE [PartitionDB]
GO
CREATE TABLE PartitionTable
	(
	MyID int NOT NULL,
	MyDate datetime NULL,
	Name varchar(50) NULL
	)  ON PartScheme1(MyID)
GO

7) Create Index on Partitioned Table

USE PartitionDB
go
CREATE UNIQUE CLUSTERED INDEX IX_PartitionTable
ON PartitionTable(MyID)
ON PartScheme1 (MyID);
GO

8) Insert Data into the Table

USE PartitionDB
go
INSERT INTO PartitionTable (MyID, MyDate,name)
VALUES (1,GETDATE(),'Rooney');
INSERT INTO PartitionTable (MyID, MyDate,name)
VALUES (11,GETDATE(),'Van persie');
INSERT INTO PartitionTable (MyID, MyDate,name)
VALUES (22,GETDATE(),'De Gea');
INSERT INTO PartitionTable (MyID, MyDate,name)
VALUES (34,GETDATE(),'Moyes');
GO

9) Verify data in the table

SELECT * FROM dbo.partitiontable

10) Verify Rows Inserted in Partitions

USE PartitionDB
go
SELECT * FROM sys.partitions
WHERE OBJECT_NAME(OBJECT_ID)='partitiontable';
GO

P1
11) Run the below code to see the details of the partitioned table

USE PartitionDB
GO
SELECT
OBJECT_NAME(idx.object_id) AS TableName ,
psh.name AS PartitionSchemeName ,
fnc.name AS PartitionFunctionName,
part.partition_number AS PartitionNumber ,
fg.name AS [Filegroup],
rows AS 'No of Records' ,
CASE boundary_value_on_right WHEN 1 THEN 'less than'
ELSE 'less than or equal to' END AS 'Condition',
value AS 'Range' ,
part.partition_id AS [Partition Id] FROM sys.partitions part
JOIN sys.indexes idx
ON part.object_id = idx.object_id
AND part.index_id = idx.index_id JOIN sys.partition_schemes psh
ON psh.data_space_id = idx.data_space_id
JOIN
sys.partition_functions fnc
ON fnc.function_id = psh.function_id LEFT
JOIN sys.partition_range_values prv
ON fnc.function_id = prv.function_id
AND part.partition_number = prv.boundary_id
JOIN sys.destination_data_spaces dds
ON dds.partition_scheme_id = psh.data_space_id
AND dds.destination_id = part.partition_number
JOIN sys.filegroups fg
ON dds.data_space_id = fg.data_space_id
JOIN (SELECT container_id, sum(total_pages) as total_pages
FROM
sys.allocation_units GROUP BY container_id) AS au
ON au.container_id = part.partition_id JOIN sys.tables t ON
part.object_id = t.object_id WHERE idx.index_id < 2
ORDER BY TableName,part.partition_number;
GO

P2 I hope this article was helpful in understanding how we can create table partitions via T-SQL.

%d bloggers like this: