Script to fix Orphans user in all Databases

SET NOCOUNT ON
create table #fix_orphanusers(dbname varchar(50),username varchar(50),userid uniqueidentifier)

insert into #fix_orphanusers
Exec SP_MSForEachDB 'select ''?'' as DBName, name AS UserName, sid AS UserSIDfrom ?..sysuserswhere issqluser = 1 and(sid is not null and sid <> 0x0)and suser_sname(sid) is nullorder by name'

declare @dbname varchar(50)
declare @username varchar(50)
declare @cmd varchar(4000)

DECLARE fixoprhans_cursor CURSOR FOR
SELECT dbname,username from #fix_orphanusers

open fixoprhans_cursor
fetch next from fixoprhans_cursor into @dbname,@username

while @@fetch_status=0
begin
select @cmd = 'use '+@dbname+char(10)+'go'+char(10)+'sp_change_users_login ''update_one'','+''''+@username+''''+','+''''+@username+''''+char(10)+'go'
exec @cmd
fetch next from fixoprhans_cursor into @dbname,@username
end
close fixoprhans_cursor
deallocate fixoprhans_cursor

drop table #fix_orphanusers

Installing SQL Server 2008

This article introduces how to install SQL Server 2008 RTM. With SQL Server 2008, you have new setup architecture and various options that can be used to easily guide you through for successful installation.

1. Once you click the setup executable you will be taken to the screen shown below. This gives various options and information on SQL Server installation. You can check the SQL Server 2008 software and hardware requirements, books online, documentation, etc

2. Click next and you will be taken to the installation options, you can choose t

he type of installation to be  performed, you will also have options for upgrading

from previous versions of SQL Server.

3. You can also perform edition upgrades, repair on current installation and

 remove nodes from SQL server cluster using the installation setup.

4. Advanced link has options with which you can create configuration file to

 perform a unattended installation and also advanced cluster configurations.

Once you click the installation and perform a new installation, it will ask for the

Processor Type information. Choose the right processor type information and the

setup executable file path.

Click next, you will be taken to SQL Server installation tasks.

Installation checks and installs setup support files if needed.

Click install in Setup Support files screen to proceed with the installation.

Specify the edition which you wish to install and enter the product key in it.

Accept license agreement and click next to proceed.

Select all the features that you would like to install in you server.

Choose the instance name if named instance or leave it as default. Also mention

details of the installation root directory, this is where SQL server binaries will be

installed

Check and review the disk space requirements and click next to proceed if

everything is fine.

Specify Services account for all SQL server services, it’s advised to use domain accounts rather than using local or system accounts.

Select the type of authentication mode for SQL Server security.

Choose the default paths for data and log files for system, user and tempdb databases.

Choose option to enable FILESTREAM (this is to be discussed as a separate topic in other blog entry)

Configure for error and reporting while installation.

Check for the installation rules and if all succeeds then click next to complete installation.

Verify the details given for the SQL server installation, if need be make changes before proceeding to next step.

Check for installation progress.

SQL Server has been installed. Hope you had a wonderful experience installing SQL Server 2008. Lets discuss other features and theories from next blog.

SQL 2005 SP3 released

Microsoft has released Service pack 3 for SQL Server 2005. The downlad details are given below.

http://www.microsoft.com/downloads/details.aspx?familyid=ae7387c3-348c-4faa-8ae5-949fdfbe59c4&displaylang=en&tm

Tools, Tools and More Tools........................

I have been writing articles and scripts in my blog and website frequently. But there has been a lean patch for almost a month now. The reason was i was busy thinking on building on some useful tools that DBA can use on thier routine work life. As a result of it, I have come up with my own tool set and will be launching them one by one ASAP. I am not good in programming but have taken help of my few best friends that I have in that area. So you all can expect more interesting stuff from this blog and my website sooner. And your feedbacks on the tools that we are going to develop is what we expect from you. Many thanks. Keep reading.........

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.

Performance Counter for SQL Server

Few of my friends who wish to do a performance tuning in their servers, they asked me what are the counters that they should check that gave me an idea to prepare this document and publish across.

Disk Related Counters:
Object Name: Physical Disk
Counters:
Current Queue Length: Sustained high queue for a period of time means that the IO subsystem has a problem.

Average Queue Length: If the Average Queue Length for the disk is high more than 2. Then this indicates a potential IO subsystem problem. This would also make the disk sec/read and disk sec/write high.

Disk Sec/Read: This counter should measure not more than 15ms in normal situation. Continuous values of more than 20ms indicate that the disks have a problem.
Under these conditions, consider the following
Move the files to additional drives.
Check for proper indexing on the tables.
Check for the CPU and memory counters to find any other potential bottlenecks.

Disk Sec/Writes: This counter should measure not more than 15ms in normal situation. Continuous values of more than 20ms indicate that the disks have a problem.
Under these conditions, consider the following
Move the files to additional drives.
Check for proper indexing on the tables.
Check for the CPU and memory counters to find any other potential bottlenecks.
Placing transactional log files in separate drives to remove additional pressure on the drives

