Archive

Archive for June, 2008

Automating administration across multiple instances of SQL Server (MultiServer Administration)

June 18, 2008 3 comments

Multiserver administration is the process of automating administration across multiple instances of Microsoft® SQL Server™.

With multiserver administration, you must have at least one master server and at least one target server. A master server distributes jobs to and receives events from target servers. A master server stores the central copy of job definitions for jobs run on target servers. Target servers connect periodically to their master server to update their list of jobs to perform. If a new job exists, the target server downloads the job and disconnects from the master server. After the target server completes the job, it reconnects to the master server and reports the status of the job.

In this topics we will talk about how to set up Multi Server Administration with SQLServer 2005.

Steps to setup MultiServer Administration:

(1) Check if Master and Target Server have installed SQLServer 2005 SP2.

(2) SQLServer Services and SQLAgent Services should not be runnng with localaccount.

(3) In case if u want to use default Encryption (no certificate) or Encrytion with Certificate Certificates

Open SQL Server Configuration , navigate to SQL Server 2005 Network Configuration and Select the Instance  u want to Set Encryption.

 Instance Protocol Properties

 

 

 

 

Click to Open Properties Window

 Instance Protocol Properties

 

 

 

 

To enable default encryption select option(yes) and click OK.In Case if u want to enable Encryption with Certificate.First Install the certificate on master and target server ,navigate to the Certificate Tab of Instance Protocol Properties and select the Cetificate.

Note : Encryption need to enable on both Master and Target Server.

Once Encryption is enabled at Master and Traget Server,restart the services of master and target server.

Lets Setup Multiserver Administration

Steps: Setup Multiserver Administration

(1) Register the Master Server and Target Servers in Consol Management of SQLServer 2005.

 

Registered SQL Servers

 

 

 

 

 

(2) Select the Master Server from Registered SQL Server and Connect.

 Select Master Server from all Registered SQL Servers

 

 

 

 

 

 

 

(3)Expand Master Server and  Navigate to SQL Server Agent–>Right Click–>Select Multi Server Administration –>Make this a Master

Master Server Wizard will pop up.

 Setup_MasterServer

 

 

 

 

 

 

 (4)Click Next

MasterServer_Wizard

 

 

 

 

 

 

(5) In Master Server Operator Wizard Provide email address for notification.As master server operator will be created on the master server and each of the target servers.

MasterServer_Operator

 

 

 

 

 

 

 

(6)Select the target server from the registered server window and move the same to target server list box.If need you can provide the description of the target server.Connect the target server from the connect box under target server list box.Click Next to Check the version compatibility of the master and target server.

Setup_TargetServer

 

 

 

 

 

 

(7) Checking Server version Compatibility

Master_TargetServer_Version_CompatibilityCheck

 

 

 

 

 

(8)Click Close.You will be naviagated to Master Server Login Credential window.

I have unchecked the option to create new login for master server, as i am logged in with my domain id with full permission.

MasterServer_Login Credentials

 

 

 

 

 

(9)Click next for  Summary Wizard.

MasterServer_Finish

 

 

 

 

(10) Click Finish to setup the Multi Server Administration Task.Enlist Task should willl pop up an error message. (MSX enlist failed for Job Server ‘xyz’)

 

MasterServer_Final_Finish

If u read the error message it states “The Target Server cannot establish an encrypted connection to the master server.”

By default SQLServer Registery values have setup encryption of level 2 (Enables full SSL encryption and certificate validation between target server and the master server.)

 

In our case we are not using SSL encryption and Certificate validation, change the value from 2 to 0.

To configure the appropriate level of security required for a specific master server/target server communication channel, set the SQL Server Agent registry subkey \HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\Microsoft SQL Server\<instance_name>\SQLServerAgent\MsxEncryptChannelOptions(REG_DWORD)
on the target server.If you are not using a certificate for Secure Sockets Layer (SSL) encrypted communications bet

(11)Check Default Value for  SQL Server Agent registry subkey

UpdateRegisteryValue_1

 

 

 

 

(12) Change the Default value of SQL Server Agent registery key (MsxEncryptionOptions) to 0

Close the registery editor window and start again from Steps: Setup Multiserver Administration till Step 10.

Multi Server Administration Setup will end successfully.

MasterServer_Final_Finish_successfull

 

 

 

