Microsoft SQL Server: December 2006

Useful Knowledge Bases and Links

SysComments v/s sys.sql_modules - Returns different result for same query

In SQL 2000 we used to query system table SysComments to get the SQL Definition of Objects like SP,Views,Trigger,Functions etc. It seems that the counterpart in SQL 2005 is sys.sql_modules. But when i ran same query for SysComments and sys.sql_modules, the rows affected was different(No. of Rows). Out of curiosity, i thought it need a postmortem. When i ran the following query in my R&D database , the SysComments returned 138 rows and sys.sql_modules returned 84 rows.

SELECT obj.name , mod.definition,obj.object_id,len(mod.definition) FROM sys.sql_modules mod INNER JOIN sys.objects obj ON mod.object_id = obj.object_id WHERE obj.type = 'p'

SELECT o.name, m.Text ,o.Object_ID, m.id,len(m.Text) FROM syscomments m INNER JOIN sys.objects o ON m.id = o.object_id WHERE o.type = 'p'

You know why this different result for same query; it because in SQL Server we have new Data Type called Nchar/nvarchar (Max) etc. So in SQL 2005 the SQL Definition is stored in nvarchar(Max) data type where as in SQL Server 2000 it is nvarchar(4000). So you will have different result when you have a large stored procedure definition.

Maximum Number of Instances by Edition in a Clustered and Nonclustered Configuration

SQL Server Editions Maximum Instances Allowed

Enterprise, Developer, -50
and Evaluation Editions

Enterprise, Developer, - 25
Evaluation Editions in a clustered configuration

Standard, Workgroup, - 16
and Express Editions

Standard, Workgroup, - 16
and Express Editions in a clustered configuration

SQL Server Instance-Aware Components SQL Server Component

SQL Server Component - Instance-aware?
--------------------------------------------------------------------------

SQL Server Database Services -Yes
(includes SQL Server Agent, Full-Text Search, and Replication)

SQL Server Analysis Services -Yes

SQL Server Reporting Services - Yes

SQL Server Integration Services - No

Notification Services -Yes
(instances are created
after installation; not during setup. )

Workstation components and tools - No

Export/Import Surface area Configuration

In larger environment you may need to have same Surface Area Configuration on many instance of SQL Server. In SQL Server 2005 , SAC command line utility make it very easier for you. To change the SAC setting using GUI is always time consuming and tedious. SAC Command line utility allow you to export and import surface area configuration to and from a file.
Here you go….

Suppose you have a master server and in that master you have configured the surface area as per the company security policy. Now you have another 50 instances to which you want to apply same configuration.

(a) Go to command prompt of Master server and change directory to “C:\Program Files\Microsoft SQL Server\90\Shared”
(b) Now you will have C:\Program Files\Microsoft SQL Server\90\Shared> prompt
(c) Give the following command
sac out C:\sac_MasterSetting.xml S MasterComputerName

Now you will have a xml file in C:\ with all you master server surface area configuration setting. Next step is to apply this to other instances

From the same directory, you can give the following command

sac in C:\sac_ MasterSetting.xml S Computer_1
sac in C:\sac_ MasterSetting.xml S Computer_2
sac in C:\sac_ MasterSetting.xml S Computer_3
……
sac in sac_ MasterSetting.xml S Computer_50

or what you can do is create a batch file and keep it in VSS for better management

Starting Database Engine in Single User Mode - SQL Server 2005

(a) Start --> SQL Server 2005 --> SQL Server Configuration Manager
(b) Select the Database Engine Service which u want to Start in Single User Mode and press Right Button
© You will have a SQL Server Properties window
(c) select Startup Parameter
(d) press “End” button of your key board
(e) Type ;-m
(f) Restart the Service

Connect to Remote SQL Server Configuration Manager

From Start --> SQL Server 2005 --> SQL Server Configuration Manager you can only connect to Local SQL Server instances. To connect to Remote instance’s SQL Server Configuration Manager there are two methods :-
(a) From SQL Server Management Studio

