can this b done by replication ?

hi all,
I have a single sql server, and two databases with similar
table names and structures for some 10-15 tables.
I want replicate the data from one db to another to keep
them in sync.
Can I do this using replication ?
Is replication possible between two db's of a single
server ?
I tried already it gives an error 18483 could not connect
to server cause distributor_admin is not defined as a
remote login at the server ?
thanks in advance
You can replicate to the same server. TO fix your distributor_admin problem
disable replication and re enable it.
This normally fixes this problem.
Hilary Cotter
Looking for a SQL Server replication book?
Now available for purchase at:
http://www.nwsu.com/0974973602.html
"San" <anonymous@.discussions.microsoft.com> wrote in message
news:007201c4dc8a$e52018a0$a401280a@.phx.gbl...
> hi all,
> I have a single sql server, and two databases with similar
> table names and structures for some 10-15 tables.
> I want replicate the data from one db to another to keep
> them in sync.
> Can I do this using replication ?
> Is replication possible between two db's of a single
> server ?
> I tried already it gives an error 18483 could not connect
> to server cause distributor_admin is not defined as a
> remote login at the server ?
> thanks in advance

can sqlsvr2005 replicate tables to sqlsvr2000?

Hello,
I have replication in place between 2 sqlsvr2000 DBs. Everything is working
OK. I am looking to upgrade the publishing sqlsvr from sqlsvr2000 to
sqlsvr2005. The subscription sever will remain as a sqlsvr2000 server
because I don't own that one. If I were to go ahead an perform the upgrade
on my end, will I still be able to replicate my table to the sqlsvr2000
subscriber?
Thanks,
Rich
Absolutely. The only caveat is that if you are using merge replication the
snapshot will have to be sent again.
Hilary Cotter
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
"Rich" <Rich@.discussions.microsoft.com> wrote in message
news:DEE16A4E-CCF9-4D6A-9A4C-4F4A69FDBD24@.microsoft.com...
> Hello,
> I have replication in place between 2 sqlsvr2000 DBs. Everything is
> working
> OK. I am looking to upgrade the publishing sqlsvr from sqlsvr2000 to
> sqlsvr2005. The subscription sever will remain as a sqlsvr2000 server
> because I don't own that one. If I were to go ahead an perform the
> upgrade
> on my end, will I still be able to replicate my table to the sqlsvr2000
> subscriber?
> Thanks,
> Rich
|||Thanks. The folks over my way are a little reluctant to move forward with
the upgrade - but hey! We have an MSDN subscription and I already downloaded
all of sqlsrv2005 enterprise. They were trying to use the Replication angle.
Glad that won't be an issue.
Rich
"Hilary Cotter" wrote:

> Absolutely. The only caveat is that if you are using merge replication the
> snapshot will have to be sent again.
> --
> Hilary Cotter
> 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
> "Rich" <Rich@.discussions.microsoft.com> wrote in message
> news:DEE16A4E-CCF9-4D6A-9A4C-4F4A69FDBD24@.microsoft.com...
>
>

Can not replicate DDL changes

Merge replication. We switched a publication over from push to pull and are now initiating everything within an application. We have just encountered a situation where it is now completely impossible to replicate DDL.

When this was a push subscription, we could execute the following and it would fly straight through the engine and hit every subscriber without having to do anything at all:

ALTER TABLE <tablename>

ADD <columnname> <datatype> NULL

Now that it is a pull subscription when I issue an ALTER TABLE and add a nullable column to the end of the table, it does NOT replicate at ALL. We get the following error message:

The schema definition of the destination table 'dbo'.'Player' in the subscription database does not match the schema definition of the source table in the publication database. Reinitialize the subscription without a snapshot after ensuring that the schema definition of the destination table is the same as the source table. (Source: MSSQL_REPL, Error number: MSSQL_REPL-2147199478)
Get help: http://help/MSSQL_REPL-2147199478

The process was successfully stopped. (Source: MSSQL_REPL, Error number: MSSQL_REPL-2147199481)
Get help: http://help/MSSQL_REPL-2147199481

It apears that we are now required to either reinitialize every subscriber every time we add a column or we are required to first distribute the DDL change to each subscriber, make sure they all have it, then add it to the publisher before anyone replicates, and then reinit every single one of them without a snapshot. This makes absolutely no sense at all.

The interesting thing is that we can add articles at will and those get applied with absolutely no problems at all to the subscribers without having to do anything other than add the article and generate a new snapshot.

Version 9.00.3042.00

I am experiencing the same problem. It appears to only occur if there are both data and metadata changes to the replicated table. Has that been your experience?|||

No. We have this problem even if only DDL needs to replicate.

There is a KB artilcle of a bug with DDL replication that is related to kicking synchronization off via Replication Monitor, but that does not apply here either. We are initiating it using RMO from the client and have verified that it is not an RMO issue by directly launching it from the command line using replmerg.exe. We have the latest service pack and hotfixes applies. There is nothing that we can find that narrows this down at this point.

