April 7, 2010 at 11:29 am
We have a production environment with a single SQL2005 Standard publisher/distributor and several hundred SQL2005Express subscribers. We are trying to create an equivalent development environment, but are running into difficulties due to the fact that the dev server must have a different name (It's on the same domain as the production server).
Using backups of the production databases, we have restored master, msdb, distribution and the user database to the new box. We have changed the server name within SQL (SELECT @@servername correctly identifies the new box).
We scripted the publication from the production server and modified the script so that the new box is identified, but when we run it, we get a vast quantity of warnings telling us that information "already exists in the vertical partition." A publication gets created, but with all these warnings, we doubt its validity. Additionally, we are unable to create the initial snapshot from this publication.
We have tried dropping replication and distribution and then recreating the distributor through the GUI to no avail.
We seek a "best practices" method of copying/restoring a merge replicated environment to a dev system that has a different server name.
April 7, 2010 at 11:32 am
In your dev environment, will you be merging from the dev server to another dev server? Or are you trying to merge with another database on the same dev server, or with the second server that is in production?
More details on your setup would be extremely helpful.
Jason...AKA CirqueDeSQLeil
_______________________________________________
I have given a name to my pain...MCM SQL Server, MVP
SQL RNNR
Posting Performance Based Questions - Gail Shaw[/url]
Learn Extended Events
April 7, 2010 at 11:38 am
Thanks for the fast reply, Jason.
We are trying to create a dev environment that is essentially a copy of our production environment. The two systems will not communicate with each other. The only difference would be the machine names. We have been trying to restore the databases from the production environment to the dev environment, but we cannot seem to generate a viable publication on the dev box.
April 7, 2010 at 11:49 am
I would take the production database from your primary server and restore the backup of that database to the dev server. Make sure to rip out any replication related pieces.
Then start from there as if you were creating a fresh publication and merge replication. All the way back at square one like you did for the production server.
This means taking a backup of the dev database and restoring it to the second dev server, then restoring tlogs, and then setting up the merge replication.
If I am guessing right, you restored the databases from both merge servers to the new dev merge servers. That may be where SQL Server is having the conflict.
Jason...AKA CirqueDeSQLeil
_______________________________________________
I have given a name to my pain...MCM SQL Server, MVP
SQL RNNR
Posting Performance Based Questions - Gail Shaw[/url]
Learn Extended Events
April 7, 2010 at 12:22 pm
I guess I wasn't specific enough.
We have one production server acting as its own distributor. This server has one publication and uses merge replication to upload/download data to/from aproximately 300 subscribers.
Using the backups from the production server, we have restored the databases to our development server. We have ensured that the dev box has the same drive letters as the production box so that we do not have to use any MOVE commands.
We have performed the following tasks within SQL:
--T-SQL to remove replication objects from production
USE userdb
EXEC sp_removedbreplication 'userdb'
Go
--T-SQL to drop publisher and drop distribution
Use master
Exec sp_dropdistpublisher 'production'
Exec sp_dropdistributiondb 'distribution'
Exec sp_dropdistributor
Go
--T-sql to change Server Name
use master
Exec sp_dropserver 'production','droplogins'
go
Exec sp_addserver 'Dev'
go
Restart services
Select @@servername {returns 'Dev'}
Configure distribution using the Configure Distribution Wizard, ensuring that paths are correct.
Script publication from production server (R-Click publication and choose generate scripts)
Copy generated script to dev box and modify, changing the server name from 'Production' to 'Dev' wherever it appears, and setting the correct password for the replication service account.
When we run the script, the publication is generated, but we get a long laundry list of warnings that information already exists in the vertical partition. The most common column listed in these warnings is 'autoid.'
When we attempt to create the initial snapshot from this publication, it hangs at 32% with a message that it is "waiting for a reply from the server." Eventually, it stops completely.
April 7, 2010 at 12:27 pm
Is the new "dev" server using the same distributor as the "production"?
If not, then overall you seem to be following the correct path. I wonder why would there be leftover data about vertical partitions even after running sp_removedbreplication. Can you confirm if the publication is a plain vanilla or has some partitioning (filtering) set?
April 7, 2010 at 12:28 pm
Using the backups from the production server, we have restored the databases to our development server >> have you restored the distribution database as well from "prod" into "dev"?
April 7, 2010 at 12:53 pm
The dev server is acting as its own distributor, just as the production server does.
April 7, 2010 at 12:53 pm
Yes, the distribution database was restored onto the dev server.
April 7, 2010 at 12:55 pm
The publication contains approximately 200 filtered articles.
April 7, 2010 at 1:52 pm
Yes, the distribution database was restored onto the dev server.
>> This might explain the leftover things and the error messages you are getting.[p][/p]
Read the notes from CirquedeSoliel:
"Then start from there as if you were creating a fresh publication and merge replication. All the way back at square one like you did for the production server."
[p][/p]
So, you should not be restoring the distribution database from production server into dev server since you will have to setup replication afresh here (yes using the same scripts, but since the servers are different, this will be a new setup).
[p][/p]
The action plan for you would be:
> Drop the existing publication and run sp_removedbreplication on dev server
> Run sp_dropdistribution to clean the distributor from dev server
> Drop the distribution database (which you restored from prod)
> Run the publisher and distribution setup wizard to setup distribution afresh. This will recreate the distribution database afresh.
> Create publication from scripts
> Run snapshot agent.
April 7, 2010 at 1:58 pm
mangeshd (4/7/2010)
The action plan for you would be:> Drop the existing publication and run sp_removedbreplication on dev server
> Run sp_dropdistribution to clean the distributor from dev server
> Drop the distribution database (which you restored from prod)
> Run the publisher and distribution setup wizard to setup distribution afresh. This will recreate the distribution database afresh.
> Create publication from scripts
> Run snapshot agent.
Thanks for following up with that list of steps.
Jason...AKA CirqueDeSQLeil
_______________________________________________
I have given a name to my pain...MCM SQL Server, MVP
SQL RNNR
Posting Performance Based Questions - Gail Shaw[/url]
Learn Extended Events
April 7, 2010 at 2:20 pm
One question:
You specified "Run the publisher and distribution setup wizard to setup distribution afresh. This will recreate the distribution database afresh."
By this, do you actually mean two separate wizards? I am familiar with the "Configure Distribution Wizard" but I have not seen a publisher setup wizard. In our case, the publisher is also the distributor.
Before we take another crack at this, I'd like to be certain I am following your steps fully.
April 7, 2010 at 2:27 pm
"Configure Distribution Wizard" is what I meant for setting up distribution (and distribution db). 😀
You can create distribution from scripts (sp_Adddistributor and sp_adddistributiondb) as well but if you are a GUI lover, you can use wizards.
Viewing 14 posts - 1 through 13 (of 13 total)
You must be logged in to reply to this topic. Login to reply