Replication scenario - seeking suggestion

I have two sites. Site A and Site B

Each site has two databases

Site A

Db1

Db2

Site B

Db1

Db2

Site A Db1 has to perform transaction replication to Site A- Db2 and Site B- Db1 and Db2.

I started Site A as pubisher and distributor and Site A and Site B both as subscriber.

Site B is in a different geographical area (state).

-

Please suggest the best scenario to save bandwidth and server load for Publisher, and Distributor.

-

Earlier I thought that I will implement local replication between Site B - in between Db1 and Db2. The Sql Server does not let me set Db1 as publisher, and distributor for its local database Db2.

-

P.S. My all databases need same transactions though they are connected to different hardware at different places. So please don't question that why I need four similar databases.

You can publish to Db1 then use Db1 as a republisher to publisher to Db2.

See in Books Online: ms-help://MS.SQLCC.v9/MS.SQLSVR.v9.en/rpldata9/html/a1485cf4-b1c4-49e9-ab06-8ccfaad998f3.htm

Martin

|||

Thank you!!

looks good.

For Site A - Db1

publisher, distributor and subsriber(Site A- Db2)

Db 2

Publisher Site A

Distributor Site B

Subscriber Db1

Subscriber Db2

I think, this is what you are suggesting.

|||

My suggestion would have the distributor on the same machine so:

A - DB 1 (master publisher)

A - DB 2 (subscriber)

B - DB 1 (subscriber , republisher)

B - DB 2 (subscriber (to B - DB 1)

Martin

|||

Thank you!!

I never tried republisher, I am running Sql Server 2000.

Let me read it, if I will have any question then I will get back.

Moreover, I could not access that help, this does not work from my computer.

This is exactly I would prefer, because otherwise it seems stupid to send data twice to the other site.

|||

Oh this is for SQL 2005 only. You need to install the SQL2005 books online to view the help link.

Martin

|||Thank you!

Replication problem after reduce no of columns to less than 254

There was a replication problem between db1 and db3. The scenario is that a
table was added with more than 254 nos. of columns. An error
[Error 2757: RAISERROR failed due to invalid parameter substitution(s) for
error 20068, severity 16, state 1]
is prompted during the creation of publication for the replication.
Therefore, the new added columns are deleted. However, there is another
error [Error 220: Arithmetic overflow error for data type tinyint, value =
256.]
is prompted when the publication for the replication was created again.
How to fix this error?
David NG
can I see the schema for this reduced table? How many columns did it have
originally?
http://www.zetainteractive.com - Shift Happens!
Looking for a SQL Server replication book?
http://www.nwsu.com/0974973602.html
Looking for a FAQ on Indexing Services/SQL FTS
http://www.indexserverfaq.com
"David NG" <David NG@.discussions.microsoft.com> wrote in message
news:384758F8-4985-4566-B8C0-7532A0C7DFC3@.microsoft.com...
> There was a replication problem between db1 and db3. The scenario is that
> a
> table was added with more than 254 nos. of columns. An error
> [Error 2757: RAISERROR failed due to invalid parameter substitution(s) for
> error 20068, severity 16, state 1]
> is prompted during the creation of publication for the replication.
>
> Therefore, the new added columns are deleted. However, there is another
> error [Error 220: Arithmetic overflow error for data type tinyint, value =
> 256.]
> is prompted when the publication for the replication was created again.
> How to fix this error?
> David NG
>

Replication problem

Hi,
I have two different servers Server1 and Server2. I had a database DB1 on Server1 which I restored onto the server Server2 with the same name DB1. Then I deleted the database DB1 from the server Server1. Now when I try to establish snapshot replication of DB1 from Server2 onto the Server1, I'm getting the following error.

"A connection could not be established to Server1 - Cannot open default database '<ID>'. Using master database instead.
Please verify SQL server running and check your SQL server registration properties (by right-clicking on the Server1 node) and try again."
DB1 was the default database on Server1 which I deleted after restoring the same onto Server2.
Now I'm not even able to connect to server1.

I'm using the user SA.

Thanks in advance.
Regards,
P.C. VaidyanathanYou must remember that when you copy a database from one server to another while users UIDs may be copied across inside the databse, they have the same name in the database and on the new server .....BUT...... the user ids are a match in name ONLY!!

Each user ID has an SUID ( 24 bit hex value I think ) that is unique for each UID on the server and in the copied over database. So, to make the UIDs line up in the databse with UIDs already defined on the new server, you need to run a special stored procedure called sp_change_users_login ( see books on line (BOL)) to map the UIDs so at they match on the UIDs. This maps the SUIDs such that UID "testuser" on your server matches UID "testuser" in the copied over database.

I have done this serveral times with minimal hassles.

Try this first, & see how you go. Post back if problems.

Cheers

J.