1. Open SQL Server Management Studio.
2. Click on Cancel if the Connect to Server dialog pops up.
3. In Management Studio, click on the View menu and select Registered Servers (if it is not visible)
4. In the Registered Servers window, right-click on Database Engine, select New and then Server Registration. This will launch the New Server Registration dialog.
5. In the New Server Registration dialog, enter the name of the remote computer in the Server name field, click on Test and then Save. If the Database Engine on the remote computer requires SQL Server authentication, you have to change Authentication to SQL Server Authentication to enter the username and password.
6. Back in the Registered Servers window in SQL Server Management Studio, right-click on the remote computer and select SQL Server Configuration Manager to configure the remote computer

(b) From MyComputer

1. Right button on “My computer” and select Manage and you will get Computer Management Window
2. Right click on Computer Management and select Connect to another computer.
3. You will have a "Select Computer Window"
4. Give the Remote Computer Name and press OK
5. Now, you will have the remote computer's Computer Management Window
6. Click on Services and Applications7. Select SQL Server Configuration Manager

Difference in SQL Server Profiler of 2005 and 2000

The main enhancement in SQL 2005 is, SQL Profiler 2005 also enables users to look at current activity happening inside Analysis Server.

Performance Counter Value from System Table

To see the current performance counter value
Select *from sys.sysperfinfo

Error Severity Level Ranges in SQL Server

Severity level Description

0-10 Indicate informational messages and non-severe errors
11-16 Indicate errors that can be corrected by the user
17-19 Indicate software errors that cannot be corrected by the user
20-25 Indicate system problems and fatal errors

Multiple Activity Monitor dialogs

You can have multiple activity monitor in SQL Server 2005 and you can also filter on Database/Process/User/BlockType etc. It really a wonderful enhanced feature from SQL 2000. Activity Monitor provides monitoring of blocking and lock information in the same interface, with options such as filtering and auto-refresh.

Activity Monitor Auto Refresh

View refresh settings on the left side pane of the Activity Monitor dialog , will give u a window to setup a auto refresh period for the Activity Monitor. Be aware that it is not recommended on a production server.

Reuse of Schedule in SQL 2005

In previous versions SQL Server, the job schedule was tightly coupled to a specific job and could never be reused. In SQL Server 2005, a user can create a schedule and then link multiple jobs to it. However, other users cannot link to a shared job schedule for security reasons.

SQL Server DMO and SMO

SQL Server 2000 Enterprise Manager uses SQL-DMO COM-Based API to manage SQL Server.
SQL Server 2005 replaces SQL-DMO with .NET based object libraries
(a) SQL Server Management Objects (SMO)
(b) Replication Management Objects (RMO).
You can't use SMO on VB6 or .NET 1.1 (2003). it is only available for .NET 2.0 and later

Remove Secondary Transaction Log File from Database

At time your database may have multiple Transaction log physical files and you may want to remove one of the Transaction log file from the database. Then run the following script

DBCC SHRINKFILE (,EMPTYFILE)
alter database test remove file

Eg.
DBCC SHRINKFILE ('test1_log',EMPTYFILE)
alter database test remove file Test1_log

Note : The primary data file and log file cannot be removed.

Dedicated Administrator Connection (DAC)

SQL Server 2005 provides a special diagnostic connection for administrators when standard connections to the server are not possible. This diagnostic connection allows an administrator to access SQL Server to execute diagnostic queries and troubleshoot problems even when SQL Server is not responding to standard connection requests.
Only members of the SQL Server sysadmin role can connect using the DAC.
The DAC is available and supported through the sqlcmd command-prompt utility using a special administrator switch (-A). You can also connect prefixing admin: to the instance name in the format sqlcmd -Sadmin:. You can also initiate a DAC from a SQL Server Management Studio Query Editor by connecting to admin:.

SQL 2005 Online Index (Only Available in Enterprise Edition)

Index operations can now be performed online; users can still access the table data and use other indexes on the table while one index is being created, altered, or dropped. In SQL Server 2005 EE , you can create, rebuild, or drop indexes online. The ONLINE option allows concurrent user access to the underlying table or clustered index data and any associated nonclustered indexes during these index operations.
Eg.
USE AdventureWorks;
GO
ALTER INDEX ALL ON Production.Product REBUILD WITH (ONLINE = ON);

Online Restore in SQL 2005

