1 Reply Latest reply: Nov 10, 2011 8:36 AM by Gernot A. Weber RSS

MSSQL 2008 DB Replication Fails During Upgrade of ITSM Forms

Gernot A. Weber

Hi all,

 

for one of our customers I've setup db-replication (initial snapshot with transactional replication once a day) of the ITSM-Suite to another MSSQL-server, where reporting with Analytics and BO is done. In principle, the reporting relevant information needs to be replicated only. However, I've setup to deal with all existing tables and view. All went fine, until I tried to patch some forms on the productive machine. It turned out, that during the upgrade some views need to be dropped, which was forbidden by the SQL-server, as it would mess up the replication:

 

<SQL > <TID: 0000000888> <RPC ID: 0009280649> <Queue: Admin     > <Client-RPC: 390600   > <USER: ewzadmin                                     > /* Mi Okt 13 2010 19:02:05.4790 */DROP VIEW T1675
<SQL > <TID: 0000000888> <RPC ID: 0009280649> <Queue: Admin     > <Client-RPC: 390600   > <USER: ewzadmin                                     > /* Mi Okt 13 2010 19:02:05.4830 */*** ERROR ***  Cannot drop the view 'T1675' because it is being used for replication. (SQL Server 3724)
<SQL > <TID: 0000000888> <RPC ID: 0009280649> <Queue: Admin     > <Client-RPC: 390600   > <USER: ewzadmin                                     > /* Mi Okt 13 2010 19:02:05.4830 */DROP VIEW H1675
<SQL > <TID: 0000000888> <RPC ID: 0009280649> <Queue: Admin     > <Client-RPC: 390600   > <USER: ewzadmin                                     > /* Mi Okt 13 2010 19:02:05.4980 */*** ERROR ***  Cannot drop the view 'H1675' because it is being used for replication. (SQL Server 3724)
<SQL > <TID: 0000000888> <RPC ID: 0009280649> <Queue: Admin     > <Client-RPC: 390600   > <USER: ewzadmin                                     > /* Mi Okt 13 2010 19:02:05.4980 */CREATE VIEW H1675 AS SELECT * FROM H1660
<SQL > <TID: 0000000888> <RPC ID: 0009280649> <Queue: Admin     > <Client-RPC: 390600   > <USER: ewzadmin                                     > /* Mi Okt 13 2010 19:02:05.5010 */*** ERROR ***  There is already an object named 'H1675' in the database. (SQL Server 2714)

 

or:

 

<SQL > <TID: 0000000888> <RPC ID: 0009280649> <Queue: Admin     > <Client-RPC: 390600   > <USER: ewzadmin                                     > /* Mi Okt 13 2010 19:01:27.4280 */OK
<SQL > <TID: 0000000888> <RPC ID: 0009280649> <Queue: Admin     > <Client-RPC: 390600   > <USER: ewzadmin                                     > /* Mi Okt 13 2010 19:01:27.4280 */UPDATE vui SET vuiName=N'NewDefault Admin View',locale=NULL,vuiType=1,owner=N'Remedy',lastChanged=N'ewzadmin',timestamp=1286989287 WHERE schemaId=2084 AND vuiId=300852113
<SQL > <TID: 0000000888> <RPC ID: 0009280649> <Queue: Admin     > <Client-RPC: 390600   > <USER: ewzadmin                                     > /* Mi Okt 13 2010 19:01:27.4290 */OK
<SQL > <TID: 0000000888> <RPC ID: 0009280649> <Queue: Admin     > <Client-RPC: 390600   > <USER: ewzadmin                                     > /* Mi Okt 13 2010 19:01:27.4400 */UPDATE field_dispprop SET label = NULL,propShort = NULL WHERE schemaId = 2084 AND fieldId = 0 AND vuiId = 300852113
<SQL > <TID: 0000000888> <RPC ID: 0009280649> <Queue: Admin     > <Client-RPC: 390600   > <USER: ewzadmin                                     > /* Mi Okt 13 2010 19:01:27.4410 */OK
<SQL > <TID: 0000000888> <RPC ID: 0009280649> <Queue: Admin     > <Client-RPC: 390600   > <USER: ewzadmin                                     > /* Mi Okt 13 2010 19:01:27.4420 */SELECT propLong FROM field_dispprop WHERE schemaId = 2084 AND fieldId = 0 AND vuiId = 300852113
<SQL > <TID: 0000000888> <RPC ID: 0009280649> <Queue: Admin     > <Client-RPC: 390600   > <USER: ewzadmin                                     > /* Mi Okt 13 2010 19:01:27.4430 */OK
<SQL > <TID: 0000000888> <RPC ID: 0009280649> <Queue: Admin     > <Client-RPC: 390600   > <USER: ewzadmin                                     > /* Mi Okt 13 2010 19:01:27.4430 */Setting LOB into the above row ...
<SQL > <TID: 0000000888> <RPC ID: 0009280649> <Queue: Admin     > <Client-RPC: 390600   > <USER: ewzadmin                                     > /* Mi Okt 13 2010 19:01:27.4810 */* WARNING * The statement has been terminated.Length of LOB data (93060) to be replicated exceeds configured maximum 65536. (SQL Server 7139)
<SQL > <TID: 0000000888> <RPC ID: 0009280649> <Queue: Admin     > <Client-RPC: 390600   > <USER: ewzadmin                                     > /* Mi Okt 13 2010 19:01:27.4820 */ROLLBACK TRANSACTION

 

Of course, removing the replication in advance of starting the upgrade process and re-initializing afterwards would be a solution. However, this is not an option, as sometimes the process of deleting the subscription on the publisher is a little bit bumpy and furthermore, re-initializing takes quite some time, whereas the (productive-)db may not answer any requests...

 

Are there any options for the replication, which allow to execute all types of db manipulation on the publisher node? Any other suggestions to resolve the problem? I would like to avoid the DSO option...

 

Thanks in advance.

 

Cheers,

 

Gernot