bominthu
asked on
SQL 2005 and 2008 replication error
Hi Expert,
I'm trying to do replication in SQL server, I'm keep getting below message. Could you advise?
Error messages:
Message: Login failed for user 'sa'.
Stack: at Microsoft.SqlServer.Replic ation.Serv erConnecti onEmulator .Connect()
at Microsoft.SqlServer.Replic ation.Snap shot.SqlSe rverSnapsh otProvider .Establish PublisherC onnection( )
at Microsoft.SqlServer.Replic ation.Snap shot.SqlSe rverSnapsh otProvider .GenerateS napshot()
at Microsoft.SqlServer.Replic ation.Snap shotGenera tionAgent. InternalRu n()
at Microsoft.SqlServer.Replic ation.Agen tCore.Run( ) (Source: MSSQLServer, Error number: 18456)
I'm trying to do replication in SQL server, I'm keep getting below message. Could you advise?
Error messages:
Message: Login failed for user 'sa'.
Stack: at Microsoft.SqlServer.Replic
at Microsoft.SqlServer.Replic
at Microsoft.SqlServer.Replic
at Microsoft.SqlServer.Replic
at Microsoft.SqlServer.Replic
ASKER
i used domain admin account only.But get sa account error.
Question/suggestions for you. Pleas respond back with answers to the five questions belov!
1. Do you log failed logins, if not enable it!
2. Try a profiler filtered on the domain login you use for replication
3. Does this account have read, write and list files and folders permissions on the snapshot location and the files and folders under it. Do you see any activity?
4. Try to logon with SA on both your machines to see if password has been changed, does it work?
5. Report in thread version/SP/hotfixes and underlying OS for your machines!
Regards Marten
1. Do you log failed logins, if not enable it!
2. Try a profiler filtered on the domain login you use for replication
3. Does this account have read, write and list files and folders permissions on the snapshot location and the files and folders under it. Do you see any activity?
4. Try to logon with SA on both your machines to see if password has been changed, does it work?
5. Report in thread version/SP/hotfixes and underlying OS for your machines!
Regards Marten
ASKER
1 .account is enabled
2. How to do that ?
3. Yes, account is domain admin Publication is always 100% complete without error. Only subscription has problem. (error is log in fail to sa but i don't even use sa to log in/ SQL svr can't access to scriber)
4. I can log in to machine B but haven't tried in second machine A as i don't know sa pw but it is enabled)
5.machine A is SQL 2005 Standard SP3, machine B is SQL 2008 Enterprise Evluate edition , I'm able to connect to SQL 2005 from SQL 2008 server but unable to connect to SQL 2008 from 2005 server)
2. How to do that ?
3. Yes, account is domain admin Publication is always 100% complete without error. Only subscription has problem. (error is log in fail to sa but i don't even use sa to log in/ SQL svr can't access to scriber)
4. I can log in to machine B but haven't tried in second machine A as i don't know sa pw but it is enabled)
5.machine A is SQL 2005 Standard SP3, machine B is SQL 2008 Enterprise Evluate edition , I'm able to connect to SQL 2005 from SQL 2008 server but unable to connect to SQL 2008 from 2005 server)
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Has replication ever worked, or is this a new setup?
//Marten
//Marten
ASKER
yes it is a new setup
ASKER
Hi Marten,
I manage to solve the problem. It's Ok now. Could you let me know how to make sure replication is completed for next days?
Do I need to always keep open SQL server Management studio in order to replicate successfully?
How can I make sure it is replicating continously ?
Thanks,
BMT
I manage to solve the problem. It's Ok now. Could you let me know how to make sure replication is completed for next days?
Do I need to always keep open SQL server Management studio in order to replicate successfully?
How can I make sure it is replicating continously ?
Thanks,
BMT
No SSMS does NOT need to be open. Its just used to manage one or many sql instances.
Check replication status:
https://www.experts-exchange.com/questions/20993524/Check-the-status-of-replication.html?sfQueryTermInfo=1+10+30+check+replic+statu
//Marten
Check replication status:
https://www.experts-exchange.com/questions/20993524/Check-the-status-of-replication.html?sfQueryTermInfo=1+10+30+check+replic+statu
//Marten
ASKER
Hi Marten,
WHen I run those query from the link you mentioned above, I only get message (Command(s) completed successfully.)
How can I know which database replication is completed successfully?
I have setup 3 databases for erplication.
Could you advise ?
Tks.
BMT
WHen I run those query from the link you mentioned above, I only get message (Command(s) completed successfully.)
How can I know which database replication is completed successfully?
I have setup 3 databases for erplication.
Could you advise ?
Tks.
BMT
Yes I will, but what kind of replication is set up?
Merge replication, transactional replication???
Regards Marten
Merge replication, transactional replication???
Regards Marten
ASKER
i choose other way to backup database.
don't use replication.
don't use replication.
For a guide, look at:
http://www.techrepublic.com/blog/howdoi/how-do-i-configure-transactional-replication-between-two-sql-server-2005-systems/123
//Marten