Memory/Cache:
Object Name: Memory:
Counters:
Page Faults/Sec: Page faults occur when the page that is not requested is not found in the memory. There are 2 types of page faults. Hard page faults and Soft page faults. Hard page faults require a disk access and where as Soft page faults are ones where the page is found elsewhere in the memory. High number of page faults/sec indicates that there is a problem with memory used by SQL Server. Use this counter in correlation with SQL Server:Memory Manager counters to check if there is a memory pressure.

Pages/Sec: This counter is the actual value of the Hard faults and should be used in correlation with Page Faults/Sec and SQL Server memory Manager counters to find the memory pressure.

CPU:
Object Name: Processor
Counters:
%User Time: SQL Server runs in User mode. Privilege mode is used by Operating system to access hardware and memory components. This counter should have a value more than 70%. If it has a value less than that check the %privileged time and %processor time counters to see if there is a problem with the processor.

%Privileged Time: Operating system moves thread to Privileged mode to access services. This counter should value less than 20%.

%Processor Time: This counter tells the percentage of the time that CPU is executing over a period. Ideally it should value less than 70%.

Interrupts/Sec: Interrupts/sec is the average rate, in incidents per second, at which the processor received and serviced hardware interrupts.

System:Object Name: System
Counters:
Processor Queue Length: Is the number threads that are waiting to be processed by the processor. This counter is calculated by the actual value divided by the number of processors. Ideally value should be less than 2.

SQL Server:
Object Name: Access Methods
Counters:
Forwarded Records/Sec: Number of records fetched through forwarded record pointers. Tables with NO clustered index can. If you start out with a short row, and update the row creating a wider row, the row may no longer fit on the data page. A pointer will be put in its place and the row will be forwarded to another page.
Can be avoided by:
Using default values
Using Char instead of Varchar.

Full Scans/Sec: Either table or Index in fully scanned. This could be caused because of wrong or unhelpful indexes.

Page Splits/Sec: Number of page splits occurring as the result of index pages overflowing. Normally, this is associated with leaf pages of clustered indexes and non-clustered indexes. This can be avoided by having proper fill factor.

Object Name: Memory Manager
Counters:
Memory Grants Pending: Memory is needed to process each user request. If enough memory is not available then the user request waits for memory which cause performance hitch in executing the query.
This can be avoided by the following
Adding more memory to the server
Adding memory to SQL Server
Creating proper Indexes

Object Name: Buffer Manager
Counters:

Buffer Cache Hit Ratio: Percentage of time the pages requested are already in memory. It should be ideally more than 99% and if less than 95% indicates that SQL server has no enough memory and adding more memory would be beneficial.

Checkpoints/Sec: Pages written to the disk during the Checkpoint process freeing up SQL cache. Memory pressure is indicated if this counter is more than 300 Secs.

Lazy Writes/Sec: Pages written to the disk during the LazyWriter process freeing up SQL cache. Memory pressure is indicated if this counter is more than 300 Secs.

Page Life Expectancy: This is one counter that I would love to monitor to check the memory pressure. This would give you the time in seconds the data pages reside in the SQL Server cache. If the value is low indicates the following problems.
SQL Cache is cold(Discussed in Page Faults)
Memory Problems
Missing indexes.
If Checkpoints/Sec, Lazywriter/Sec and Page life expectancy together is less than 300 seconds then it indicates the cause of memory pressure and your server needs more memory.

Object Name: Databases
Counters:
Transactions/Sec: Indicates the number of transaction that occur per second in the server.

Object Name: General StatisticsCounters:
User Connections: Number of connections made to the SLQ Server.

Object Name: Latches
Counters:
Average Latch wait Time: Latches are short term light weight synchronization object. Latches are not held for the duration of a transaction. Typical latching operations during row transfers to memory, controlling modifications to row offset table, etc.
A high value here indicates potential problems with IO subsystem or Memory.

Object Name: Locks
Counters
Average Wait Time(ms), Lock Wait time(ms) and Lock waits/Sec: All these counters are related to the locks held by the SQL Server. Transaction should be as short as possible and should hold locks only for a short time without blocking other user queries, A high value here would indicate the following.
Memory Pressure
IO Subsystem problem
Improper indexes
Improper SQL structures
Improper placement of files in the disk subsystem
Incorrect usage of SQL Isolation levels

These are the major counters that I would wish to check. Apart from this you can also check the following counters.

Object Name Counter Name
SQL Statistics Compilations/sec
SQL Statistics Recompilations/sec
SQL Statistics Batch Requests/sec
SQL Server: Buffer Manager Readahead pages/sec

SQL Server Security Bulletin

Introduction:

This document analyzes the information given in the Microsoft bulletin regarding the Security patch that needs to be applied in all SQL Servers version 7 and above.

Scope of the Document:

1. Information regarding the Bulletin
2. Are we affected?
3. Deployment Plans and Guidance
4. Other points to ponder

Bulletin Information:

Bulletin URL: http://www.microsoft.com/technet/security/bulletin/MS08-040.mspx
http://support.microsoft.com/kb/941203#appliesto

Software Affected: SQL Server 7, 2000 and 2005 (32 bit and 64 bit) and (GDR and QFE releases)

The bulletin describes that a security patch has been released for known vulnerabilities in SQL Server and its components. The known vulnerabilities are listed below.

Memory Page Reuse Vulnerability - CVE-2008-0085
Convert Buffer Overrun - CVE-2008-0086
SQL Server Memory Corruption Vulnerability - CVE-2008-0107
SQL Server Buffer Overrun Vulnerability - CVE-2008-0106

Memory Page Reuse Vulnerability:

With this vulnerability, a database user with access can read other user’s data with custom applications as SQL Server does not initialize the memory pages before reallocating memory to users.

Microsoft has not received any information from customers or other users stating that their system is exploited using this vulnerability and information regarding this vulnerability was brought up through responsible disclosure.

Installing this security path creates a new system stored procedure sp_clean_db_free_space that can be used to clear the residual pages in the memory. This procedure cleans up all files in the database.

Mitigation Factor: Only users with database access can exploit this vulnerability.

Any other Workarounds:

Microsoft Suggests to have the configuration option “Common Criteria Compliance” set to 1. But enabling this option would result in performance degradation of the system and application.

Convert Buffer Overrun Vulnerability:

This vulnerability exists in SQL convert function that allows authenticated user to gain elevated control over the system.

Microsoft has not received any information from customers or other users stating that their system is exploited using this vulnerability and information regarding this vulnerability was brought up through responsible disclosure.

Mitigation Factor: Only users with database access can exploit this vulnerability.

Any other Workarounds: None

SQL Server Memory Corruption Vulnerability:

This vulnerability exists in SQL server that allows authenticated user to gain elevated control over the system.

Microsoft has not received any information from customers or other users stating that their system is exploited using this vulnerability and information regarding this vulnerability was brought up through responsible disclosure.

Mitigation Factor: Only users with database access can exploit this vulnerability.

Any other Workarounds: None

SQL Server Buffer Overrun Vulnerability:

This vulnerability exists in SQL Server function that allows authenticated user to gain elevated control over the system.

Microsoft has not received any information from customers or other users stating that their system is exploited using this vulnerability and information regarding this vulnerability was brought up through responsible disclosure.

Mitigation Factor: Users with database access can exploit this vulnerability. With SQL Server 2005, services by default run on non-admin accounts. Also SQL Server 2005 prompts for a domain account for services.

Any other Workarounds: None

Are we affected?

We can use Microsoft Baseline Security Analyzer (MBSA 2.1) to determine the vulnerabilities in the System. MBSA 2.1 detects these vulnerabilities and reports of any other missing security updates too.

Download URL:

http://technet.microsoft.com/en-us/security/cc184923.aspx

Deployment Plan and Known Issues:

Deployment Instructions are available in http://www.microsoft.com/technet/security/bulletin/MS08-040.mspx.

Download links and impact instructions for SQL Server

Verification after Installation: Executing the query “Select @@version” should result with the version number given below.

Also the information can be found in the log file on the directory listed below. SQL Server 2000 - %WINDIR%\SQLHotfix SQL Server 2005 - %PROGRAMFILES%\Microsoft SQL Server\90\Setup Bootstrap\LOG\Hotfix

Known Issues:

There are few known issues with this update as listed below.

1. Dependent services remain in stopped state even though SQL Services are started. We have to check and verify that all dependent services are started.
2. SQL Server services or components that are in disabled state will be updated. This security patch update requires that all services to be enabled and running so that the installer detects them and applies the necessary patches.
3. Upon unsuccessful completion, we need to reboot the system so that the changes are rolled back.

