Chris: What kind of "replication" did your DBA set up? We have a customer looking at this type of configuration in order to create a reporting server. They're deciding between either transactional replication with a read-only consumer or a mirrored database with snapshots. BTW, we did some testing a few years ago with SQL Server 2000 and transactional replication with read-write consumers, and that failed miserably because SQL Server added columns to every table to be replicated, which of cource confused arserver.exe terribly. Thanks, --Phil
----- Original Message ---- From: "Moore, Christopher Allen" <[EMAIL PROTECTED]> To: arslist@ARSLIST.ORG Sent: Thursday, June 5, 2008 9:56:53 PM Subject: Cautionary tale? SQL Replication service and Remedy ** Hey everyone- Has anyone had any experience using the SQL replication ability built into 2005? I don’t know much about it- apparently it’s a function which looks at transaction logs and uses them to create and maintain a separate instance of a DB. Our DBA was planning to use it to create a separate DB for reporting. He turned it on last Friday and now our database is for all intents and purposes unusable and we’re on the verge of restoring from a backup created last Thursday and losing a weeks worth of work- salvaging what we can of course. I’m not a DB expert, but the problem seems to be that the replication service somehow keeps the schema table from updating correctly, so Remedy is looking for tables/views that are either incorrect or don’t exist. The first manifestation of a problem came Monday when creating a field using the class manager. The change created an error with the message ‘An object named H208 already exists on the server’. The next day when people tried to update that CI they got an error that C########## was an invalid column name, and the number corresponded to the field ID I had tried to create. We could search on the form, but not modify any records. Looking at the base forms in the admin tool revealed that the field was indeed created. I called BMC support at this point (having no idea the replication service could be to blame). There was no table H208, but there was a view H208. He had me delete the pending change and told me to open the forms in the admin tool to delete the field. Trying to do so on the base regular form gave the error that I couldn’t delete it because it was referenced by a join form. Trying to remove it from the join gave me the same ‘Object named H208 already exists on the server’ error. BMC support seemed stumped. I made a simple change on a different form on the dev system and got a string of warnings along the lines of “Field : Closed Date: Creation of one of the SQL views for the form failed within the SQL database.” I then contacted the DBA to ask if his change the previous Friday could have possibly done anything and he was sure it could not have. I tried adding a field via the class manager on the dev server to test it and got the same error (object H208). I had the DBA remove the replication and restarted the server and tried again- no error. The replication service somehow screws up the database on form changes. Now we knew the cause but the fix was the problem- for several days we had been unable to update information in our computer systems form. Then today it got much worse. A caller had been getting an error when trying to open a form in search mode- ARERR300 MALLC error. A second user reported it so we decided to restart the server to free up memory (fortunately it was already after hours). Once the server came back up and we logged in we got a new error- the home page would load but the application list was empty and we got an error 1900- could not set application list. For the users of the client this wasn’t insurmountable- they can use the object list; however 90% of our users only use the web, so they get to the home page and are stuck. Additionally, tickets of a high or critical priority cannot be created as such- they much be put in as a 3 or 4 then saved then moved up to a 1 or 2. Also P1 and P2 tickets cannot be resolved- some of the work we’ve done requires a P2+ to be related to a CI on resolution and that no longer works. I called BMC back and have been getting bounced around the world’s call centers. They’ve requested multiple logs, but I’m not very confident they will have a fix. Tomorrow is going to be pretty exciting… Anyone heard anything about this sort of thing happening with the replication service in SQL? Thanks, Chris __Platinum Sponsor: www.rmsportal.com ARSlist: "Where the Answers Are" html___ _______________________________________________________________________________ UNSUBSCRIBE or access ARSlist Archives at www.arslist.org Platinum Sponsor: www.rmsportal.com ARSlist: "Where the Answers Are"