Restoring data while the database is online is called an online restore. Online restore is supported only on SQL Server 2005 Enterprise Edition. In this edition, a file, page, or piecemeal restore is online by default. Online Restore applies to only those database which have multiple files or file groups. The database can be online when the primary filegroup is online and all other secondary filegroups are offline. By default the restore command in SQL 2005 EE is Online only.

Piecemeal Restores in SQL 2005

In SQL Server 2005, databases that contain multiple filegroups can be restored and recovered in stages through a process known as piecemeal restore. Piecemeal restore involves a series of restore sequences, starting with the primary filegroup and, in some cases, one of more secondary filegroups. After the restore sequence is completed, recovered files, if they are valid and consistent with the database, can be brought online directly Piecemeal restore works with all recovery models, but is more flexible for the full and bulk-logged models than for the simple model.

All editions of SQL Server 2005 support offline piecemeal restores. In SQL Server 2005 Enterprise Edition, a piecemeal restore can be either online or offline.

FAQ : Table row count from all the tables in the database

1. Simple Method using undocumented stored procedure sp_msforeachtable

CREATE TABLE #TableCount (tablename sysname, ROWCNT int)

INSERT INTO #TableCount

EXEC sp_msforeachtable 'select ''?'', count(*) from ?'

SELECT * FROM #TableCount where rowcnt>0

DROP TABLE #TableCount

Method 2 : Using SysIndexes and Sysobjects system tables

SELECT O.[name]
,I.rowcnt
FROM sysobjects O
JOIN sysindexes I
ON O.[id] = I.[id]
WHERE O.xtype = 'U'
AND O.status > 0
AND I.indid <2

Method 3. Using Cursors

Create proc spCountNoofRecords
as
Begin
SET NOCOUNT ON
create table #RecCount(TableName varchar(300),NoofRecords int)
Declare @RecCount1 int,@TableName varchar(300),
@SqlStatement nchar(2000),@ParmDefinition NVARCHAR(500)
DECLARE cRecCount CURSOR FOR
select Name from Sysobjects where xtype='u' order by name
OPEN cRecCount
FETCH NEXT FROM cRecCount INTO @TableName
WHILE @@FETCH_STATUS = 0
BEGIN
set @SqlStatement=N'Select @RecCount=count(*) from ['+@TableName +']'
SET @ParmDefinition = '@RecCount int OutPut'
EXECUTE sp_executesql @SqlStatement, @ParmDefinition,
@RecCount = @RecCount1 OUTPUT;
insert into #RecCount select @TableName,@RecCount1
FETCH NEXT FROM cRecCount INTO @TableName
END
CLOSE cRecCount
DEALLOCATE cRecCount
Select *From #RecCount
End

Change Service Broker identifier After Database Restore

When u want to create a copy of a Database using Restore/sp_Detach &sp_Attach command if the database is Service Broker Enabled, then you should change the Database UniqueIdentifier after restoration. Ie. Each database has a identifier used for routing Service Broker messages to that database. In restore and sp_Attach/Detach method both database will have same identifier, so erratic routing may occur. In such scenario after restoration of the database, you must change the Identifier by the following command

ALTER DATABASE [DatabaseName] SET NEW_BROKER

Stored Procedure using Parameterized TOP clause

CREATE PROCEDURE spTopRecords
@Top int
as
-- set how many records to return
SET ROWCOUNT @Top
SELECT * FROM
SET ROWCOUNT 0

Configure SQL 2005 for Remote Access

Configure SQL 2005 for remote access in a Windows Firewall enabled Box

While connecting to a SQL Server instance installed in a Windows Firewall enabled box using web browser we may get an error saying “Timeout expired. The timeout period elapsed prior to completion of the operation or the server is not responding.” The cause of this error is that the web browser is not able to interact with the server because the port is blocked by the Firewall. It is also to be noted that the behavior differ from Named Instance to Default Instance. Generally, when we debug this error there are few steps to be followed :-

For Default Instance :-