DDL plain and simply does not replicate. We have built this on a test environment using a centrally manage configuration, aka push, and all DDL replicates without any problems. We then dropped the push subscribers off and configured pull subscribers to this same publication and at that point DDL refuses to replicate via any mechanism.

The only way we can get DDL changes shoved down to a subscriber is to do one of the following:

1. Move the DDL change to a new table and add that as a new article

2. Drop the existing article from the publication, have everyone synch to remove it from their machines, apply the DDL change, add the article back in, have everyone synch to get the incremental snapshot and add the table back with the updated schema

3. Reinitialize all of the subscribers

Every one of those options is horrible, for obvious reasons when you are dealing with hundreds of subscribers moving all over the globe.

|||I am using push subscriptions, initializing the sync using RMO. I continue to see the problem when the subscriptions are set to reinitialize. The workaround supplied by the error message works but is impractical.|||We aren't reinitializing ours. They are just blowing up this way. In our case, you can't do what is suggested in the error message, because schema changes are not allowed on the subscriber.|||Is there any official response to this problem?|||Nope. Since we can't even get the support case moving forward, we are taking the alternative approach of blowing away each of the publications, issuing the DDL that we need, putting the publication back together and then reinitializing. We've reverted our application design and deployment to what I've been using for the past decade, prior to SQL Server 2005. I no longer trust the DDL replication component and will not use it. This creates a major bottleneck in our ability to deliver solutions quickly as well as enhance existing solutions, but we are left with no choice.

Can not replicate DDL changes

Merge replication. We switched a publication over from push to pull and are now initiating everything within an application. We have just encountered a situation where it is now completely impossible to replicate DDL.

When this was a push subscription, we could execute the following and it would fly straight through the engine and hit every subscriber without having to do anything at all:

ALTER TABLE <tablename>

ADD <columnname> <datatype> NULL

Now that it is a pull subscription when I issue an ALTER TABLE and add a nullable column to the end of the table, it does NOT replicate at ALL. We get the following error message:

The schema definition of the destination table 'dbo'.'Player' in the subscription database does not match the schema definition of the source table in the publication database. Reinitialize the subscription without a snapshot after ensuring that the schema definition of the destination table is the same as the source table. (Source: MSSQL_REPL, Error number: MSSQL_REPL-2147199478)
Get help: http://help/MSSQL_REPL-2147199478

The process was successfully stopped. (Source: MSSQL_REPL, Error number: MSSQL_REPL-2147199481)
Get help: http://help/MSSQL_REPL-2147199481

It apears that we are now required to either reinitialize every subscriber every time we add a column or we are required to first distribute the DDL change to each subscriber, make sure they all have it, then add it to the publisher before anyone replicates, and then reinit every single one of them without a snapshot. This makes absolutely no sense at all.

The interesting thing is that we can add articles at will and those get applied with absolutely no problems at all to the subscribers without having to do anything other than add the article and generate a new snapshot.

Version 9.00.3042.00

I am experiencing the same problem. It appears to only occur if there are both data and metadata changes to the replicated table. Has that been your experience?|||

No. We have this problem even if only DDL needs to replicate.

There is a KB artilcle of a bug with DDL replication that is related to kicking synchronization off via Replication Monitor, but that does not apply here either. We are initiating it using RMO from the client and have verified that it is not an RMO issue by directly launching it from the command line using replmerg.exe. We have the latest service pack and hotfixes applies. There is nothing that we can find that narrows this down at this point.

DDL plain and simply does not replicate. We have built this on a test environment using a centrally manage configuration, aka push, and all DDL replicates without any problems. We then dropped the push subscribers off and configured pull subscribers to this same publication and at that point DDL refuses to replicate via any mechanism.

The only way we can get DDL changes shoved down to a subscriber is to do one of the following:

1. Move the DDL change to a new table and add that as a new article

2. Drop the existing article from the publication, have everyone synch to remove it from their machines, apply the DDL change, add the article back in, have everyone synch to get the incremental snapshot and add the table back with the updated schema

3. Reinitialize all of the subscribers

Every one of those options is horrible, for obvious reasons when you are dealing with hundreds of subscribers moving all over the globe.

|||I am using push subscriptions, initializing the sync using RMO. I continue to see the problem when the subscriptions are set to reinitialize. The workaround supplied by the error message works but is impractical.|||We aren't reinitializing ours. They are just blowing up this way. In our case, you can't do what is suggested in the error message, because schema changes are not allowed on the subscriber.|||Is there any official response to this problem?|||Nope. Since we can't even get the support case moving forward, we are taking the alternative approach of blowing away each of the publications, issuing the DDL that we need, putting the publication back together and then reinitializing. We've reverted our application design and deployment to what I've been using for the past decade, prior to SQL Server 2005. I no longer trust the DDL replication component and will not use it. This creates a major bottleneck in our ability to deliver solutions quickly as well as enhance existing solutions, but we are left with no choice.