953743 (http://support.microsoft.com/kb/953743/) Supported method for applying updates to SQL Server 7.0
953742 (http://support.microsoft.com/kb/953742/) SQL Server 2000 and MSDE 2000 installers stop dependent services
953741 (http://support.microsoft.com/kb/953741/) SQL Server 2005 installers stop dependent services
953740 (http://support.microsoft.com/kb/953740/) How to identify and enable disabled SQL Server instances in SQL Server 2000
953739 (http://support.microsoft.com/kb/953739/) SQL Server 2005 installers do not update an instance of the SQL Server service that is in a
disabled state.

Other Points to Ponder:

This patch is included in SP3 of SQL Server 2005 which is scheduled to release in Third Quarter of 2008. By the time earning freeze is over, we should be having SP3 on which we can schedule to install so that this patch too is applied.

http://blogs.technet.com/dataplatforminsider/archive/2008/04/15/sql-server-2005-sp3-coming-soon.aspx
http://blogs.technet.com/beatrice/archive/2008/04/16/sql-2005-sp3-announced.aspx
http://blogs.technet.com/dataplatforminsider/archive/2008/01/25/microsoft-sql-server-2008-roadmap-clarification.aspx

Configuring Peer-to-Peer Replication

I wanted to give a step by step illustatration here too for Peer-to-Peer replicatio. But unfortunately I had almost 26 screenshots in the article and all the images were not able to fit to the size limit of 1024MB. So I am giving you the link to my website where the original article resides. Thanks.


http://www.sql-articles.com/index.php?page=articles/P2P_replication_GUI.html

Moving System Databases - SQL Server 2005

There are lots of articles, Links and sites that describe how to move system databases from the SQL default location to the location described by the DBA but still lot of users find difficult while moving them and land up in trouble. So I thought of wiriting an article for moving the databases so that the users can use the same and enjoy the task.

Tasks for moving system databases:

1.Moving tempdb databases.

a.) Execute the script below.

USE master;
GO
alter database tempdb MODIFY FILE (NAME = tempdev,FILENAME='NEW PATH');
GO
alter database tempdb MODIFY FILE (NAME = templog,FILENAME='NEW PATH');
GO

b.) Restart services.
c.) Confirm path of database files.

2. Moving model and msdb databases.

a.) Execute the script below.

USE master;
GO
alter database msdb MODIFY FILE (NAME = MSDBData,FILENAME='NEW PATH');
go
alter database msdb MODIFY FILE (NAME = MSDBLog,FILENAME='NEW PATH');
go

USE master;
GO
alter database model MODIFY FILE (NAME = modeldev,FILENAME='NEW PATH');
go
alter database model MODIFY FILE (NAME = modellog,FILENAME='NEW PATH');
go

b.) Stop services
c.) Copy the files to the new location
d.) Restart services.
e.) Confirm path of database files.

3.) Moving master database:

a.) Edit the startup parameters to reflect the new path for –d, –l and -e parameters.
b.) Stop the services.
c.) Move the master and resource database files to the new location
d.) Change the sql port to different one than usual to avoid excess connections and create an alias with that port
e.) Start the services using NET START MSSQLSERVER /f /T3608 (*MSSQLSERVER is for default instance, if you have installed named instance then you need to use NET START MSSQL$Instancename /f /T3608)
f.) Execute the script given below from sqlcmd

USE master;
GO
alter database mssqlsystemresource MODIFY FILE (NAME = data,FILENAME='NEW PATH\mssqlsystemresource.mdf');
go
alter database mssqlsystemresource MODIFY FILE (NAME = log,FILENAME='NEW PATH\mssqlsystemresource.ldf');
go
alter database mssqlsystemresource set READ_ONLY;
go

g.) Stop the services
h.) Change the port back to 1433
i.) Remove the alias
j.) Start sql services.
k.) Confirm if the data files and log files reside on desired path.

How Addicted to Blogging Are You?

85%How Addicted to Blogging Are You?

Short Cut Keys for SQL Server 2000 and 2005

I have always googled out for finding any shortcut key to be used in SQL Server that gave me an idea to post this. I have gathered all the SQL Server 2000 and 2005 shortcuts with the help of books online and internet and posted it in our website which can be accessed from the link below.

http://www.sql-articles.com/index.php?page=articles/shortcut_keys.htm

SQL Server 2008 in August

Microsoft has announced that SQL Server 2008 will be in August price list. This means that SQL Server 2008 RTM will be launched as scheduled in Q3 2008. The blog entry from dataplatforminsider confirms this from Microsoft.

http://blogs.technet.com/dataplatforminsider/archive/2008/07/09/sql-server-2008-available-on-august-price-list.aspx

Thank You - My Friends

These are the mails, messages that i got as wishes for my MVP. Thank you friend. With all your prayers and wishes, I would like to grow.

My Ex-Managers and Ex-Leads:

Thiyagarajan Jayaraman: (My Project Director @ Hexaware)

Congratulations Sugesh. Wish I never lost you. Happy to know that you have not stopped because of the setback. Hope you would not let such things happen again.

Wish you all the best. Thank you for remembering to keep me informed
regards
Thiyagu
-------------------------------------------------------------------
Eyalarasan: (My Team Lead @ CITIGROUP)

Hi Sugesh,

Congratz!

Just went through the site (www.sql-articles.com).. great!

You guys are doing great job... My hearty wishes!!

Regards
Eyal.s
***************************************************************************

Colleagues(Ex and Current):

Sriram Srinivasan

Great News!!! Congratulations now you have to further work hard to retain the title.

All the best
-------------------------------------------------------------------
Ajay

Hi Sugesh,

Great buddy... Hearty Congratulations on your SUCCESS... Ofcourse this is just the beginning.... Nice to know that you are one among the valuable professional's of MS. and I am proud to tell others that I know a MVP... I was checking out your site and it's really excellent and informative...
-------------------------------------------------------------------
Saravanan

Hi Sugesh,

My Hearty Congrats to you.

I know that you are working as a SQL SERVER DBA, and did not have much information other than that.

I am very happy to know that you have made into a number of handful people around the world.

All the Best for your future endeavours

Thanks
Saravanan.S
-------------------------------------------------------------------
Anandhi

congrates on this great achievement Sugesh.
Hats off to you and All the very best for your future as well...
-------------------------------------------------------------------
Roopa Jean:

Hi Sugesh,
congrats a billion Billion....
hey wow so happy for the great acheivement....yes you deserve it....
wish you many such acheivements in the fore coming days......
cheers
Roopa Jean
-------------------------------------------------------------------
Shomi Basu (Current DBA in my project @ Hexaware)

Dear Sugesh,

Hope you remember me. Congratulations for your success and hope to see you even higher. Will bug you again and again for all my doubts.

Regards
Shomik
-------------------------------------------------------------------
Naresh C. (Current DBA in my project @ Hexaware)

Congratulations Sugesh.
***************************************************************************
Friends:

Surabhya Rao:

Hey Sugesh!

Congrats man!! Chance illa....engayo poita...am sure lots of hard work must have gone into it! Very happy to know that all your efforts have got appreciated and acknowledged!!
Very happy to hear this news...congrats once again!! Keep it up :)

Regards,
Surabhya.
-------------------------------------------------------------------
Sriram V(SSN):

Sugesh,
Congratulations and I have already bookmarked the sql-articles page. Good luck with ur efforts!!
-------------------------------------------------------------------
Chakravarthy Kailash:

Machi i am so happy that as if i achieved it............ really so happy machi.......... congrats ........ and really you deserve it for all the hard work you have done so far................
-------------------------------------------------------------------
Viswanathan Swaminathan

Hey Sugesh,
Great to hear that you are one of the MVP. Congrats da!!!!
-------------------------------------------------------------------
Balakumar:

Hey Sugesh,

Way to go man...Congratulations and my Best wishes.

Thanks,
Bala
-------------------------------------------------------------------
Narendran Mohan:
It is great to see you among the top contributors around the globe. Very proud of you...All the best and let this be one more stepping stone for you..

With best regards,
Naren
-------------------------------------------------------------------
Dalavai

This is simply great … Keep going buddy…congrats and all the best.
-------------------------------------------------------------------
Swetha

Wow. Many congratulations Sugesh! That is a definitely a great achievement.
-------------------------------------------------------------------
Sasi:

I'm really proud to see this.. Keep going!! All the best Sugesh.
-------------------------------------------------------------------
Odaiyappan

Congrats Sugesh, Keep going.

Gone through those web pages of microsoft and ur sql site it is an excellent work, i am proud that i shared the class room with you at one point of my schooling.
-------------------------------------------------------------------
Samuel Ignatius:
Hi Sugesh,

many congrats, really good to hear that, specially when i know and have seen you that how much you desired to get this certification. keep in touch.

regards
Samuel
-------------------------------------------------------------------
Vinod Chidambaram
Congrats da Sugu....Very very very happy for you da....God will be always with you...Celebrate this occasion and enjoy life.
-------------------------------------------------------------------
Srividhya Seshan

Hi,
Hearty Congratulations!!! I am sure you will hold it forever.Be an MVP everywhere. Wish you the very best always.
Congrats Once again.

Thanks,
Srividhya.
-------------------------------------------------------------------
Rohini Bharath

Hi Sugesh,

Hearty Congratulationns!!!!!!!!My best wishes for your future achievements toooooooooo
-------------------------------------------------------------------
Dinesh

Congrats da mama.
All the very best. I explored the site a bit,looks clean n simple interface.
Good job,convey my wishes to chakra and seth.
-------------------------------------------------------------------
Rekha

Hey, Congratulations on your success! I feel happy to see a fellow student come so far in his professional life. I wish you success in whatever you do on your way to glory!
-------------------------------------------------------------------

Awarded Microsoft MVP

I am coming with the happiest news of my professional career. For people who might know me for long this could be great news and for others it would be great to know this news.

