Any SQL DBA's? SQL2005 replication question

Chat Forum

Any SQL DBA's? SQL2005 replication question

5 posts & 4 voices | Started 10 months ago by enfht | Latest reply from xiphon

Tags:

No tags yet.

enfht - Member

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!

Posted 10 months ago #
fennesz - Member

1) create the database on the subscriber
2) how can sa have domain permissions? it's a SQL Server login

Posted 10 months ago #
enfht - Member

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

Posted 10 months ago #
ourkidsam - Member

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.

Posted 10 months ago #
xiphon - Member

We use Redgate Tools SQL Compare to duplicate our DBs.

Might be of some use?

Posted 10 months ago #

RSS feed for this topic

Reply

You must log in to post.

Singletrack Mountain Bike Magazine

Chat Forum

Any SQL DBA's? SQL2005 replication question

Reply

Don't miss…

Singletrack

Website & Forums

Exits