November 12, 2013 at 7:14 am
I need to collect the audit report for around 100+ servers , how can i autoomate this task instead of going each server.
November 12, 2013 at 8:03 am
Hi,
Are you wanting to run the same query against the 100+ servers to compile this report? If so, I have an SSIS package that I created that dynamically creates a SQL Server connection to each of my instances stored within a control table. For each of these, it runs a query and populates a destination table to pull it all into one query. If you would like further details let me know.
Alternatively you could use a 3rd party product like Redgate SQL multiscript to run against each of these instances. Or for free, in management studio, create a registered server group that includes all of your instances and you can then run the query against them all at the same time. http://technet.microsoft.com/en-us/library/bb964743.aspx
I hope that helps.
November 12, 2013 at 8:08 am
thanks pls let me know the details to implement .
November 12, 2013 at 8:25 am
You will need a table to populate with your instances, something like;
CREATE TABLE [SQLAdmin].[SQLInstances](
[ID] [int] IDENTITY(1,1) NOT NULL,
[SQLInstance] [varchar](30) NULL,
[Version] [varchar](10) NULL,
[Active] [smallint] NULL,
UNIQUE NONCLUSTERED
(
[SQLInstance] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]
GO
You will then need to create an SSIS package with the following variables:
InstanceName - String - Populate it with the name of one of your instances
ServerConnections - Object - System.Object.
InitialCatalogue - String - Insert name of your DB here
* You will then need to setup a connection in connection manager and for the expressions, use your the InitialCatalogue variable for the IntialCatalog and your InstanceName variable for your Servername.
You will then need your data flow task that executes a query like
Select * from SQLAdmin.SQLInstances
where ACTIVE = 1
and have it going into a recordset destination. In the adv properties of your recordset destination set the variablename to use your ServerConnections variable an set the input columns too.
Then hang a for each loop editor off this data flow task and in the collection tab, set it to use the ADO Enumerator, and in the variable mappings set it to use the instancename variable and index 1.
Then inside the for each loop container, put a data flow task in with the source editor connection as your connection setup earlier (marked *) and have the results populating a destination table.
Obviously it's difficult to explain in a forum but I hope that's a starter for 10.
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply