by Hi
Running on Win 2000 and MS SQL 2000,
I am setting up a push transactional replication with no
data and schema initialisation from say server X to server
Y. Replication is global and with no trust relationships
available. Also, I could not start servers involved using
domain accounts or match local user accounts that start
agent services across all servers.
My testing went as follows:
-To start with Server x SQL Agent Service was setup to
start as Local System Account and connect to server as sa.
Log Reader Agent and Distribution Agent failed with (The
process could not connect to server/Login failed for user)
- Then I changed SQL Agent Service in Server X to start
using local user account member of Users Group. The
service was unable to start.
- Then I changed SQL Agent Service in Server X to start
using local user account member of Local Admin group but
with no access to SQL Server. Log Reader Agent and
Distribution Agent failed with (The process could not
connect to server/Login failed for user)
- Then I gave the local user access to database in SQL
Server X. Now replication is working with no problems.
- Server Y still starts using local system account.
Although SQL Agent is setup to connect with a valid user
(sa). It connects using the user used to start
SQL Agent service (Start-up account in the General Tab)
and not using details of SQL Server Connection (in the
Connection Tab of 'SQL Server Agent Properties).
My conclusion is :
To push a transactional replication, with no data and
schema initialisation, from say server X to server Y:
1- Server X SQL Agent Service needs to start using Local
or Domain User. The account needs to be member of Win
local administrator group and with sufficient access to
the Primary SQL Server. It does not, however, need access
to the Replicate SQL Server, providing when setting up the
subscription an account with permission to connect to the
replicate server was chosen.
2- SQL Server Agent service in the replicate server (only
receiving push transactional replication) can start using
any method including local system account.
Any feedback. I noticed that Microsoft suggest that SQL
Agent service in both servers should either start using
domain account or the same local account.
Any feedback, would the above fail at a later stage or
affect my replication in any way? Would the method above
work with MS SQL 7? Am I messing something?
Comments highly appreciated.
Tariq