After Successfull Setup Master Server SQL Agent will show (MSX) and Target Server SQLAgent will show (TSX).

masterserver_msx_finish

 

 

 

 

Lets expand Master Server SQL Agent

SQL Server Agent(MSX)–> Jobs–>

U will see two new folder gets created by the name Local Jobs and Multi-Server Jobs.Local Job is for Master Server Only, where as Multi-Server will be responsible for all Target Server.We can Create the Job at Master and it will get deployed to the Targeted Server

 sqlagent_msx1

 

 

 

Lets Create a Job on Master Server and see how it gets created or modified on Target Server.

Create a new job in Master Server

MasterServer_MSX_Create_Job

 

 

 

 

Create Job steps

 masterserver_msx_create_job_step

 

 

 

 

Select the Target Server for Job to be Deployed.

masterserver_msx_create_job_settarget

 

 

 

 

 

Now Create the Job. The same will get create at master fisrt and later get syncronized to the selected target server.

masterserver_msx_create_jobcreated_master

 

 

 

 

 

Master will take few second to deploy or modify the job at target server.
Lets Check the Target Server

 masterserver_msx_create_jobcreated_target2

 

 

 

 

 

Job can be executed from Master Server and Target Server.Last Execution history can be viewed from Master server aslo.

The Step detail can only be seen from the Target Server only.

 

 

How to truncate Mirrored Database Log File

June 17, 2008 11 comments

Mirroring is a new feature comes with SQLServer 2005.
Mirroring provides High Availibity of databases without any dataloss.
Mirroring Comes with three flavours
(1) High Performance (asynchronous).
(2) High safety without automatic failover (synchronous).
(3) High safety with automatic failover(synchronous) need withness server.

(Read more in BOL  Look For : mirroring databases [SQL Server])

This document will focus on how to shrink log file while databases are participating in mirroring.

Database files participating in mirroring can’t be shirnked by using truncate_only option with Backup log command.

To shrink Log file of database  participating in mirroring, Backup the log file at any location (can be a local drive or network location)

 BackupLog <DatabaseName> to disk=‘D:\ff\dbname.trn’

Lets check if all transaction is written to disk.

DBCC LOGINFO(‘database name’)

Transaction which are written on disk will have status=0

In case if last transaction show status=2 then backup the database log once again.

Now shrink the file

DBCC SHRINKFILE(Logfileid)

or

DBCC SHRINKFILE(Filename,minsize)

 The file will shrink to its maximum extent.

 

 

How to create partitioned table and moving non-partitioned tables to partition schemes

June 10, 2008 Leave a comment

 Tables Partitioning is a new feature available in SQL Server 2005, focusing on 

improving performance of large database systems and Highly Transactional Databases. Partitioning feature is only available with SQLServer2005 EE and DE.

This article focuses on how to create a partitioned table and moving non-partitioned tables to partition schemes.

Steps: Creating the Partitioned Tables

1.Add FileGroups

 

USE [Database name]

GO

ALTER DATABASE [AdventureWorks] ADD FILEGROUP[FG_od]

GO

 

 

2.Add Secondry Data Files (.ndf) and associate it with respective filegroups

 

USE [Database name]

GO

ALTER DATABASE [AdventureWorks] ADD FILE

(

NAME = N’01_31_Jan_Dec_2008′,

FILENAME = N’G:\RSDBSecondryDataFiles1_31_Jan_Dec_2008.ndf’ ,SIZE = 2048KB ,FILEGROWTH = 1024KB

)TO FILEGROUP [FG_01]

GO

 

Once the fileGroups and secondrydatafiles have been added,Create partition fucntion and partition schemes.

3.Create Partition Function (creating partition function on datatype Int.)

 

 

USE [Database name]
GO

CREATE PARTITION FUNCTION [PF_DBAW_ON_IDNUMBER] ( INT )

AS RANGE

LEFT FOR VALUES (130000000,260000000,520000000,1040000000,…..)

 

     Creating partition fucntion on DateTime datatype.

 

USE [Database name]
GO

CREATE PARTITION FUNCTION [PF_DBAW_ON_DateTime](DateTime)

AS RANGE

LEFT FOR VALUES

(‘20080331 23:59:59:997’,–Jan-Mar2008

‘20080630 23:59:59:997’,–Apr-Jun2008

‘20080930 23:59:59:997’,–July-Sept2008

‘20081231 23:59:59:997’,–Oct-Dec2008

‘20090331 23:59:59:997’—Jan-Mar2009

)

RANGE LEFT/RANGE RIGHT

This is probably one of the more confusing aspects of table partitioning. Partition functions are defined with either RANGE LEFT or RANGE RIGHT. One way to remember the difference is that a function with RANGE LEFT means that the partition data relative to the boundary is to the left of the boundary; RANGE RIGHT means that the partition data relative to the boundary is to the right of the boundary.

If the boundary is <= 130000000 the value will be written in First filegroup.

If the boundry is > 130000000 and <= 260000000 the value will written in second filegroup and so on.

 

4. Create Partition Schemes

 

USE [Database name]
GO

CREATE PARTITION SCHEME PS_DBAW —name of partition scheme

AS PARTITION [PF_DBAW_ON_IDNumber]

TO (FG_01, FG_02, FG_03,FG_04,[Primary])

GO

— all partition are held as per the filegroup series and depending upon function.)

 

5. Create Table using partition Schemes (Partitioned Table)

 

Create table TestPartition

(

Cust_id INT,

Cust_date datetime

)

ON [PS_DBAW] ([Cust_id])

 

     Check the table properties to confirm table is partitioned or not.

     run below command to check data location.

     exec sp_help TestPartition

     Data_located_on_filegroup

     —————————

     PS_DBAW —————Partition Scheme

 

     Check Table partition structure

SELECT *

FROM sys.partitions

WHERE OBJECT_ID = OBJECT_ID(‘TestPartition’)

 Query Output:-  

partition_id

Object_id

index_id

partition_number

hobt_id

rows

72057594058964992

919674324

0

1

72057594058964992

0

72057594059030528

919674324

0

2

72057594059030528

0

72057594059096064

919674324

0

3

72057594059096064

0

72057594059161600

919674324

0

4

72057594059161600

0

72057594059227136

919674324

0

5

72057594059227136

0

 

Lets insert some data and see how it get partitioned. 

Declare @int int

set @int=0

while @int<10000

Begin

set @int=@int+14

BEGIN Tran

insert into dbo.TestPartition(Cust_id,Cust_date) values(@int,getdate())

commit

End

 Check Table partition structure

SELECT *

FROM sys.partitions

WHERE OBJECT_ID = OBJECT_ID(‘TestPartition’)

Expected Output:-

partition_id

Object_id

index_id

partition_number

hobt_id

rows

72057594058964992

919674324

0

1

72057594058964992

4501

72057594059030528

919674324

0

2

72057594059030528

3658

72057594059096064

919674324

0

3

72057594059096064

578

72057594059161600

919674324

0

4

72057594059161600

0

72057594059227136

919674324

0

5

72057594059227136

0

 

 

Check data in each filegroup.

SELECT $partition.PF_DBAW_ON_IDNUMBER(o.Cust_id)

AS [Partition Number]

, min(o.Cust_id) AS [Min Cust_id No]

, max(o.Cust_id) AS [Max Cust_id No]

, count(*) AS [Rows In Partition]

FROM TestPartition AS o

GROUP BY $partition.PF_DBAW_ON_IDNUMBER(o.Cust_id)

ORDER BY [Partition Number]

 

Steps : Moving non-partitioned tables to Partitioned schemes.

Before moving non-partitioned table to partition scheme , script all relationship(FK).Once you drop the clustered index participating in FK relationship all relationship will be dropped.

  1. Run sp_help tablename command to check the table status.Do not forget to check Foregin Key relationship.

  2. Drop clustered index and recreate again using dataspace type as partiton scheme.

 

GO

DROP INDEX <table_name>.<index_name>

GO

Create Clustered index

BEGIN TRANSACTION

GO

CREATE CLUSTERED INDEX IX_Cust_Id ON TestPartition

(

Cust_id

) WITH( STATISTICS_NORECOMPUTE = OFF,

IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON,

ALLOW_PAGE_LOCKS = ON) ON PS_DBAW(Cust_id)

GO

COMMIT

 

Once clustered Index is created , check the properties of the table,It will show table is partitioned.

Now create all the relationship again if any.