October 19, 2011 at 1:25 pm
Where I currently work, the security team has recently set up our own dedicated SQL Server, and I'm looking to use one of the databases to gather/organize user access reports from all current production SQL servers into one place. To do this, I believe that I need to set up linked servers, but I'm not sure how to go about it. I was wondering if someone can help out, or point me in the direction of an article relating to what I'm trying to do.
Lets say for the sake of simplicity the name of my server is Security_Server, and the names of the production servers are PROD1, PROD2, etc... I currently have a windows auth login with sysadmin privileges on all servers, and want to query data from PROD1, PROD2, etc., and store that data into tables in a database on Security_Server. Since I already have a login on the PROD servers, can I just create the linked servers on Security_Server to run queries, or will I need to go out to each individual PROD server, and add a linked server that points to Security_Server?
Side note: All servers are MSSQL Server 2008.
Thank you in advance for any help,
October 19, 2011 at 2:20 pm
I would setup linked servers on Security_Server to the prod servers and "pull" the data to the Security_Server. Since they are all SQL Server you can just set them up using their names and the SQL Server type of Linked Server. If you want to run the queries to the linked servers in a job then you'll need to either setup a Proxy for the job to run under, use a domain account for the SQL Server agent service that has necessary permissions on the prod servers, or use a specific account to connect to the linked servers.
Jack Corbett
Consultant - Straight Path Solutions
Check out these links on how to get faster and more accurate answers:
Forum Etiquette: How to post data/code on a forum to get the best help
Need an Answer? Actually, No ... You Need a Question
October 19, 2011 at 7:21 pm
Thanks Jack, I though that was the right approach but I figured I'd ask around first.
October 19, 2011 at 7:42 pm
I wouldn't use linked servers at all, I would use SSIS to do the job. One of the problems with linked servers are an error on one tends to kill a process, this can be easily worked around with on SSIS. And this is really easy to do on SSIS. As well as adding another server to the check can be as simple as adding a new row to a table..
CEWII
Viewing 4 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply