Life of being a DBA

There has been enough said about being a DBA in a critical production environment in many websites, blogs and interviews. But today was the day that I had a taste of it. I had to join a web meeting to do a production migration early morning around 5:45AM. And all was expected to go well and the task to be completed within 4 hours. This meant that the expected time to complete the task was 4 hours which included testing the application after migration.

Unfortunate enough, the migration went well through all the steps and I was expecting a HAPPY SATURDAY as usual until we realized that we had a problem starting one of our services. That was a simple service that should have started without any issues from all sides. But things were going the other way what seemed to be simple became one of the complex tasks to handle and was bugging me for almost 3 hours.
I had to make my mind work at rapid pace to get things faster but with no luck. At last all I had to do to bring the service up and running was one of the funniest things. I had to reboot the server to have it running. Yes the simplest MicroSoft solution got rid of what seemed to be the complex issue.

I took almost 3 hours for us to take such a decision. Now I realize that being a DBA is not the easiest tasks that I could consider moving forward. I had literally gone through 4 hours for a simple task that got mine.

Life of being a DBA is something that people should love as if it’s part of their wholesome life. I love being a DBA in any cause and still love it more, more and more to go.

Data Partitioning

Introduction:
This article takes introduces you to the concept of data partitioning in SQL server 2005. It also gives you an example of how to start from the scratch and also how to partition an already existing table.

Data Partitioning:
Partitioning is a very powerful feature in SQL Server 2005. It means that you can horizontally partition the data in your table, thus deciding in which filegroup each row must be placed.
This allows you to operate on a partition even with performance critical operation, such as reindexing, without affecting the others. In addition, during restore, as soon a partition is available; all the data in that partition are available for quering, even if the restore is not yet fully completed.

Illustration of Data Partitioning:
Before proceeding with the steps that are given below, create a database called datapart in your server.

1. Create the file groups in which you are going to place the data
Alter database datapart add filegroup data_part
Alter database datapart add filegroup data_part1

2. Create the files in which you are going to place the data and assign them to the filegroups
Alter database datapart add file (name = 'part1',
filename = 'f:\sugesh\data\part1.ndf') to filegroup data_part
Alter database datapart add file (name = 'part2',
filename = 'f:\sugesh\data\part2.ndf') to filegroup data_part1

3. Create a partition function that partitions the table data in interval of 500 which means that the values below 500 will be placed in partition 1 and others in partition 2.
create partition function part(int) as range right for values (500)

4. Create a partition scheme so that you can associate the partition function to the filegroups
create partition scheme part as partition part to (data_part, data_part1)

5. Create a table to test our data partitioning
create table datapart(name varchar(10), part int) on part(part)

6. Insert values into the tables to the right and left of your partition value.
insert into datapart values('sugesh',0)
insert into datapart values('kumar',499)
insert into datapart values('sugesh',500)
insert into datapart values('sugesh',10000)

7. Select the values from the table along with the partition file id to find where the data has been placed and confirm if the data has been placed as per data partition defined.
select *, $partition.part(part) from datapart

The values 1 and 2 that you see in the last column in the figure given above is the partition id field and as you see all values below 500 are placed in partition 1 and others in partition 2.
Now, what’s up for us next? I have been asked by some of my colleagues, friends and few others that, is it possible to partition a table that already has rows in it. Yes of course, given below are the steps that I have used to partition the data that we have in a table with the data partition feature in SQL Server 2005.

1. Create a table without specifying any data partition in the table.
create table datapart1(name varchar(10), part int) on part(part)

2. Insert values into your table such that the values are split across your partitions.
insert into datapart1 values('sugesh',499)
insert into datapart1 values('sugesh',999)

3. Create a partition function that partitions the table data in interval of 500 which means that the values below 500 will be placed in partition 1 and others in partition 2.
create partition function part(int) as range right for values (500)

4. Create a partition scheme so that you can associate the partition function to the filegroups
Create partition scheme part as partition part to (data_part, data_part1)

5. Insert all the value that you have in the table into a backup table
select * into datapart1_2007jun25 from datapart1

6. Drop and re-create the table specifying the data partitioning that needs to be used.
drop table datapart1
go
create table datapart1(name varchar(10), part int) on part(part)

7. insert the values into the table that you have created with data partitioning from the backup table that has the original values
insert into datapart1
select * from datapart1_2007jun25

8. Select the values from the table along with the partition file id to find where the data has been placed and confirm if the data has been placed as per data partition defined.
select *, $partition.part(part) from datapart1

Alternate Suggestions:
Though there are other options of using the alter table…. Split partition, alter table…..switch partition they can’t be effectively used for a table that already has large number of rows in it with clustered and non-clustered indexes. This solution of mine worked a way to get them around.

Conclusion:
I hope you have enjoyed learning the way of partitioning the data in SQL Server 2005. The simplest way to partition the data and improve the performance of reads and writes for larges tables. Your opinions, views and suggestions are always welcome.

Clustering SQL Server Integration Services

Until the release of SQL Server 2005 SP2, Clustering SQL Server Integration Services was a bit complex task But Microsoft made it a very simple task with the release of Service Pack 2. Given below are the steps to be followed to cluster SSIS.