(a) Make sure that the Database Engine is enabled for Local & Remote Connection and Using Both TCP/IP and Named Pipes (using Surface Area Configuration (SAC) Tool)
(b) Create an exception for SQL Server 2005 in Windows Firewall
loadTOCNode(3, 'moreinformation');
To create an exception for SQL Server 2005 in Windows Firewall, follow these steps:
(i) Right click on Local Area Network in Network Connection and Select Properties
(ii) Select ‘Advanced’ Tab
(iii) Select ‘Setting’
(iv) In Windows Firewall, click the Exceptions tab, and then click Add Port
(v) Give your Instance Name
(vi) Give the SQL Server Default Instance Port No (for eg. 1433 )
(vii) Select TCP
(viii) Click OK
(You can do this for the SQL Service instead of mentioning the port number, by using Add Program in the Exception Tab)
For Named Instance
When we use named instance generally the port number will be different from 1433(as 1433 is the default port for SQL Server). For named instance, you have to mention the port number in the connection string. If you are running SQL Server 2005 by using an instance name and you are not using a specific TCP/IP port number in your connection string, you must enable the SQL Server Browser service to allow for remote connections. Then you have to follow the above mentioned step for the corresponding ports. Apart from that you should make sure that the SQL Browser Service is running and the UDP Port 1434 is added in the exception. Steps are as follows
(a) Make sure that the Database Engine is enabled for Local & Remote Connection and Using Both TCP/IP and Named Pipes (using Surface Area Configuration (SAC) Tool)
(b) Create an exception for SQL Server 2005 in Windows Firewall
loadTOCNode(3, 'moreinformation');
To create an exception for SQL Server 2005 in Windows Firewall, follow these steps:
(i) Right click on Local Area Network in Network Connection and Select Properties
(ii) Select ‘Advanced’ Tab
(iii) Select ‘Setting’
(iv) In Windows Firewall, click the Exceptions tab, and then click Add Port
(v) Give your Instance Name
(vii) Give the SQL Server Named Instance Port No (for eg. 1433 )
(vii) Select TCP
(viii) Click OK

(c) Now, you should add an exemption for UDP port 1434
(i) In Windows Firewall, click the Exceptions tab, and then click Add Port
(ii) Give your Instance Name
(ii) Give the Port No as 1434
(iii) Select UDP
(iv) Click OK
(d) Go to the SQL Server Configuration Manager and Make sure that the SQL Browser is running

Transactional Replication in SQL Server 2000

Microsoft SQL Server 2000
An Introduction to Replication and
Step by Step process to Setup Transactional Replication

By
K Madhu

Introduction. This article gives a brief description of Replication and a Step by Step process of configuring Transactional Replication. I have not included the screen shot of setting up because it will occupy lot of space. I have given the Screen Name in quotes to identify which screen I am mentioning.

1. Introduction to Replication. Replicating Business data across an organization involves much more than simply copying a piece of information from one site to another. A replication system must address all of the following business needs :

(a) High Availability of Data :- The Replication system should be reliable and should not expose business operation to computer system failure.
(b) Consistent Information Delivery :- The distribution system must protect the integrity of data.
(c) High Performance :- The replication system must not burden the source of the data, must use networks efficiently, and must allow each site to optimize access methods to local data.
(d) Easy centralized Administration :- Administrators must be able to easily manage all the distributed components of the replication system from a single desktop.
(e) Heterogeneous Data Source Access :- The replication system should be able to move data across different vendors data sources.
(f) Local Autonomy :- Each site which receives replicate data should be free to decide which set of data it wishes to receive, how it will view the data, how it will access the data, and how it will modify the data.

3. What is Replication : Only a few a years ago , corporate data resided in a central location. Remote departments accessed the information they needed by establishing direct connections to the central sites, or by requesting printed reports from central MIS. The connections however were expensive, unreliable, and limited in number while the reports were inflexible and not timely.
Open systems brought inexpensive and powerful computing resources to all corners of an enterprise. The ability to share corporate information effectively using these new resources became an important competitive advantage for organizations. The question enterprises face today is not “Why distribute and share corporate data” , but rather “How can one distribute information effectively”.

4. Importance of Replication : In the present competitive environment, organization quickly discovered that a reliable replication system must do much more than simply copy a piece of data. The system must also :-
· Maintain the integrity of the data at the transaction level
· Deliver data quickly and efficiently across the network
· Allow distributed sites to modify data
· Be easy to monitor and manage
· Transfers data in any direction across heterogeneous data sources

