MSSQL Shared Servers
Post # 27 permalink Topic #27 by mreschke on 2008-02-18 11:50:12 (viewed 417 times)

Setting up shared servers in Windows 2003.

Shared servers allows multiple servers to communicate with each other through queries and stored procedires.

Example: Server1 can read/write/query to Server2 and visa versa

Setuping up the Distributed Transaction Coordinator[-][- -][++]

  1. Goto Administration Tools -> Component Services
    1. Under Services (local), start the 'Distributed Transaction Coordinator'
    2. Goto properties on 'Distributed Transaction Coordinator', Log On tab, make sure account is NetworkServices
  2. Under Component Services, expand to My Computer, goto properties on My Computer
    1. MSDTC tab, then click Security Configuration
    2. Check 'Network DTC Access'
    3. Check 'Allow Remote Administration'
    4. Check 'Allow Inbound'
    5. Check 'Allow Outbound'
    6. Check 'No Authentication Required'
    7. Check 'Enable Transaction Internet Protocol (TIP) Transactions'
    8. Check 'Enable XA TRansactions'
    9. The Acount should be NetworkServices here also
  3. Open MSSQL Enterprise Manager, expand to localhost
    1. Open Security
    2. Right Click Linked Servers, click New Linked Server...
    3. Enter each servers information, on each server

To test shared server connection, type this in query analyzer:[-][- -][++]

Code Snippet
SET xact_abort ON 
GO
USE  pubs
GO
BEGIN DISTRIBUTED TRANSACTION
SELECT  *  FROM [server-name].pubs.dbo.authors
COMMIT TRAN
GO

Replace server-name with your shared server, ex: [server1].pubs.dbo.authors

SET ANSI_x and XACT_ABORT on all shared procedures[-][- -][++]

In all your shared server procedures, during creartion, just above CREATE PROCEDURE, you must type

Code Snippet
SET ANSI_NULLS ON
GO
SET ANSI_WARNINGS ON
GO
CREATE PROCEDURE .....

Right under AS in your stored procedures you must set XACT_ABORT ON

Code Snippet
CREATE PROCEDURE dbo.SomeProcedureName

AS 
SET XACT_ABORT ON
...
GO

Example Procedure (with a nice server looping cursor, limited to 8000 characters)[-][- -][++]

Code Snippet
CREATE PROCEDURE dbo.SomeProcedureName

/*
mReschke 2008-01-20
Adds a new template and binds it to a module (planID)
*/

@planID as int,
@templateName as nvarchar(50),
@templateFile as nvarchar(50),
@templateFile2 as nvarchar(50),
@templateTypeID as int,
@templateSize as nvarchar(50),
@coopID as int

AS 
SET XACT_ABORT ON

DECLARE @server as nvarchar(50), @sql as nvarchar(4000) DECLARE SharedServers CURSOR FORWARD_ONLY FOR SELECT server FROM ebis_prod.dbo.SharedServerList OPEN SharedServers FETCH NEXT FROM SharedServers INTO @server BEGIN TRANSACTION
SET @sql = 'DECLARE @identity int BEGIN TRANSACTION' WHILE @@FETCH_STATUS = 0 BEGIN IF (@server <> '') BEGIN SET @sql = @sql + 

'
INSERT INTO [' + @server + '].Ebis_Prod.dbo.tblWizardTemplates (templateName, templateFile, templateFile2, templateTypeID, templateSize, coopID)
VALUES ('''+@templateName+''', '''+@templateFile+''', '''+@templateFile2+''', '+CONVERT(varchar,@templateTypeID)+', '''+@templateSize+''', '+CONVERT(varchar, @coopID)+')

SET @identity = (SELECT templateID FROM ['+@server+'].Ebis_Prod.dbo.tblWizardTemplates WHERE
    templateName = '''+@templateName+'''
    AND templateFile = '''+@templateFile+'''
    AND templateFile2 = '''+@templateFile2+''')

INSERT INTO [' + @server + '].Ebis_Prod.dbo.tblWizardModuleTemplates (planID, templateID)
VALUES ('+CONVERT(varchar, @planID)+', @identity)
'

END FETCH NEXT FROM SharedServers INTO @server END SET @sql = @sql + ' COMMIT TRANSACTION' exec (@sql) COMMIT CLOSE SharedServers DEALLOCATE SharedServers

GO

Example Procedure (unlimited)[-][- -][++]

The disadvantage of the string concatenation sql statement above is that it has a size limit of 4000 \
(@sql nvarchar(4000)), or you could use varchar(8000). Still, this limitation will often be a problem, \
if it is, you will have to use \ the normal style T-SQL and just duplicate your query for each server. \
This method is not very expandable or reusable but will have to do.

Code Snippet

CREATE PROCEDURE dbo.someprocedure

/* mReschke 2008-01-21 Update to add planID, templateID to tblWizardTemplateWebControlOptions on insert */

@controlID integer,
@planID as int,
@templateID as int,
@optionID integer,
@optionTypeID  integer,
@optionHTML ntext

AS
DECLARE @identity as int
SET XACT_ABORT ON

IF @optionID > 0
BEGIN
BEGIN DISTRIBUTED TRANSACTION

    UPDATE tblWizardTemplateOptions SET 
        optionHTML = @optionHTML,
        optionTypeID = @optionTypeID
    WHERE optionID = @optionID

    UPDATE [dyna-sql].Ebis_Prod.dbo.tblWizardTemplateOptions SET
        optionHTML = @optionHTML,
        optionTypeID = @optionTypeID
    WHERE optionID = @optionID

COMMIT TRANSACTION
END
ELSE
BEGIN
SET @identity = (SELECT MAX(optionID) + 1 FROM tblWizardTemplateOptions)
if (@identity > 0)
BEGIN
BEGIN DISTRIBUTED TRANSACTION
    INSERT INTO tblWizardTemplateOptions (optionID, optionTypeID, optionHTML) VALUES (@identity, @optionTypeID, @optionHTML)

    INSERT INTO [dyna-sql].Ebis_Prod.dbo.tblWizardTemplateOptions (optionID, optionTypeID, optionHTML) VALUES (@identity, @optionTypeID, @optionHTML)

    INSERT tblWizardTemplateWebControlOptions (planID, templateID, webControlID, optionID)
        VALUES (@planID, @templateID, @controlID, @identity)

    INSERT [dyna-sql].Ebis_Prod.dbo.tblWizardTemplateWebControlOptions (planID, templateID, webControlID, optionID)
        VALUES (@planID, @templateID, @controlID, @identity)  

COMMIT TRANSACTION
END
END

GO

The big problem with distributed queries[-][- -][++]

One large problem with distributed queries is that if the query fails, the auto increment key is not reverted back \
to it's original number. For example, if a query inserts into serverA, but not into serverB, upon the next insert \
serverA will have an ID of 1 greater than serverB. If you are trying to keep to tables perfectly in sync, as was the \
case with a project I had, you should not use auto increment keys. Instead, use GUIDs or do a MAX(priKey) +1 as I \
did in the Example Procedure directly above. If the query fails, all data will be rolled back, and since there are no \
primary keys, all data will be in sync.

One other problem with distributed queries. The @@IDENTITY variable will NOT be returned from a remote server query.

Example:

Code Snippet
INSERT INTO [localserver1].db.dbo.table1 'HI'
SELECT @@IDENTITY

will work fine, @@IDENTITY will contain the newest auto increment inserted, but
Code Snippet
INSERT INTO [remoteserver1].db.dbo.table1 'HI'
SELECT @@IDENTITY

will NOT work, @@IDENTITY will be NULL or 0.

Which is another reason why you should NOT use primary keys with distributed transactions.