I am now proud to say that I am part of few Microsoft Most Valuable Professional’s (MVP's) around the globe. I have been awarded this for my contributions towards SQL server and will be holding this title for a year from now. (I am sure; I will be going for a renewal too, no giving back again).

At this point, I would like to thank a few people who got me to this. The list might be small but these people mean a lot to me towards achieving this.

Krishna Gopalan - My manager at Hexaware. When I joined Hexaware, i was in infant stage of working with SQL Server. He was the one who had trust in me giving variety of tasks involving lot of SQL Server and other Microsoft products helping me to achieve near expertise. I could still hear him saying "Sugesh, I know you are the right person to do this job". That was the level of confidence that he had on me. I can never forget the ISA implementation that I worked on with his guidance. The hard to understand infrastructure application. Without him, i wouldn't have made it right here.

Stephen Seth - One of my best friends, He too works as a SQL Server DBA. i would literally pain him asking N number of questions and making comments out of everything so that we both were in right path of learning something that we were working on. Lot of my articles, scripts and comments has gone for his review before being published.

Vidhya Sagar and Deepak - My collegaues @ CITIGROUP. These are other 2 guys who have been with me together through out this journey. When we started our website (www.sql-articles.com) they both backed me up in designing the website which a person would like to read things about SQL Server. The great news here is they too are MVPs now.

And my parents and sister, I have no words to thank them. They have always stood besides me on whatever i do. Without their constant support and encouragement i would not have really achieved anything out here.

If i would have forgotten to name anyone here, please apologize me. Thanks for all others supporting me and would love to say
a BIG thanks again.

https://mvp.support.microsoft.com/profile=5888001B-22BC-4DFB-BEF1-F640063A9568

Configuring Peer-to-Peer replication Using TSQL:

This article brings you the easiest way to configure P2P replication using TSQL. Also I assume that the database has been initialized in all nodes participating in the replication.

1. Configure Distributor:
This script needs to be executed against all servers that participates in the replication.

/* Check if distributor database already exists*/
declare @svrname as sysname
select @svrname = @@servername
declare @cnt int
Select @cnt=count(*) from sys.databases where name = 'distribution'
If @cnt>0
begin
Print 'distributor already exists'
End
Else
Begin
Exec sp_adddistributor @distributor=@svrname
Exec sp_adddistributiondb @database='distribution',@security_mode=1
End

2. Create Publication and Subscriber:
This script needs to be executed against all the publisher databases involved in the P2P replication instances. There are few parameters that you might need to change in this script which will be noted so edit the parameters before executing.

/*Add the publisher*/
declare @svrname as sysname
select @svrname = @@servername
/* Add the publisher server now*/

exec sp_adddistpublisher @publisher =@svrname,
@distribution_db = N'distribution',
@security_mode = 1,
@publisher_type = N'MSSQLSERVER'

/* Enable the database for publication*/
declare @dbname varchar(30)
declare @dbcheck sql_variant

/* Check if the database is set as publication*/
/*Set you database name*/
select @dbname = '------' /***** Set Your Database Name Here**************/
select @dbcheck = databasepropertyex(@dbname,'ispublished')
select @dbcheck
if @dbcheck = 0
begin
print 'Database not set as Publisher. Setting as Publisher'
exec sp_replicationdboption @dbname=@dbname,
@optname='publish',
@value='true'
end
else
begin
print 'Database already set as Publisher'
end

/* Add the Publication now*/
declare @pubname varchar(300)
select @pubname = '---------' /********Add Publication Name Here***********/
/*Execute this step against the database which you need to set as publication*/
exec sp_addpublication @publication=@pubname,
@restricted='false',
@sync_method='native',
@repl_freq='continuous',
@allow_push='true',
@allow_pull='true',
@immediate_sync='true',
@allow_sync_tran='false',
@autogen_sync_procs='false',
@retention=60,
@independent_agent='true',
@enabled_for_p2p='true',
@status='active',
@allow_initialize_from_backup='true'
Go

/* Add articles for this Publication*/
/**********This step needs to be executed for each article in the publication***************/
declare @artname varchar(300)
declare @artins varchar(300)
declare @artdel varchar(300)
declare @artupd varchar(300)
select @artname = '-------'/**********Add Article Name Here***************/
select @artins = 'CALL [sp_MSins_'+@artname+']'
select @artdel = 'CALL [sp_MSdel_'+@artname+']'
select @artupd = 'CALL [sp_MSupd_'+@artname+']'

/*Execute this step against the database which you need to set as publication*/
declare @pubname varchar(300)
select @pubname = '----------'/***********Add your publication name here*************/
exec sp_addarticle @publication = @pubname,
@article = @artname,
@source_owner = N'dbo',
@source_object = @artname,
@type = N'logbased',
@description = N'',
@creation_script = N'',
@pre_creation_cmd = N'none',
@schema_option = 0x00000000000044F7,
@identityrangemanagementoption = N'manual',
@destination_table = @artname,
@destination_owner = N'dbo',
@status = 16,
@vertical_partition = N'false',
@ins_cmd = @artins,
@del_cmd = @artdel,
@upd_cmd = @artupd
Go

/*Add the Subscriber*/
declare @pubname varchar(300)
select @pubname = '----------'/***********Add your publication name here*************/
declare @dbname varchar(30)
select @dbname = '----------'/***********Add your Subscriber database name here*************/
declare @subname varchar(30)
select @subname = '----------'/***********Add your Subscriber Server name here*************/

exec sp_addsubscription @publication = @pubname,
@subscriber = @subname,
@destination_db = @dbname,
@sync_type = 'replication support only'
Go

/*Add the Subscriber Agents*/
declare @pubname varchar(300)
select @pubname = '----------'/***********Add your publication name here*************/
declare @dbname varchar(30)
select @dbname = '----------'/***********Add your Subscriber database name here*************/
declare @subname varchar(30)
select @subname = '----------'/***********Add your Subscriber Server name here*************/

exec sys.sp_addpushsubscription_agent
@publication = @pubname,
@subscriber = @subname,
@subscriber_db = @dbname,
@subscriber_security_mode = 1,
@frequency_type = 64,
@frequency_interval = 1,
@frequency_relative_interval = 1,
@frequency_recurrence_factor = 0,
@frequency_subday = 4,
@frequency_subday_interval = 5,
@active_start_time_of_day = 0,
@active_end_time_of_day = 235959,
@active_start_date = 0,
@active_end_date = 0,
@dts_package_location = N'Distributor'

Will Update Shortly

Hey Guys,

I have come up with lot of articles on Replication, Performance, Architecture and High availability solutions of SQL Server 2005. I will be posting them shortly. Just to keep you updated that there will be lot of articles sooner in this blog.

SQL Server JOB deletion ERROR

I have found in discussion forums regarding the error given below when you try to delete a job that was part of maintenance.

The DELETE statement conflicted with the REFERENCE constraint "FK_subplan_job_id". The conflict occurred in database "msdb", table "dbo.sysmaintplan_subplans", column 'job_id'.
The statement has been terminated. (.Net SqlClient Data Provider)


This error is caused not in all systems in some and it depends. I have tried to replicate the error but it would be always unsuccessful. Anyways the solution to can be handled with the script given below.

SCRIPT

declare @jobname varchar(100)

select @jobname='PASS YOUR NAME OF THE JOB'

delete from msdb.dbo.sysmaintplan_log
where plan_id in(
select plan_id from msdb..sysmaintplan_subplans
where job_id in(
select job_id from sysjobs where name =@jobname))

delete from sysmaintplan_subplans
where job_id in(
select job_id from sysjobs where name =@jobname)

Then you can delete your job. Hope this post helps you.

Testing Cluster Configuration:

Most people never like to test what they can done. Assuming that things have been done perfectly which results in a situtation of panic sometimes. So i thought that i could let you all know the steps to check after completing a cluster configuration.

1. Check if the Nodes are available and able to ping each other.
2. Check if you are able to reach the nodes from the client machines both with the names and IP address.
3. Try validating a failover and check if it failsover successfully. This can be done as given in the topic Validation Failover.
4. Check if all the disks are seen in the cluster group. This is the place most DBAs could go wrong. They might not have added the disks as dependencies in their configuration. To check and add disks as dependecies look on the topic Disk resources in cluster.
5. Verify error log and event viewer log.
6. Try connecting to the SQL Server management studio to check if it succedds.

Validating Failover:

1. Start Cluster Administrator.
2. In the left pane, expand Groups, right-click a cluster group, select the Move Group option, and finally select a node to move the resource group.
If the failover is functioning properly, the owner of the resources will change to the new node name.
3. Repeat step 2 to move the resource to all other nodes of the server cluster.

Disk resources in cluster:

1. Start Cluster Administrator.
2. Select the group that contains the disk resource you want to use with SQL Server Services.
3. Make sure the disk resource is owned by the node you are currently logged on to..
4. In the right pane, select the disk resource, right-click, select Change Group, and then select
the group that contains your SQL Server.
5. When prompted to confirm the move action, click Yes.
6. Once again, you will be prompted to confirm the disk resource move to the other group, Click Yes.
The disk will now be moved to the target group.
7. Take the SQL Server resource offline.
8. Right-click the SQL Server resource, and select Properties; alternatively,
double-click the resource. Select the Dependencies tab. Click Modify.
9. In the Modify Dependencies dialog box, select the new disk resource listed under Available
Resources. And move that resource to Dependencies;
10. The Dependencies tab will now show that the disk is added as a dependency of the
resource. Click Apply. Click OK.
11. Bring the offline resources online. SQL Server will be able to use the newly added disk.

Renaming a Failover Clustering Instance of SQL Server

Can you rename the SQL virtual name in a Cluster? The answer for this in SQL Server 2005 is abviously Yes. New to SQL Server 2005 is the ability to rename a clustered instance of SQL Server without having to uninstall and reinstall it.

Follow these steps:

1. Start Cluster Administrator.
2. Select the resource group that has the SQL Server resources.
3. Take the SQL Server service offline.
4. Right-click the SQL Server Network Name resource, and select Properties; alternatively, double-click the resource.
5. Select the Parameters tab of the resource’s properties page
6. Enter the new name of the failover clustering resource and click Apply.
7. Select the General tab. Change the name of the resource to include the new name you configured
in step 6. Click OK.
8. Bring the SQL Server resources online.
9. Ping the new name of the SQL Server failover clustering instance. If the name cannot be
resolved, you will have to flush your DNS cache by issuing these three commands in succession:
ipconfig /flushdns
ipconfig /registerdns
and nbtstat –RR.
10. Start SQL Server Management Studio, and connect with the new instance name. If this succeeds,
the change has been done successfully.
11. For a final verification, you can also run a SELECT @@SERVERNAME query, which should reflect the name change.

Note:
You can only rename the SQL Server virtual Name and not the instance name and this operation involves some down time.

Getting Information on SQL Server Clustering

SQL Server 2005 aruably gives the best resources to see if the instance is clustered or not. And if so, what are the properties of the cluster. The DMVs that are related to the Cluster can be used for this. These DMVs are very useful for a new DBA joining a company who can easily find out the environment and the disk details without needing for a proper document on place.

To check if the instance is clustered:

select SERVERPROPERTY('IsClustered')

If this returns 1 then your environment is clustered.

To check the computer name of the node owning the cluster

Select SERVERPROPERTY('ComputerNamePhysicalNetBIOS')

The following DMVs would can be used for the cluster information.

sys.dm_os_cluster_nodes
This will return a row for each node in the failover cluster instance configuration. If the current instance is a failover clustered instance, it returns a list of nodes on which this failover cluster instance (formerly ‘virtual server’) has been defined. If the current server instance is not a failover clustered instance, it returns an empty rowset.

Sample Output:

SQLCLUSN1
SQLCLUSN2

sys.dm_io_cluster_shared_drives

This will return the information such as drive name of each of the shared drives if the current server instance is a clustered server. If the current server instance is not a clustered instance it returns an empty rowset.

Sample Output:

D
L
T
X

SQL 2005 Edition Comparision

SQL Server 2005 comes with different editions with each having features that are either available in the other or not. This document lists the comparison of the features available between the different editions
http://www.sql-articles.com/index.php?page=articles/edcomp.htm

Isolation Levels

This article provides an easy-to-understand view of what ‘Isolation Levels’ really mean and when to use which level. ‘Isolation Level’ is a setting that decides how data which is a part of an ongoing transaction is made visible to other transactions.

http://www.sql-articles.com/index.php?page=articles/isolevel.htm

Locks & Locking Hints

Locking, Locks and Deadlocks are words that a developer/DBA does not want to hear in real life but still there are possibilities that we hear them because of some bad code written. This article takes you on to the type of locks available in SQL server and the ways of avoiding them using the hints available. Before getting into know the types of locks and the hints available, let us see what is meant by a lock and a deadlock.

http://www.sql-articles.com/index.php?page=articles/lhints.htm

Moving System Databases

There are lots of articles, Links and sites that describe how to move system databases from the SQL default location to the location described by the DBA but still lot of users find difficult while moving them and land up in trouble. So I thought that I would just do a graphical way of moving the databases so that the users can use the same and enjoy the task.

http://www.sql-articles.com/index.php?page=articles/msysdb.htm

Upgrading to SQL Server 2005

As we all know that Microsoft has released SQL Server 2005 sometime back and is planning to release the next version KATMAI before end of 2008. Also it has announced the end of life cycle support for SQL Server 2000. It’s high time that we start with upgrading our instances with SQL Server 2005 and upgrade before the life cycles support ends.

http://www.sql-articles.com/index.php?page=articles/usql.htm

Query Plans & Planguides

Most developer DBAs have tough time tuning TSQL part of the database and application. And most people leave in middle of it either frustrated or can’t go beyond that. Here is a simple look on how to use the query hints and plan guides to tune the TSQL part of your database and application.

http://www.sql-articles.com/index.php?page=articles/query_plans.html

Memory Management in SQL Server

I have heard people asking me how SQL Server manages memory. The answer to this was very difficult with SQL Server earlier versions. And with SQL Server 2005 it has been simpler to explain and understand. SQL Server by default installation manages memory dynamically and has settings that it can well negotiate with the operating system for the need of memory. Let us look into the depth of the SQL Server memory management.

Read more of this on http://www.sql-articles.com/index.php?page=articles/memory_management.html

Check Identity

This will list out identity columns in a Database. This is compatible to SQL 2005 only.
http://www.sql-articles.com/index.php?page=Scripts/check_idenity.php

Connection Property

This script will list out the current connection properties on SQL 2005.

http://www.sql-articles.com/index.php?page=Scripts/conn_prop.php

Database Details

This will list out the Database properties of your database in sql server. This is compatible to SQL 2000 and 2005. Before you use the script make sure you have given the databasename for the parameter @dbname.

http://www.sql-articles.com/index.php?page=Scripts/db_details.php

Index Details for Tables

This script will list out the details of the indexes in a table.This is compatible to SQL 2000 and 2005.
http://www.sql-articles.com/index.php?page=Scripts/index_details.php

Permission List

This script gives a detailed information about permission for users in a database. This output lists Object name, Schema name, user name and permission. This is compatible only to SQL 2000.
http://www.sql-articles.com/index.php?page=Scripts/permission_list.php

Permission List SQL 2005

This script gives a detailed information about permission for users in a database. This output lists Object name, Schema name, user name and permission. This is compatible only to SQL 2005.

http://www.sql-articles.com/index.php?page=Scripts/permission_list_2005.php

Scripting User level Permission - SQL2000

This script gives a out script that can be used to script out the exising user permissions in a database.This is compatible only to SQL 2000.Don't forget to change the database name in paramert @dbname before using the script.

http://www.sql-articles.com/index.php?page=Scripts/scrip_user_00.php