5. Important factors for Planning Replication. When planning for replication you should consider the following factors :-
(a) Autonomy. You have to consider the level of autonomy or server independence that you want to support at each site. Determine what data needs to be replicated and at what frequency.
(b) Latency. It determines the time delay between updates. Ie. The time taken for a change at the publishing server to be made available at the subscribing server. Less Latency high data consistency.
(c) Data Consistency. Data consistency is the critical factor. It can be accomplished through data convergence or transaction consistency. Replication can ensure data consistency , but you will have to keep potential latency in mind . How data consistency is managed and maintained is somewhat dependent on the replication method you select.

Data Convergence means that all sites eventually end up with the same values. However , the values are not necessarily the same as they would be if all of the changes had taken place on one server

Transactional Consistency. Is a little different. The results at any server are the same as if all transaction were executed on a single server

(d) Schema consistency. The best part of SQL Server 2000 is that it supports Schema changes during relocation provided it is done through SP_Repl schema change stored procedure or through Replication GUI.

(e) You should consider your geographic location and connectivity available.

5. SQL Server 2000 as a Replication Server. Microsoft SQL Server comes with the replication server where as other vendors is not. For other vendors like Sybase it is a separate product altogether.

6. Components of Replication.

Publisher
Distributor
Publisher
Subscriber
Subscriber

Publisher. The source database is maintained at the Publisher. It is a server that makes the data available for subscription to other servers. It also identifies what data has changed at the subscriber during the synchronizing process.

Distributor. The Distributor collects them from the publisher’s transaction log. There are two types of replication Local The changes to the published data are stored on the Distributor’s Distribution Database where they are held until forwarded to Subscribers. The Distributor can be on the same server as the Publisher or can be at a different server. It depends upon your setting up and the server configuration. The distribution database stores any transaction awaiting distribution and tracks the replication status. A distributor can server multiple Publishers and Subscribers (see pict)

Subscriber. Subscriber do what it literally means. By subscribing to a publication which has one or more articles (Tables or any objects like sps , views etc) the Subscriber is subscribing to all of the articles in the publication. The subscriber can not subscribe to individual articles alone. Ie. It has the destination database. Subscription can be either PUSH or PULL

7. Type of Replication . MSSQL Server supports various types of replication. The type can be decided by answering following question.
Connectivity :- Sometime you will not have connectivity and you even want to replicate.
To Reduce Latency:- In some situation the data updates may required to be real time.
What is the tolerance for data on one or more of the replicated servers.

All the above points keeping in mind you can decide the type of Replication.

Snapshot Replication. When do Snapshot replication, a “Picture” is taken at the source of all of the data to be replicated. The destination database data will be replaced with this data. ( I am not going into detail of Snapshot as this article is aimed to address transactional replication).

Transactional Replication Is also known as dynamic replication. Here the modification to the publication at the publisher are propagated to the subscriber incrementally. Any logged changes to the published articles, such as Insert,Update and delete statements are tracked and replicated to the subscriber. All transaction are applied to the subscriber in the same order as it was applied to the publisher. In Transactional replication the modification are applied to the subscriber more or less real-time updates.

Merge Replication. Merge Replication provides advantageous of both snapshot and transactional replication. After the initial snapshot applied to the subscriber both the publisher and subscriber changes are tracked by the SQL Server. The changes will be synchronized on scheduled basis or on demand. ( I am not going into detail of Merge replication as this article is aimed to address transactional replication).

8. How Transactional Replication Works.

Log Reader Agent After a database is set up for transactional replication, its transactional log is monitored for changes to published tables. The log reader agent then has responsibility for copying the transactions marked for replication from the publisher to the distributor..

Distribution Agent This is responsible updating the data from distributor to the subscribers.

9. The Process of Transactional Replication

When you configure Transactional replication it copies the schema and data first time by the snapshot agent. After the initial snapshot has been completed transactional replication follows these basic steps :-

(a) Modifications are posted to the publisher’s transaction log
(b) The Log Reader Agent reads the transaction log and identifies changes marked for replication
(c) Changes taken from the transaction log are written to the distribution database on the distributor
(d) The distribution agent applies the changes to the appropriate database tables.
(e) You can set up the log reader agent to read the transaction log continuously or on a schedule basis.

