Replication

•Check flag RangeUId in both web.config on both server (publisher & subscriber)

Check database under replication

This will give you a table containing database and replication information on it.

Displays information about a Subscriber. This stored procedure is executed at the Publisher on any database.

Remove replication on database

This stored procedure is executed at the Publisher on the publication database or at the Subscriber on the subscription database. The procedure removes all replication objects from the database in which it is executed, but it does not remove objects from other databases, such as the distribution database.

sp_mergesubscription_cleanup [ @publisher =] 'publisher', [ @publisher_db =] 'publisher_db', [ @publication =] 'publication'

Removes metadata, such as triggers and entries, in sysmergesubscriptions and sysmergearticles after the specified merge push subscription is removed at the Publisher. This stored procedure is run at the Subscriber on the subscription database.

Drops a subscription to a merge publication and its associated Merge Agent. This stored procedure is executed at the Publisher on the publication database.

Drops subscriptions to a particular article, publication, or set of subscriptions on the Publisher. This stored procedure is executed at the Publisher on the publication database.

Conflicts

If replication (de)activation fails on TSS, you can use conflict viewer of SSMS. Go to object explorer > Replication > Local Publication > Right click on publication > View conflict

Press ‘Select All’ button and then ‘Remove’ button to remove all conflict.

To open replication monitor, right click on Replication (SQL Server management studio > Object Explorer) and click on Launch replication monitor.

Dead sessions

When turret switch from primary TSS to secondary TSS, the ping is not done anymore on primary server – meaning that dead session can appear as dead if you consult information about these terminal on primary server. Same sessions are shown as alive if you consult secondary server. It can be problematic if you user ‘Clear dead session’ on primary server – it will delete sessions that are not really dead but simply not yet replicated to primary server.

To prevent this, increase time of ping interval on TSS (session.validity.interval) to a higher value than maximum interval of replication. Do not forget to adapt ping interval of terminal to be coherent with TSS ping interval.

Stored procedure

Following stored procedure can be used to fix problems regarding SQL replication (incorrect computer name, incorrect network name, ..):

sp_helpserver: Reports information about a particular remote or replication server, or about all servers of both types. Provides the server name, the network name of the server, the replication status of the server, the identification number of the server, and the collation name. Also provides time-out values for connecting to, or queries against, linked servers. http://msdn.microsoft.com/en-us/library/ms189804.aspx

SELECT @@SERVERNAME: Returns the name of the local server that is running SQL Server. http://msdn.microsoft.com/en-us/library/ms187944.aspx

sp_dropserver 'SERVER_NAME': Removes a server from the list of known remote and linked servers on the local instance of SQL Server. http://msdn.microsoft.com/en-us/library/ms174310.aspx

sp_addserver 'SERVER_NAME': Defines the name of the local instance of SQL Server. This stored procedure also defines a remote server. http://msdn.microsoft.com/en-us/library/ms174411.aspx

sp_setnetname @server = 'server', @netname = 'network_name': Sets the network names in sys.servers to their actual network computer names for remote instances of SQL Server. This procedure can be used to enable execution of remote stored procedure calls to computers that have network names containing SQL Server identifiers that are not valid. http://msdn.microsoft.com/en-us/library/ms190318.aspx

sp_dropmergesubscription @subscriber= 'subscriber' : Delete subscriber. http://technet.microsoft.com/en-us/library/ms176045.aspx

The 'OriginalMachineName' value can be found in the registry on this location: 'HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\Microsoft SQL Server\90\Machines' shows SQL2K5SWSRV.

Exception scenarios

Scenarios

System behavior

Duplicate primary key exception

You are using Console/TSS and you create an entity (device, administrator, user, shortcut…). When you click ‘Add’ button you get the exception ‘Duplicate Key Exception’.

The secondary TSS is probably not well configured as secondary.

- Check about screen to get replication status.

- In audits traces, check if current tss is secondary with this trace : “Application Console started on xxx.xxx.xxx.xxx (secondary server)”

- in web.config of both application (TSS and Console) check tag ReplicationUId is marked as secondary (undefined by default)

- Restart IIS

SQL command TRUNCATE table does not work anymore

You try to use SQL command TRUNCATE table but you get an error message: “Cannot truncate table 'SYS_AUDIT' because it is published for replication.”

Truncate command does not create transaction log. If no transaction log, there is no replication that is possible. This limitation is normal. Deactivate replication or use command ‘DELETE FROM…’ are work around.