SQL Server Performance Forum – Threads Archive
Replication problem
I have two servers at a remote site, both runnig MSSQL 7 SP4. One is our production server and one is our on-site test server. To avoid performance issues during operational hours, the users would like me to run recreate some of the tables in 3 databases to the test server so they can run ad hoc reports. All the information that I have found shows me that the user account to accomplish this either way needs to have domain rights. In fact, all of our users have the primary group on our network set as "domain users". I can not create a domain level account without having that user name be at least part of the domain user group. Unfortunately, invoking this gives anyone who accesses a server that uses that user name, access to shared folders on that rest of that domain, plus access to other servers on the rest of our WAN. This is something that we don’t want to let our partner have the ability to access. And the consequences of changing the primary group to another group is also a task that seems to be fraught with danger and not desirable considering who we have working on the network and where we want them to access. So, my question is…..Is there another way to do replication and/or DTS without that? So far, my research has not shown me the way. [8][?]I do not think your statement is true about the user account needing to be a domain user is correct. This *would* be correct if you are logging in with NT authentication, is this the case and if so, is this a necessity? Create a sql user with the appropriate set of permissions to do the necessary task, and then instruct your users to use the sql login.
"How do you expect to beat me when I am forever?"
I think DTS is the best way to move your data in the three tables, not replication. If you use DTS, you don’t need any special account to use it, you just need proper permissions to set it up. This will not affect any other users and their permissions. See the SQL Server Books Online on creating DTS packages to learn more about the security model. I assume you are the SA on both boxes, if that is the case, setting up DTS should be easy.
——————
Brad M. McGehee
Webmaster
SQL-Server-Performance.Com
Both machines are logged on using the machine’s administrator account which was set up as the same user name and password for each. Also the services are set up to work under the NT log on account. But when I do that DTS and Replication both do not work. When I try changing the SQLAgent service to run under a network capable account (in this case, the domain administrator) neither process works. If I try other network accounts, either I get a message stating that the service can’t run under that account or the same result as under the network admin one. Thanks
[8D]
Somewhere there must be a mistake of some sort. To repeat myself, so that I am clear, you need to use a domain-based account to run all of your SQL Server services, and ensure that this account is made a local administrator of each SQL Server. For example, at our company, we have an account called sqlservice. This was created as a domain account, and is not given any special permissions. Then on each SQL Server, before we install it SQL Server itself, I add the the sqlservice domain account to the local administrators group. Then I install SQL Server, and when asked for the account to use for the mssqlserver and the sqlserveragent accounts, I use this domain account. I do this exactly the same for each SQL Server. If you follow these steps exactly, DTS and replication will work fine.
——————
Brad M. McGehee
Webmaster
SQL-Server-Performance.Com
Thanks for the help here. It seems that I have been able to set up a configuration that matches all of my requirements. I now have to reverse the process so that the three databases replicate from the production machine to the test machine so that the user can do the reports on it. Tomorrow will tell the story.<br /><br />I will keep you updated.<br /><br />[<img src=’/community/emoticons/emotion-5.gif’ alt=’
‘ />]
]]>