10. When to use Transactional Replication :You should use Transactional replication when you want to reduce latency and provide Subscribers with relatively near real-time information. It normally required LAN connection.

Step by step procedure to configure Transactional Replication

Organizational Setting Up

Server A - This is the server where the Publisher (Source Database ) situated. You can also configure Distributor on the same server. So here I am configuring Publisher and Distributor on the Same Server.

Server B - This is the server Subscriber . Both publisher and subscriber are SQLServer 2000.

Login.

In the ideal scenario Server A and Server B will have their own Database Administrator (SA) and Password. So you have to have one login in Server B (ie in the subscriber server) for the replication purpose. Register the Server B in Server A using that login and password.

Configuring Distributor

(a) From the EM (Enterprises Manager) select Tools à Replication à Configure Publishing Subscriber and Distributor.
(b) Now you will get a “Welcome to the Configure Publishing and Distribution wizard” welcome Screen.
© Click Next
(d) Now you will get a “Configure Publishing and Distribution wizard for Server A” Screen. In this screen you will get all the Distributor server name which is already there n the same domain. Either you can select the existing server as your Distributor or Configure a new one. Here we are configuring Server A as Distributor. So select the first option “Make Server A its own distributor: SQL Server will create a distribution database and log” option from this window.
(e) click Next
(f) if You have not changed the SQL Server Agent startup account from system account to any of your windows domain user account the following message will popup “SQL Server Agent on Server A currently uses system Account which causes replication between server fail ……”. If you found this message first you will have to configure SQL Server Agent in some other account (see Book on line SQL Server Agent help). It is assumed that you have a dedicated domain account to start sql server agent and it added into your SQL Server Login also.
(g) Now You will get a window called “Specify Snapshot Folder” Here you can define the folder as you required. By default SQL Server Installed path will come.
(h) Click next
(i) Now, you will get a warning message. If you have kept the default folder . Click “Yes”.
(j) Now you will get “Customise the configuration “ window. Here you have two option .
· Yes, Let me set the distribution ……
When you click this option you can give your own distribution database name and distribution database data files path.

· No, Use the Default setting.
If you click this option the distribution database will be named as Distribution and files will be stored in the SQL Server default path.
(k (a) ) if you click “NO” option in the above screen you will get “Completing the configure Publishing and distribution wizard”.
(k (b)) if you click “Yes” option in the above screen , you will get “Provide Distribution Database Information Screen” where you can change the default configuration of distribution database.
(k © ) After the need full changes been done, click next.
(k (d) ) Now you will get “Enable Publisher” window, where by default Server A will be checked. Click next.
(k (e) ) Now you will get “Enable Publication Database” window from where you can select the Database which has to be Published (ie. Used as source database in replication). Here you can select either Transactional Replication or Merge Replication. Not Snapshot. Here I would Prefer, not to create Publisher or subscriber from this window. Just do not check any thing here click next, next and finish.
(l) Click Finish to create Distribution Database.
(m) You will get a confirmation window stating that the Server A is now a distributor ……. Etc.
(n) Click Close.

After the configuration of distribution database, the Replication monitor toll will be added Automatically to your server A. Now you can see that a new distribution database which you have defined earlier, is created which will act as a repository of data to be replicated.

Configuring Publication

After configuring distributor, the second step is to publish your Source database. To configure Publication do the following steps :-

(a) in the EM click on Tools à Replication à Create and Manage
Publication option
(b) You will get “Create and Manage Publication of Server A” window ,
click on “Create Publication “
(c) You will get “Welcome to the Create Publication wizards” Screen .
(d) Check Show Advance Option and click Next
(e) In the “Choose Publication Database” window select the database which is to be published and click next.
(f) Now you will get “Select Publication Type” window from where you can select the publication type. As this article is for Transactional Replication I would select Transactional replication. Click Next.
(g) click “Immediate Updating” In the “Updatable Subscriptions” window. If you select “Queued Updating” then the changes will be replicated to Subscriber as per the Schedule. Press Next.
(h) Now You will get “Specify Subscriber Types” Windows , select the suitable option from this window. Here I am select “Server Running SQL Server 2000”. Click Next
(i) Now you have “Specify Article” Window , where you can select which all are the object you want to replicate. You can find a Button called “Article Default” . Click on this button .
I. Now you have a window called “Default Table/object Article Property” and it has three Tabs (incase of table object). It depends on what object you have selected).
II. In the “General” Tab you can change the owner of the Destination Table if needed.
III. In the “Command” Tab of this window you can change the Naming convention of the Triggers which will be created.
IV. In the “Snapshot “ window of this table you have so many options. By default “Drop the existing table and re-create it” Radio Button will be selected. You can check the User Triggers check box if you want to replicate the triggers also. If you want to uncheck some object you can do that here.

