December 13, 2013 at 12:41 am
Hi everyone!
OK, my goal is to be able to collect baseline statistic data from across all instances in our infrastructure and place the results into holding tables in a centralised location ie. a server with an instance dedicate to the task.
In order to be able to complete the task I need to be able to query all databases across all instances and in order to accomplish that, I have created within the SSMS registered server groups with all of the servers I want to collect information on. Querying ad-hoc against the group is easy, that is not the problem. The problem is what T-SQL can I execute within a job that can collect this information? OPENQUERY has been suggested but that can only (as far as I am aware) operate against a single database. For me that would mean executing around 300 OPENQUERY statements every 15 minutes in some cases.
The only other solution I have come up with is to create a small database in each instance with associated jobs to populate the holding tables.
So then, the question: What T-SQL do I need to execute to be able to query an entire server group and not just an indiividual database?
Many thanks for yur help in advance!
Regards,
Kev
December 17, 2013 at 8:00 am
Might be easier to use Powershell. Create a database with a table containing all the database instance names that you want to connect to, use Powershell to read that table and connect to the instances one by one - pull the data you require and then populate another table with the results. Schedule the Powershell script to run as a Windows task or think you can also run it via a SQL job.
December 17, 2013 at 8:29 am
Thank you for your input.
It is looking like the solution lies outside of SQL Server itself so I will be looking at PowerShell as the answer. Once I figure out how to do that I will post the result here.
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply