Hello,
I have a SQL 2000 database which stores details of consumers input via
our website.
I am currently trying to configure some off-site disaster recovery,
which includes synchronising our database (including tables, data and
stored procedures) to another server.
We are limited on bandwith at the moment due to our office locations,
so ideally I need to keep the data transfers to a minimum. I am happy
for the transfer to take place overnight.
My question is, which replication model should I choose. I first
thought that Snapshot would be best, but the only problem is that the
configuration seems to indicate that it would drop and re-create each
table on the desitination server. This obvisously isn't very scalable,
as more data in the database will mean that the transfer wil take
progressively longer.
Is there a better way to do it? I guess the ideal way would be to only
copy the changes made, each night. Can I do this with transactional
replication?
Cheers,
Andrew
Yes - transactional replication will have the least impact on your limited
bandwidth, so toher things considered I'd choose this. I have an article on
replication times on www.replicationanswers.com that you might find helpful.
Cheers,
Paul Ibison SQL Server MVP, www.replicationanswers.com
(recommended sql server 2000 replication book:
http://www.nwsu.com/0974973602p.html)
|||Thanks Paul.
Will the storing of a whole day's transactions have any performance
implications on my SQL server?
|||Typically no, but if you find by doing some monitoring that the production
server is struggling, you can use a remote distributor. Before going to this
step, you can use pull subscriptions which'll offload some of the work.
Cheers,
Paul Ibison SQL Server MVP, www.replicationanswers.com
(recommended sql server 2000 replication book:
http://www.nwsu.com/0974973602p.html)
|||Thanks for the advice. I currently have the distribution on the
production server and am using push subscriptions, in order to make it
easier to administer the whole thing.
I will try your suggestions out if we run into problems.
Many thanks
Showing posts with label configure. Show all posts
Showing posts with label configure. Show all posts
Wednesday, March 28, 2012
Monday, March 26, 2012
Replication Setup Issues
While trying to configure replication from and to SQL Server 2000 boxes, I
encountered the following error, which I was not able to get past. The
error is encountered after completing the "Push Subscription Wizard" which
is set to create the database and initialize the schema and data. The
database gets created, but the schema does not.
Does anyone have advice on how to get past this error?
If not, I have questions further on regarding my test environment...
The process could not connect to Subscriber '<SERVERNAME>'.
Login failed for user '(null)'. Reason: Not associated with a trusted SQL
Server connection.
(Source: <SERVERNAME> (Data source); Error number: 18452)
I am connected using windows authentication, and I am an administrator on
both boxes. I can connect to either database and perform any action I want,
but I still keep getting this error. I have not found any way to specify a
login for replication, I assume the job runs either under my login or as SA.
I changed the scheduled task to be owned by SA, and attempted to run it as
both "Self" and "DBO", with no luck. I kept getting the same error. Some
of the IDs that exist in our production database do not exist in the
database we are replicating to, although SA exists, as does my account.
Normally I do not use Enterprise manager for anything and I script
everything out, but the only instructions I had were for EM. Would I be
better off using a script in QA for setting up replication?
Regarding my test environment...
Now, after spending a couple of hours on this error, I decided to go back to
my test environment (where everything worked the first time) and make it
look more like my production environment so I could do another round of
testing. I restored the master database and my 3 database which will be
replicated, but the backups were form different dates and I ran into all
kinds of conflicts. Essentially, SQL Server said the replication
definitions were invalid, and would not let me remove them. Next week, when
time allows, I will be getting a cold backup of the entire database to
attach to, hopefully that will get my test database in a state where I can
do another round of testing.
Am I on the right track with my test environment? What is the proper way to
copy a replication database, and remove the replication?
Any advice will be greatly appreciated.
Are you using sql server logins for the distribution agent, and the
subscriber is set to only allow windows (trusted) authentication? This could
be one cause of the error.
Cheers,
Paul Ibison SQL Server MVP, www.replicationanswers.com
|||Both of the databases are set up to allow both SQL Server and trusted
authentication. I confirmed by logging into each server as SA, just to be
sure.
I am, however, performing all of the setup using trusted authentication,
under my own account, which has administrative priviledges.
I never specified a logon for the distribution agent, however. Maybe that
is the problem? I was never actually prompted to do so, and was unable to
find where to specify it. I am the owner of the distribution agent
process, and the first process in the scheduled job is set to run as (Self),
so I assume it is running under my account. I tried changing this to DBA,
and I tried changing the owner to SA, neither made a difference.
"Paul Ibison" <Paul.Ibison@.Pygmalion.Com> wrote in message
news:exM%23TihUHHA.3652@.TK2MSFTNGP04.phx.gbl...
> Are you using sql server logins for the distribution agent, and the
> subscriber is set to only allow windows (trusted) authentication? This
could
> be one cause of the error.
> Cheers,
> Paul Ibison SQL Server MVP, www.replicationanswers.com
>
|||Jim,
just testing all posibilities here so pls can you check what the following
script returns at the subscriber.
Use Master
go
Select @.@.Servername
Cheers,
Paul Ibison SQL Server MVP, www.replicationanswers.com
|||Excellent catch. My test database comes back with the production database
name. The question is, how do I correct this, or how do I get the test
database to match my production database configuration?
"Paul Ibison" <Paul.Ibison@.Pygmalion.Com> wrote in message
news:ua103$CVHHA.4784@.TK2MSFTNGP03.phx.gbl...
> Jim,
> just testing all posibilities here so pls can you check what the following
> script returns at the subscriber.
> Use Master
> go
> Select @.@.Servername
> Cheers,
> Paul Ibison SQL Server MVP, www.replicationanswers.com
>
>
>
|||ok, I found this in BOL explaining how to change the server name. The
question is, will this be enough to get the restored database in a testable
state, or am I going about this the wrong way? Tonight I intend to take a
full cold backup from production and reattach to all the databases on test.
How to change to the current server name in the SQL Server 6.5 master
database (ISQL/w)
To change to the current server name in the SQL Server 6.5 master database
1.. Start Microsoft SQL ServerT in minimal configuration mode. In a
command prompt window, from the \Mssql\Binn directory, run:
sqlservr -f
2.. On the Start menu, point to Programs /Microsoft SQL Server 6.5, and then
click ISQL/w.
3.. Enter the sa password, and then click Connect.
4.. Execute SELECT @.@.SERVERNAME to retrieve the former server name.
5.. Execute sp_dropserver to drop the former server.
6.. Execute sp_addserver to add the current server.
7.. Stop SQL Server. In the command prompt window, press Ctrl+C.
8.. Restart SQL Server.
9.. Execute SELECT @.@.SERVERNAME to verify the current server name.
Examples
--Start SQL Server in minimal configuration mode.
--Retrieve the former server name.
SELECT @.@.SERVERNAME
--Drop the server returned from the previous select.
sp_dropserver 'SERVER6X'
--Add the current server.
sp_addserver 'SERVER70', local
--Stop SQL Server.
--Restart SQL Server in minimal configuration mode.
--Verify the current server name.
SELECT @.@.SERVERNAME
"Jim Underwood" <james.underwoodATfallonclinic.com> wrote in message
news:uDG$BKDVHHA.2212@.TK2MSFTNGP02.phx.gbl...[vbcol=seagreen]
> Excellent catch. My test database comes back with the production database
> name. The question is, how do I correct this, or how do I get the test
> database to match my production database configuration?
> "Paul Ibison" <Paul.Ibison@.Pygmalion.Com> wrote in message
> news:ua103$CVHHA.4784@.TK2MSFTNGP03.phx.gbl...
following
>
|||Jim,
this should be enough to get the subscriber set up. For step 6 you just have
to remember the 'local' switch.
Cheers,
Paul Ibison SQL Server MVP, www.replicationanswers.com
|||Paul, thank you for the assistance. I'll post back in a couple of days to
let everyone know how I made out.
I have added your site to my online SQL Server library. I have a feeling it
will come in useful.
Thanks again,
Jim
"Paul Ibison" <Paul.Ibison@.Pygmalion.Com> wrote in message
news:%23Lh$XgDVHHA.4756@.TK2MSFTNGP06.phx.gbl...
> Jim,
> this should be enough to get the subscriber set up. For step 6 you just
have
> to remember the 'local' switch.
> Cheers,
> Paul Ibison SQL Server MVP, www.replicationanswers.com
>
|||I was finally able to get my test environment up and running, after renaming
my server, deleting the entries in the distribution database, and updating
the server name in the msdb.dbo.jobs table.
The test environment replicated without any issue, so I do not know why I am
having trouble in production.
Maybe I just need to remove the replication entirely and start from scratch?
Before I go that route, does anyone know what would be likely to cause this
error when trying to run the distribution agent?
The process could not connect to Subscriber '<SERVERNAME>'.
Login failed for user '(null)'. Reason: Not associated with a trusted SQL
Server connection.
(Source: <SERVERNAME> (Data source); Error number: 18452)
"Jim Underwood" <james.underwoodATfallonclinic.com> wrote in message
news:e5aCzMhUHHA.5012@.TK2MSFTNGP04.phx.gbl...
> While trying to configure replication from and to SQL Server 2000 boxes, I
> encountered the following error, which I was not able to get past. The
> error is encountered after completing the "Push Subscription Wizard" which
> is set to create the database and initialize the schema and data. The
> database gets created, but the schema does not.
> Does anyone have advice on how to get past this error?
> If not, I have questions further on regarding my test environment...
> ----
--
> --
> The process could not connect to Subscriber '<SERVERNAME>'.
> Login failed for user '(null)'. Reason: Not associated with a trusted SQL
> Server connection.
> (Source: <SERVERNAME> (Data source); Error number: 18452)
> ----
--
> --
> I am connected using windows authentication, and I am an administrator on
> both boxes. I can connect to either database and perform any action I
want,
> but I still keep getting this error. I have not found any way to specify
a
> login for replication, I assume the job runs either under my login or as
SA.
> I changed the scheduled task to be owned by SA, and attempted to run it as
> both "Self" and "DBO", with no luck. I kept getting the same error.
Some
> of the IDs that exist in our production database do not exist in the
> database we are replicating to, although SA exists, as does my account.
> Normally I do not use Enterprise manager for anything and I script
> everything out, but the only instructions I had were for EM. Would I be
> better off using a script in QA for setting up replication?
> Regarding my test environment...
> Now, after spending a couple of hours on this error, I decided to go back
to
> my test environment (where everything worked the first time) and make it
> look more like my production environment so I could do another round of
> testing. I restored the master database and my 3 database which will be
> replicated, but the backups were form different dates and I ran into all
> kinds of conflicts. Essentially, SQL Server said the replication
> definitions were invalid, and would not let me remove them. Next week,
when
> time allows, I will be getting a cold backup of the entire database to
> attach to, hopefully that will get my test database in a state where I can
> do another round of testing.
> Am I on the right track with my test environment? What is the proper way
to
> copy a replication database, and remove the replication?
> Any advice will be greatly appreciated.
>
|||OK, I removed the replication entirely and started over, with the same
problems as before. It turned out to be an NT account/permissions issue.
The account that runs the SQL Server Agent on the distributor needs to have
access to the subscriber server as well.
Just to repeat the error:
> ----
> The process could not connect to Subscriber '<SERVERNAME>'.
> Login failed for user '(null)'. Reason: Not associated with a trusted SQL
> Server connection.
> (Source: <SERVERNAME> (Data source); Error number: 18452)
I looked at the security event logs on the subscriber server and saw a login
failure when I was trying to push the subscription. The ID that was
erroring out was a local ID on the distributor. The SQL Server Agent was
being run under this local account. Creating the same local account on the
subscriber server and giving it the required permissions, corrected the
problem.
encountered the following error, which I was not able to get past. The
error is encountered after completing the "Push Subscription Wizard" which
is set to create the database and initialize the schema and data. The
database gets created, but the schema does not.
Does anyone have advice on how to get past this error?
If not, I have questions further on regarding my test environment...
The process could not connect to Subscriber '<SERVERNAME>'.
Login failed for user '(null)'. Reason: Not associated with a trusted SQL
Server connection.
(Source: <SERVERNAME> (Data source); Error number: 18452)
I am connected using windows authentication, and I am an administrator on
both boxes. I can connect to either database and perform any action I want,
but I still keep getting this error. I have not found any way to specify a
login for replication, I assume the job runs either under my login or as SA.
I changed the scheduled task to be owned by SA, and attempted to run it as
both "Self" and "DBO", with no luck. I kept getting the same error. Some
of the IDs that exist in our production database do not exist in the
database we are replicating to, although SA exists, as does my account.
Normally I do not use Enterprise manager for anything and I script
everything out, but the only instructions I had were for EM. Would I be
better off using a script in QA for setting up replication?
Regarding my test environment...
Now, after spending a couple of hours on this error, I decided to go back to
my test environment (where everything worked the first time) and make it
look more like my production environment so I could do another round of
testing. I restored the master database and my 3 database which will be
replicated, but the backups were form different dates and I ran into all
kinds of conflicts. Essentially, SQL Server said the replication
definitions were invalid, and would not let me remove them. Next week, when
time allows, I will be getting a cold backup of the entire database to
attach to, hopefully that will get my test database in a state where I can
do another round of testing.
Am I on the right track with my test environment? What is the proper way to
copy a replication database, and remove the replication?
Any advice will be greatly appreciated.
Are you using sql server logins for the distribution agent, and the
subscriber is set to only allow windows (trusted) authentication? This could
be one cause of the error.
Cheers,
Paul Ibison SQL Server MVP, www.replicationanswers.com
|||Both of the databases are set up to allow both SQL Server and trusted
authentication. I confirmed by logging into each server as SA, just to be
sure.
I am, however, performing all of the setup using trusted authentication,
under my own account, which has administrative priviledges.
I never specified a logon for the distribution agent, however. Maybe that
is the problem? I was never actually prompted to do so, and was unable to
find where to specify it. I am the owner of the distribution agent
process, and the first process in the scheduled job is set to run as (Self),
so I assume it is running under my account. I tried changing this to DBA,
and I tried changing the owner to SA, neither made a difference.
"Paul Ibison" <Paul.Ibison@.Pygmalion.Com> wrote in message
news:exM%23TihUHHA.3652@.TK2MSFTNGP04.phx.gbl...
> Are you using sql server logins for the distribution agent, and the
> subscriber is set to only allow windows (trusted) authentication? This
could
> be one cause of the error.
> Cheers,
> Paul Ibison SQL Server MVP, www.replicationanswers.com
>
|||Jim,
just testing all posibilities here so pls can you check what the following
script returns at the subscriber.
Use Master
go
Select @.@.Servername
Cheers,
Paul Ibison SQL Server MVP, www.replicationanswers.com
|||Excellent catch. My test database comes back with the production database
name. The question is, how do I correct this, or how do I get the test
database to match my production database configuration?
"Paul Ibison" <Paul.Ibison@.Pygmalion.Com> wrote in message
news:ua103$CVHHA.4784@.TK2MSFTNGP03.phx.gbl...
> Jim,
> just testing all posibilities here so pls can you check what the following
> script returns at the subscriber.
> Use Master
> go
> Select @.@.Servername
> Cheers,
> Paul Ibison SQL Server MVP, www.replicationanswers.com
>
>
>
|||ok, I found this in BOL explaining how to change the server name. The
question is, will this be enough to get the restored database in a testable
state, or am I going about this the wrong way? Tonight I intend to take a
full cold backup from production and reattach to all the databases on test.
How to change to the current server name in the SQL Server 6.5 master
database (ISQL/w)
To change to the current server name in the SQL Server 6.5 master database
1.. Start Microsoft SQL ServerT in minimal configuration mode. In a
command prompt window, from the \Mssql\Binn directory, run:
sqlservr -f
2.. On the Start menu, point to Programs /Microsoft SQL Server 6.5, and then
click ISQL/w.
3.. Enter the sa password, and then click Connect.
4.. Execute SELECT @.@.SERVERNAME to retrieve the former server name.
5.. Execute sp_dropserver to drop the former server.
6.. Execute sp_addserver to add the current server.
7.. Stop SQL Server. In the command prompt window, press Ctrl+C.
8.. Restart SQL Server.
9.. Execute SELECT @.@.SERVERNAME to verify the current server name.
Examples
--Start SQL Server in minimal configuration mode.
--Retrieve the former server name.
SELECT @.@.SERVERNAME
--Drop the server returned from the previous select.
sp_dropserver 'SERVER6X'
--Add the current server.
sp_addserver 'SERVER70', local
--Stop SQL Server.
--Restart SQL Server in minimal configuration mode.
--Verify the current server name.
SELECT @.@.SERVERNAME
"Jim Underwood" <james.underwoodATfallonclinic.com> wrote in message
news:uDG$BKDVHHA.2212@.TK2MSFTNGP02.phx.gbl...[vbcol=seagreen]
> Excellent catch. My test database comes back with the production database
> name. The question is, how do I correct this, or how do I get the test
> database to match my production database configuration?
> "Paul Ibison" <Paul.Ibison@.Pygmalion.Com> wrote in message
> news:ua103$CVHHA.4784@.TK2MSFTNGP03.phx.gbl...
following
>
|||Jim,
this should be enough to get the subscriber set up. For step 6 you just have
to remember the 'local' switch.
Cheers,
Paul Ibison SQL Server MVP, www.replicationanswers.com
|||Paul, thank you for the assistance. I'll post back in a couple of days to
let everyone know how I made out.
I have added your site to my online SQL Server library. I have a feeling it
will come in useful.
Thanks again,
Jim
"Paul Ibison" <Paul.Ibison@.Pygmalion.Com> wrote in message
news:%23Lh$XgDVHHA.4756@.TK2MSFTNGP06.phx.gbl...
> Jim,
> this should be enough to get the subscriber set up. For step 6 you just
have
> to remember the 'local' switch.
> Cheers,
> Paul Ibison SQL Server MVP, www.replicationanswers.com
>
|||I was finally able to get my test environment up and running, after renaming
my server, deleting the entries in the distribution database, and updating
the server name in the msdb.dbo.jobs table.
The test environment replicated without any issue, so I do not know why I am
having trouble in production.
Maybe I just need to remove the replication entirely and start from scratch?
Before I go that route, does anyone know what would be likely to cause this
error when trying to run the distribution agent?
The process could not connect to Subscriber '<SERVERNAME>'.
Login failed for user '(null)'. Reason: Not associated with a trusted SQL
Server connection.
(Source: <SERVERNAME> (Data source); Error number: 18452)
"Jim Underwood" <james.underwoodATfallonclinic.com> wrote in message
news:e5aCzMhUHHA.5012@.TK2MSFTNGP04.phx.gbl...
> While trying to configure replication from and to SQL Server 2000 boxes, I
> encountered the following error, which I was not able to get past. The
> error is encountered after completing the "Push Subscription Wizard" which
> is set to create the database and initialize the schema and data. The
> database gets created, but the schema does not.
> Does anyone have advice on how to get past this error?
> If not, I have questions further on regarding my test environment...
> ----
--
> --
> The process could not connect to Subscriber '<SERVERNAME>'.
> Login failed for user '(null)'. Reason: Not associated with a trusted SQL
> Server connection.
> (Source: <SERVERNAME> (Data source); Error number: 18452)
> ----
--
> --
> I am connected using windows authentication, and I am an administrator on
> both boxes. I can connect to either database and perform any action I
want,
> but I still keep getting this error. I have not found any way to specify
a
> login for replication, I assume the job runs either under my login or as
SA.
> I changed the scheduled task to be owned by SA, and attempted to run it as
> both "Self" and "DBO", with no luck. I kept getting the same error.
Some
> of the IDs that exist in our production database do not exist in the
> database we are replicating to, although SA exists, as does my account.
> Normally I do not use Enterprise manager for anything and I script
> everything out, but the only instructions I had were for EM. Would I be
> better off using a script in QA for setting up replication?
> Regarding my test environment...
> Now, after spending a couple of hours on this error, I decided to go back
to
> my test environment (where everything worked the first time) and make it
> look more like my production environment so I could do another round of
> testing. I restored the master database and my 3 database which will be
> replicated, but the backups were form different dates and I ran into all
> kinds of conflicts. Essentially, SQL Server said the replication
> definitions were invalid, and would not let me remove them. Next week,
when
> time allows, I will be getting a cold backup of the entire database to
> attach to, hopefully that will get my test database in a state where I can
> do another round of testing.
> Am I on the right track with my test environment? What is the proper way
to
> copy a replication database, and remove the replication?
> Any advice will be greatly appreciated.
>
|||OK, I removed the replication entirely and started over, with the same
problems as before. It turned out to be an NT account/permissions issue.
The account that runs the SQL Server Agent on the distributor needs to have
access to the subscriber server as well.
Just to repeat the error:
> ----
> The process could not connect to Subscriber '<SERVERNAME>'.
> Login failed for user '(null)'. Reason: Not associated with a trusted SQL
> Server connection.
> (Source: <SERVERNAME> (Data source); Error number: 18452)
I looked at the security event logs on the subscriber server and saw a login
failure when I was trying to push the subscription. The ID that was
erroring out was a local ID on the distributor. The SQL Server Agent was
being run under this local account. Creating the same local account on the
subscriber server and giving it the required permissions, corrected the
problem.
Replication setup Error..
When I tried to configure replication, the following error comes out.
What is that about and any idea?When I tried to configure replication, the following error comes out.
What is that about and any idea?
Try this Microsoft article:
http://support.microsoft.com/?id=321822
What is that about and any idea?When I tried to configure replication, the following error comes out.
What is that about and any idea?
Try this Microsoft article:
http://support.microsoft.com/?id=321822
Monday, March 12, 2012
Replication on snapshot
Hi is that possible to configure replication in the following situation.
server A is built by snap of server B. Because i am able to create publisher on server B but i am unable to create same on server A.
could any one explain the situation, why it is happening this way.
any help is appriciated.
if you are curious what kind of error i got.
TITLE: Publisher Properties
An error occurred connecting to Publisher 'SERVER A'.
For help, click: http://go.microsoft.com/fwlink?ProdName=Microsoft+SQL+Server&ProdVer=9.00.1399.06&EvtSrc=Microsoft.SqlServer.Management.UI.PubshrPropertiesErrorSR&EvtID=CantConnectToPublisher&LinkId=20476
ADDITIONAL INFORMATION:
SQL Server replication requires the actual server name to make a connection to the server. Connections through a server alias, IP address, or any other alternate name are not supported. Specify the actual server name, 'SERVER B'. (Replication.Utilities)
BUTTONS:
OK
The @.@.servername is wrong on server A. If you do "SELECT @.@.servername" on Server A, I bet you will see the name of "B". You can do the following to correct it:
exec sp_dropserver 'server B'
exec sp_addserver 'server A', 'local'
Stop and restart the server.
You should be OK now.
Replication on multiple servers
here what I want to do,
1) transactional replication using two servers
2)Configure one server as publisher and distributor
3)Create publication on server 1
4)Create immediate update push subscription on server 2
5)I want bidirectional replication
First three steps I have created successfully
for step 4,
I created a push subcription from server 1 on publication created in step 3.
I am unable to find the option "immediate updating subscriber" in the wizard of creating push subcription.
anyway even without this option I am unable to create the replication.
in the replication monitor it is displaying as follows for the publication,
Snapshot---succeeded---snapshot of 1 artile created
Log reader--idle------no replicated transaction available
Server2:db2--Push--failed--the process could not connect to subscriber 'Server2'
what is missing?
thanks in advance for your advice
mayurThere is one step missing in your Replication setup. You have to define 'Server 2' as a subscriber. And 'Server 2 has to be able to lookup 'Server 1' as a publisher.
As far as setting 'Server 2' as updatable subscriber, you need to not only turn on 'Allow_Sync_Trans' in publisher, 'update_mode' has to be 'Sync Trans' in subscriber.
One last recommandation, write script if you can.|||I succeeded creating replication from Server1 to server2.
The problem is Server1 is in one domain and Server2 is in seperate domain.
the data is being replicated from S1 to S2
the problem is when I add/update any record in S2 database then it gives error that,
"Login failed for user sa"
what should i do next
which user account should I set up at subscriber i.e S2
thanks
Q: One last recommandation, write script if you can.
what do you mean by that, as I am using Enterprise manager|||S2 is now defined as remote server (or link server) on S1.
The authentification of S2 is made via windows logins. You shoul do one of the follwoing.
1) Talk to your domain admin(s) and set a trust relationship between those two domains
2) If you want to use the sa user to authentificate from s1 to s2, make sure that SqlServer on S2 is set up with MixedMode authentificatio, or will not work
Good luck,
ionut|||In BOL, under system stored procedure there is a section with all the system stored procedures you need to create or disable replication. Writing script gives you more control in the replication setup. You can start out using EM, but eventually you will find things that you can't do with EM.
1) transactional replication using two servers
2)Configure one server as publisher and distributor
3)Create publication on server 1
4)Create immediate update push subscription on server 2
5)I want bidirectional replication
First three steps I have created successfully
for step 4,
I created a push subcription from server 1 on publication created in step 3.
I am unable to find the option "immediate updating subscriber" in the wizard of creating push subcription.
anyway even without this option I am unable to create the replication.
in the replication monitor it is displaying as follows for the publication,
Snapshot---succeeded---snapshot of 1 artile created
Log reader--idle------no replicated transaction available
Server2:db2--Push--failed--the process could not connect to subscriber 'Server2'
what is missing?
thanks in advance for your advice
mayurThere is one step missing in your Replication setup. You have to define 'Server 2' as a subscriber. And 'Server 2 has to be able to lookup 'Server 1' as a publisher.
As far as setting 'Server 2' as updatable subscriber, you need to not only turn on 'Allow_Sync_Trans' in publisher, 'update_mode' has to be 'Sync Trans' in subscriber.
One last recommandation, write script if you can.|||I succeeded creating replication from Server1 to server2.
The problem is Server1 is in one domain and Server2 is in seperate domain.
the data is being replicated from S1 to S2
the problem is when I add/update any record in S2 database then it gives error that,
"Login failed for user sa"
what should i do next
which user account should I set up at subscriber i.e S2
thanks
Q: One last recommandation, write script if you can.
what do you mean by that, as I am using Enterprise manager|||S2 is now defined as remote server (or link server) on S1.
The authentification of S2 is made via windows logins. You shoul do one of the follwoing.
1) Talk to your domain admin(s) and set a trust relationship between those two domains
2) If you want to use the sa user to authentificate from s1 to s2, make sure that SqlServer on S2 is set up with MixedMode authentificatio, or will not work
Good luck,
ionut|||In BOL, under system stored procedure there is a section with all the system stored procedures you need to create or disable replication. Writing script gives you more control in the replication setup. You can start out using EM, but eventually you will find things that you can't do with EM.
Wednesday, March 7, 2012
Replication Montor 2005
Is there any way to configure repl. monitor in 2005 to view all the publications that go
via a distributor in one place? (as opposed to having to link to each publisher to see the replictaion status).
we have 7 publishers going throigh one distributor and in 2000 could see them all on the distributor...now have to connect to each publisher & look on each...
Thanks
DesX
This is a design change in SQL 2005 (largely based on customer feedback), and there's no way to accomplish what you want with the given tools.
However if you want to build your own tool, you can. SQL Monitor is built around proc sp_replmonitorpublication, which has parameter @.publisher. If NULL, you get everything back for the given distributor.
Subscribe to:
Posts (Atom)