(j) click Next in the “Specify Article” Window after doing the need full change as per mentioned in sl. h(a) , h(b) , h(c),h(d).
(k) Now you have “Article Issue” window where you no need to do any thing, just click next.
(l) In the “Select Publication Name and Description” window you can give the appropriate name like TransReplServerAtoServerBDatabaseName etc. Click Next
(m) Now you have “Customise the property of the Publication” window where You have two option.
· “Yes I will filter …” : If you want to apply any filter to the source table you can select in this window. Ie. If you want to replicate some selected column/row of the source table then select “Vertical / Horizontal Filtering as you needed.
· “No , Create the Publication….”
Click Next
(n) In the “Completing the Create Publication wizard” click “Finish”.
After configuring the Publisher, your published data base will have Shared Symbol.

Configuring Subscription

After configuring Publisher, next step is to configure Subscription. To configure Subscription do the following steps. You have two types of Subscription. Push, Pull Subscription. Here were are configuring Push Subscription. :-

in the EM click on Tools à Replication à Push Subscription to others

(a) in the “Create and Manage Publication on ServerA” window , expand the published database. Select the Required Publication from this window and click on “Push New Subscription” .
(b) In the “welcome to Push Subscription Wizards” window check “Show Advance options in this wizard” and click Next.
(c) “Choose Subscriber “ window select ServerB from the Servers List shown in the list.
(d) In the “choose Destination Database” window give the Subscription Name and click on “Browse and Create” button to select the destination database in ServerB. Now you will get a list of all user defined database in the ServerB and you can select the destination database from the list and click OK.
(e) Click Next.
(f) In the “Set Distribution Agent Location” window you have two options

· Run the Agent at the distributor
· Run the agent at the subscriber
Select the Run the agent at the distributor.
Click Next
(g) in the “Distribution Agent Schedule” window You have two options
· Continuously : By selecting this option the changes will be replicated to the subscriber near real time.
· Using the Following schedule : if you have selected this option , the changes will be updated in the target database as per the schedule.
Select Continuously and click next.
(h) In the “Initialize Subscription” window you have two options
· Yes Initialize the schema and data : When you select this option you have another option “Start the Snapshot agent ….” Check this box also to start the replication immediately.
· No the Subscriber already ……. :

When you configure the subscriber first time check the first option and click Next.
(h) In the “Updatable Subscription “ window you have two option
· None – the changes are not Replicated : This is the most used option
· Immediate Updating : if you are selecting this option you should be careful
(i) in the “Start Required Service” window the “SQL Server Agent” box will be already checked if the Agent is running. Otherwise you can check this box and click Next
(j) in the “Completing Push Subscription wizards” window click finish

The Subscription configuration is completed. You will get a confirmation box saying that “Subscription were created on ServerB successfully at the following ……” . Close the window.

Conclusion. In brief, Transactional replication give you a near real-time data at the subscriber. Once you have configured Replication, you can make script of the configuration you have made. SQL Server gives us a way of generating replication scripts , which are Transact – SQL scripts based on the replication configuration what you have already made . This scripts let us document our configuration and give us an easy way to recover and reinstall replication. If you need to set up multiple servers with identical configuration, you can configure graphically in one server, generate a script and use the script to configure the remaining servers.

Author :

K Madhu
DBA
DOP, Indian Navy
e-mail ID - madhuotp@Yahoo.com

Microsoft SQL Server

Saturday, December 30, 2006

Useful Knowledge Bases and Links

Tuesday, December 26, 2006

SysComments v/s sys.sql_modules - Returns different result for same query

Thursday, December 21, 2006