1. Install SSIS in all node of cluster.
2. Move to %Program Files%\Microsoft SQL Server\90\DTS\Binn
3. Look for the file MsDtsSrvr.ini.xml
4. Look up for the Server Name class, Change it to point to the SQL Server Virtual Name
5. Restart SQL Server Integration services

SQL Server 2008 RTM

Microsoft has come up with SQL Server 2008 release. SQL Server 2008 RTM was released on Aug 6th 2008 and is available for all customers and users.

http://www.microsoft.com/Presspass/press/2008/aug08/08-06SQLServer2008PR.mspx

Unusual Backup Error in SQL Server

You might be wondering what is happening in your SQL Server when you see the error message given below. Its all simple, you need not panic.
Error Number: 18272
Error Message: I/O error on backup or restore restart-checkpoint file '%1'. Operating system error %2. The statement is proceeding but is non-restartable.
This happens when SQL server is not able to find the default backup directory. SQL Server needs to create a checkpoint file when a BACKUP or RESTORE operation happens. This happens when the RESTART option is specified. WITH RESTART tells SQL Server to start from the point where the operation was last aborted. When SQL Server searches for the file and its not available then this error is thrown.
How to avoid this error:

1. Check the default backup directory for the SQL Server and see if exists, if not create it.
2. Verify if the backup directory has enough space in it.
3. Verify if SQL Server service account has permissions to read and write into that directory.

Introduction to Notification Services

I have been a Core Server Side SQL Server DBA for more than half a decade. This was the first application side task that I handled from the SQL Server front. I would like to thank my friends Chakravarthy and Poornima for giving me this wonderful opportunity to work on Notification Services. I don’t claim that this document would give you all that are needed to work with Notification Services. It just gives you a brief idea of what it is and how you can create a simple notification service application.

And before going into the discussion, let me say that Notification services is removed from SQL Server 2008 and though Microsoft Supports it in SQL 2005 it comes under the Support of Reporting Services feature and is not taken as an independent feature.

Microsoft Notification Services can be used to generate notifications and send it to the users. Notifications sent can be either event driven or scheduled. All subscribers who have subscribed for the subscription will be notified on the event.

Key Terms in Notification Services:

Instance Configuration File (ICF): This file contains parameter details for the Notification service instance. This is the primary file that is used to create the notification services.

Application Definition File (ACF): This File contains details of the application settings like the events, rules, subscribers, subscriptions, notification to be generated, etc.

Both of these files will be dealt in detail in the coming sections.

Subscriber: A person or application that subscribers to receive notifications.

Subscription: It’s the information that needs to be sent to the subscribers.

Event: It’s the piece of information that has to be captured and sent to the subscribers.

Notification: Notification is the message that contains the information related to the subscription.

Generator: It governs the rule processing for the notification services.

Distributor: It governs the notification formatting and delivery. It looks for notifications on a configured interval and sends the notifications to the subscribers in the event of an occurrence of the same.

Subscription Device: A subscription device can be anything like a e-mail, Phone, Message, File, etc

Notification Service Architecture:

Below is the simple architecture of how Notification service.

1. Subscribers subscribe to events.
2. Events are captured by the application.
3. Service matches subscriptions to the events.
4. Notification service generates a notification.
5. Notification Service formats and sends the notification to the subscribers.

Detailed Architecture of Notification Services.

1. Notification Services stores subscriber and subscription information on databases that are created for it. (A Notification service creates 2 databases when services are installed, we will be covering those in detail when we create the service). We can create a subscription management application using a programming language to manage subscriber and subscriptions. This provides the better subscription management for the subscriber (end users) to add/edit/view their subscription data.
2. Using the event providers that are defined in the Definition File, NS mines the database for events and stores the event data in the application database. There are various event providers that are available in NS. This event provider monitors a directory for XML event data. Using this event provider, you can drop XML event files in the directory, and the event provider reads the XML events and submits them to the application database. Another Advantage of Event providers is they can be run by the event provider host component, or they can run independently of Notification Services which gives it more robustness.
3. SQL Queries are written to get the necessary information passed to the notification services. Then generator matches the subscriptions and the events and generates notifications. According to the need of the application the administrator can configure how frequent this generator should run. This makes the application more efficient in time sensitive notifications.
4. The distributor distributes the notification after proper formatting using the content formatter described.

Creating a Sample Notification Service Application:
We are going to create a sample notification service application once you create the application you will find the following.
1. NS$instancename Services in the services applet.
2. InstanceNameApplicationName database in SQL Server.
3. InstanceNameNSMain database in SQL Server.

Steps to be followed:
1. Create an ICF file.
2. Create an ADF File.
3. Login to SQL Server management Studio, Right click Notification Services to create a new notification services instance.
4. Browse to the click ICF file and click OK
5. If it succeeds then you will see the above service and database created in the server.
6. Right click the Notification Service Instance Name, Goto Tasks->Register.
7. Check the “Create Windows Service” giving proper credentials and click OK.
8. Confirm this succeeds.
9. Right click Instance and select enable.
10. Right click Instance and select Start.

This starts SQL Server Notification Service and your notification service application starts to collect the events defined in the ADF file. I will cover how to use Notification Service to create and send notifications to subscribers in my next article on Notification Services.