Need to set up transactional replication to a second machine on the same domain/LAN/subnet
- I've created the local publication and generated the snapshot
- When I'm adding the subscriber and choosing "new subscription db" it won’t create the copy db on the subscriber machine and throws up a generic CREATE DATABASE failed message
- I'm using full domain admin credentials throughout, the SA also uses full domain admin permissions.
Problem I have is I can’t find any related info on the net and the only guy with SQL experience here isn’t proving to be much help. I can set the subscriber up if I point to a locally created db but of course this doesn’t match when the replica data arrives. It's all W2K3 R2 and SQL2005 SP2
Any advice or KB articles would be really useful to me!
1) create the database on the subscriber
2) how can sa have domain permissions? it's a SQL Server login
It can't write the db on the subscriber. I suggested restoring a copy of the db from backup but the SQL guy here says "the first thing that a subscriber service needs is a snapshot (backup) of the publisher database. The publisher creates this snapshot as a baseline from which it start logging changes. So, restoring a copy of the db onto the replica machine won’t help as replication will simply ignore it and start again"
I don't know enough to say whether he's correct or not..
You're correct the SA has it's own password. All required domain credentials are full domain admin
I've never done what you're talking about (publisher/subscription) but if the new db is on a separate instance, have you done the surface area configuration on that instance to allow connections?
Also, have you checked Windows event viewer for a better description of what the error is?
Can't think of much more past that though as like I say, not anything I've done before.
We use Redgate Tools SQL Compare to duplicate our DBs.
Might be of some use?