August 14, 2013 at 5:07 am
Hi,
I got a requirement where I need to collect report about SQL server configuration and the report should have columns like SQL server instance name,SQL Server Collation,Edition,location of TempDB,SQL server Version,Number of databases on that SQL instance etc.
I need to generate the report querying 300 SQL server instances which are in various SQl server versions like 2000,2005,2008,2008 R2 and in different windows box.
Need help on best possible solution and script to generate the report.
August 14, 2013 at 5:12 am
You can use an SSIS package to loop through all your servers, or you can use the Central Management Servers feature in Management Studio. I'm sure there are other ways of doing it as well.
John
August 14, 2013 at 5:24 am
Hi John,
Request you to tel me the SSIS package steps to run my query against multiple servers.
I would build the SQL query to collect the column information.
August 14, 2013 at 5:31 am
Open 2008's SSMS. Register all the servers you need to run the query against. Right click the folder (in Registered Servers) -> New Query. Write your query, run it.
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
August 14, 2013 at 5:32 am
Put your server names in a table on a central server, and use a For Each Loop container to loop through them and change the connection string each time. Use an Data Flow task within the container to get the results of your query into a table on the central server.
Those are just the basic details, so if you're new to SSIS you'll need to take one of the many on-line tutorials to learn how to create and deploy a package.
You should consider the Central Management Server as well, especially if this is a one-off. If you have 300 servers to manage then it'll come in handy for many other DBA tasks.
John
August 14, 2013 at 7:03 am
t2sqldba (8/14/2013)
Hi,I got a requirement where I need to collect report about SQL server configuration and the report should have columns like SQL server instance name,SQL Server Collation,Edition,location of TempDB,SQL server Version,Number of databases on that SQL instance etc.
I need to generate the report querying 300 SQL server instances which are in various SQl server versions like 2000,2005,2008,2008 R2 and in different windows box.
Need help on best possible solution and script to generate the report.
And a word from our sponsor...http://www.red-gate.com/products/dba/sql-multi-script/
gsc_dba
August 15, 2013 at 2:59 pm
GilaMonster (8/14/2013)
Open 2008's SSMS. Register all the servers you need to run the query against. Right click the folder (in Registered Servers) -> New Query. Write your query, run it.
I agree completely with the addendum that it's useful to organize in "folders", i.e. "Server Groups", so you can select subsets that are important to you to run queries again.
Also useful for these scenarios is code like:
IF @@SERVERNAME = 'name'
or
IF SERVERPROPERTY('servername')
August 15, 2013 at 4:00 pm
You could use the following query that gives most of the information you are looking for. One option would be to store the query in a sql file and run it on multiple instances using isql. All the isql commands (one for each instance) would be stored in cmd file and the output could be written to a csv file.
set nocount on
declare @MYSQLVERSIONS table (versionnumber varchar(100), versionname varchar(100))
INSERT INTO @MYSQLVERSIONS VALUES ('11.0.2316.0', 'SQL Server 2012 CU1');
INSERT INTO @MYSQLVERSIONS VALUES ('11.0.2100.6', 'SQL Server 2012 RTM');
INSERT INTO @MYSQLVERSIONS VALUES ('10.50.2811.0', 'SQL Server 2008 R2 SP1 CU6');
INSERT INTO @MYSQLVERSIONS VALUES ('10.50.2806.0', 'SQL Server 2008 R2 SP1 CU5');
INSERT INTO @MYSQLVERSIONS VALUES ('10.50.2796.0', 'SQL Server 2008 R2 SP1 CU4');
INSERT INTO @MYSQLVERSIONS VALUES ('10.50.2789.0', 'SQL Server 2008 R2 SP1 CU3');
INSERT INTO @MYSQLVERSIONS VALUES ('10.50.2772.0', 'SQL Server 2008 R2 SP1 CU2');
INSERT INTO @MYSQLVERSIONS VALUES ('10.50.2769.0', 'SQL Server 2008 R2 SP1 CU1');
INSERT INTO @MYSQLVERSIONS VALUES ('10.50.2500.0', 'SQL Server 2008 R2 SP1');
INSERT INTO @MYSQLVERSIONS VALUES ('10.50.1815.0', 'SQL Server 2008 R2 CU13');
INSERT INTO @MYSQLVERSIONS VALUES ('10.50.1810.0', 'SQL Server 2008 R2 CU12');
INSERT INTO @MYSQLVERSIONS VALUES ('10.50.1809.0', 'SQL Server 2008 R2 CU11');
INSERT INTO @MYSQLVERSIONS VALUES ('10.50.1807.0', 'SQL Server 2008 R2 CU10');
INSERT INTO @MYSQLVERSIONS VALUES ('10.50.1804.0', 'SQL Server 2008 R2 CU9');
INSERT INTO @MYSQLVERSIONS VALUES ('10.50.1797.0', 'SQL Server 2008 R2 CU8');
INSERT INTO @MYSQLVERSIONS VALUES ('10.50.1777.0', 'SQL Server 2008 R2 CU7');
INSERT INTO @MYSQLVERSIONS VALUES ('10.50.1765.0', 'SQL Server 2008 R2 CU6');
INSERT INTO @MYSQLVERSIONS VALUES ('10.50.1753.0', 'SQL Server 2008 R2 CU5');
INSERT INTO @MYSQLVERSIONS VALUES ('10.50.1746.0', 'SQL Server 2008 R2 CU4');
INSERT INTO @MYSQLVERSIONS VALUES ('10.50.1734.0', 'SQL Server 2008 R2 CU3');
INSERT INTO @MYSQLVERSIONS VALUES ('10.50.1720.0', 'SQL Server 2008 R2 CU2');
INSERT INTO @MYSQLVERSIONS VALUES ('10.50.1702.0', 'SQL Server 2008 R2 CU1');
INSERT INTO @MYSQLVERSIONS VALUES ('10.50.1600.1', 'SQL Server 2008 R2 RTM');
INSERT INTO @MYSQLVERSIONS VALUES ('10.0.5775.0', 'SQL Server 2008 SP3 CU4');
INSERT INTO @MYSQLVERSIONS VALUES ('10.0.5770.0', 'SQL Server 2008 SP3 CU3');
INSERT INTO @MYSQLVERSIONS VALUES ('10.0.5768.0', 'SQL Server 2008 SP3 CU2');
INSERT INTO @MYSQLVERSIONS VALUES ('10.0.5766.0', 'SQL Server 2008 SP3 CU1');
INSERT INTO @MYSQLVERSIONS VALUES ('10.0.5500.0', 'SQL Server 2008 SP3');
INSERT INTO @MYSQLVERSIONS VALUES ('10.0.4330.0', 'SQL Server 2008 SP2 CU9');
INSERT INTO @MYSQLVERSIONS VALUES ('10.0.4326.0', 'SQL Server 2008 SP2 CU8');
INSERT INTO @MYSQLVERSIONS VALUES ('10.0.4323.0', 'SQL Server 2008 SP2 CU7');
INSERT INTO @MYSQLVERSIONS VALUES ('10.0.4321.0', 'SQL Server 2008 SP2 CU6');
INSERT INTO @MYSQLVERSIONS VALUES ('10.0.4316.0', 'SQL Server 2008 SP2 CU5');
INSERT INTO @MYSQLVERSIONS VALUES ('10.0.4285.0', 'SQL Server 2008 SP2 CU4');
INSERT INTO @MYSQLVERSIONS VALUES ('10.0.4279.0', 'SQL Server 2008 SP2 CU3');
INSERT INTO @MYSQLVERSIONS VALUES ('10.0.4272.0', 'SQL Server 2008 SP2 CU2');
INSERT INTO @MYSQLVERSIONS VALUES ('10.0.4266.0', 'SQL Server 2008 SP2 CU1');
INSERT INTO @MYSQLVERSIONS VALUES ('10.0.4000.0', 'SQL Server 2008 SP2');
INSERT INTO @MYSQLVERSIONS VALUES ('10.0.2850.0', 'SQL Server 2008 SP1 CU16');
INSERT INTO @MYSQLVERSIONS VALUES ('10.0.2847.0', 'SQL Server 2008 SP1 CU15');
INSERT INTO @MYSQLVERSIONS VALUES ('10.0.2816.0', 'SQL Server 2008 SP1 CU13');
INSERT INTO @MYSQLVERSIONS VALUES ('10.0.2812.0', 'SQL Server 2008 SP1 CU14');
INSERT INTO @MYSQLVERSIONS VALUES ('10.0.2808.0', 'SQL Server 2008 SP1 CU12');
INSERT INTO @MYSQLVERSIONS VALUES ('10.0.2804.0', 'SQL Server 2008 SP1 CU11');
INSERT INTO @MYSQLVERSIONS VALUES ('10.0.2799.0', 'SQL Server 2008 SP1 CU10');
INSERT INTO @MYSQLVERSIONS VALUES ('10.0.2789.0', 'SQL Server 2008 SP1 CU9');
INSERT INTO @MYSQLVERSIONS VALUES ('10.0.2775.0', 'SQL Server 2008 SP1 CU8');
INSERT INTO @MYSQLVERSIONS VALUES ('10.0.2766.0', 'SQL Server 2008 SP1 CU7');
INSERT INTO @MYSQLVERSIONS VALUES ('10.0.2757.0', 'SQL Server 2008 SP1 CU6');
INSERT INTO @MYSQLVERSIONS VALUES ('10.0.2746.0', 'SQL Server 2008 SP1 CU5');
INSERT INTO @MYSQLVERSIONS VALUES ('10.0.2734.0', 'SQL Server 2008 SP1 CU4');
INSERT INTO @MYSQLVERSIONS VALUES ('10.0.2723.0', 'SQL Server 2008 SP1 CU3');
INSERT INTO @MYSQLVERSIONS VALUES ('10.0.2714.0', 'SQL Server 2008 SP1 CU2');
INSERT INTO @MYSQLVERSIONS VALUES ('10.0.2710.0', 'SQL Server 2008 SP1 CU1');
INSERT INTO @MYSQLVERSIONS VALUES ('10.0.2531.0', 'SQL Server 2008 SP1');
INSERT INTO @MYSQLVERSIONS VALUES ('10.0.1835.0', 'SQL Server 2008 RTM CU10');
INSERT INTO @MYSQLVERSIONS VALUES ('10.0.1828.0', 'SQL Server 2008 RTM CU9');
INSERT INTO @MYSQLVERSIONS VALUES ('10.0.1823.0', 'SQL Server 2008 RTM CU8');
INSERT INTO @MYSQLVERSIONS VALUES ('10.0.1818.0', 'SQL Server 2008 RTM CU7');
INSERT INTO @MYSQLVERSIONS VALUES ('10.0.1812.0', 'SQL Server 2008 RTM CU6');
INSERT INTO @MYSQLVERSIONS VALUES ('10.0.1806.0', 'SQL Server 2008 RTM CU5');
INSERT INTO @MYSQLVERSIONS VALUES ('10.0.1798.0', 'SQL Server 2008 RTM CU4');
INSERT INTO @MYSQLVERSIONS VALUES ('10.0.1787.0', 'SQL Server 2008 RTM CU3');
INSERT INTO @MYSQLVERSIONS VALUES ('10.0.1779.0', 'SQL Server 2008 RTM CU2');
INSERT INTO @MYSQLVERSIONS VALUES ('10.0.1763.0', 'SQL Server 2008 RTM CU1');
INSERT INTO @MYSQLVERSIONS VALUES ('10.0.1600.0', 'SQL Server 2008 RTM');
INSERT INTO @MYSQLVERSIONS VALUES ('9.0.5266.0', 'SQL Server 2005 SP4 CU3');
INSERT INTO @MYSQLVERSIONS VALUES ('9.0.5259.0', 'SQL Server 2005 SP4 CU2');
INSERT INTO @MYSQLVERSIONS VALUES ('9.0.5254.0', 'SQL Server 2005 SP4 CU1');
INSERT INTO @MYSQLVERSIONS VALUES ('9.0.5000.0', 'SQL Server 2005 SP4');
INSERT INTO @MYSQLVERSIONS VALUES ('9.0.4325.0', 'SQL Server 2005 SP3 CU15');
INSERT INTO @MYSQLVERSIONS VALUES ('9.0.4317.0', 'SQL Server 2005 SP3 CU14');
INSERT INTO @MYSQLVERSIONS VALUES ('9.0.4315.0', 'SQL Server 2005 SP3 CU13');
INSERT INTO @MYSQLVERSIONS VALUES ('9.0.4311.0', 'SQL Server 2005 SP3 CU12');
INSERT INTO @MYSQLVERSIONS VALUES ('9.0.4309.0', 'SQL Server 2005 SP3 CU11');
INSERT INTO @MYSQLVERSIONS VALUES ('9.0.4305.0', 'SQL Server 2005 SP3 CU10');
INSERT INTO @MYSQLVERSIONS VALUES ('9.0.4294.0', 'SQL Server 2005 SP3 CU9');
INSERT INTO @MYSQLVERSIONS VALUES ('9.0.4285.0', 'SQL Server 2005 SP3 CU8');
INSERT INTO @MYSQLVERSIONS VALUES ('9.0.4273.0', 'SQL Server 2005 SP3 CU7');
INSERT INTO @MYSQLVERSIONS VALUES ('9.0.4266.0', 'SQL Server 2005 SP3 CU6');
INSERT INTO @MYSQLVERSIONS VALUES ('9.0.4230.0', 'SQL Server 2005 SP3 CU5');
INSERT INTO @MYSQLVERSIONS VALUES ('9.0.4226.0', 'SQL Server 2005 SP3 CU4');
INSERT INTO @MYSQLVERSIONS VALUES ('9.0.4220.0', 'SQL Server 2005 SP3 CU3');
INSERT INTO @MYSQLVERSIONS VALUES ('9.0.4211.0', 'SQL Server 2005 SP3 CU2');
INSERT INTO @MYSQLVERSIONS VALUES ('9.0.4207.0', 'SQL Server 2005 SP3 CU1');
INSERT INTO @MYSQLVERSIONS VALUES ('9.0.4053.0', 'SQL Server 2005 SP3 GDR (Security Update)');
INSERT INTO @MYSQLVERSIONS VALUES ('9.0.4035.0', 'SQL Server 2005 SP3');
INSERT INTO @MYSQLVERSIONS VALUES ('9.0.3356.0', 'SQL Server 2005 SP2 CU17');
INSERT INTO @MYSQLVERSIONS VALUES ('9.0.3355.0', 'SQL Server 2005 SP2 CU16');
INSERT INTO @MYSQLVERSIONS VALUES ('9.0.3330.0', 'SQL Server 2005 SP2 CU15');
INSERT INTO @MYSQLVERSIONS VALUES ('9.0.3328.0', 'SQL Server 2005 SP2 CU14');
INSERT INTO @MYSQLVERSIONS VALUES ('9.0.3325.0', 'SQL Server 2005 SP2 CU13');
INSERT INTO @MYSQLVERSIONS VALUES ('9.0.3315.0', 'SQL Server 2005 SP2 CU12');
INSERT INTO @MYSQLVERSIONS VALUES ('9.0.3310.0', 'SQL Server 2005 SP2 Security Update');
INSERT INTO @MYSQLVERSIONS VALUES ('9.0.3301.0', 'SQL Server 2005 SP2 CU11');
INSERT INTO @MYSQLVERSIONS VALUES ('9.0.3294.0', 'SQL Server 2005 SP2 CU10');
INSERT INTO @MYSQLVERSIONS VALUES ('9.0.3282.0', 'SQL Server 2005 SP2 CU9');
INSERT INTO @MYSQLVERSIONS VALUES ('9.0.3257.0', 'SQL Server 2005 SP2 CU8');
INSERT INTO @MYSQLVERSIONS VALUES ('9.0.3239.0', 'SQL Server 2005 SP2 CU7');
INSERT INTO @MYSQLVERSIONS VALUES ('9.0.3233.0', 'SQL Server 2005 QFE Security Hotfix');
INSERT INTO @MYSQLVERSIONS VALUES ('9.0.3228.0', 'SQL Server 2005 SP2 CU6');
INSERT INTO @MYSQLVERSIONS VALUES ('9.0.3215.0', 'SQL Server 2005 SP2 CU5');
INSERT INTO @MYSQLVERSIONS VALUES ('9.0.3200.0', 'SQL Server 2005 SP2 CU4');
INSERT INTO @MYSQLVERSIONS VALUES ('9.0.3186.0', 'SQL Server 2005 SP2 CU3');
INSERT INTO @MYSQLVERSIONS VALUES ('9.0.3175.0', 'SQL Server 2005 SP2 CU2');
INSERT INTO @MYSQLVERSIONS VALUES ('9.0.3161.0', 'SQL Server 2005 SP2 CU1');
INSERT INTO @MYSQLVERSIONS VALUES ('9.0.3152.0', 'SQL Server 2005 SP2 Cumulative Hotfix');
INSERT INTO @MYSQLVERSIONS VALUES ('9.0.3077.0', 'SQL Server 2005 Security Update');
INSERT INTO @MYSQLVERSIONS VALUES ('9.0.3054.0', 'SQL Server 2005 KB934458');
INSERT INTO @MYSQLVERSIONS VALUES ('9.0.3042.01', 'SQL Server 2005 "SP2a"');
INSERT INTO @MYSQLVERSIONS VALUES ('9.0.3042.0', 'SQL Server 2005 SP2');
INSERT INTO @MYSQLVERSIONS VALUES ('9.0.2047.0', 'SQL Server 2005 SP1');
INSERT INTO @MYSQLVERSIONS VALUES ('9.0.1399.0', 'SQL Server 2005 RTM');
create table #SVer(ID int, Name sysname, Internal_Value int, Value nvarchar(512))
declare @SmoRoot nvarchar(512)
declare @osname nvarchar(512)
declare @osversion nvarchar(512)
insert #SVer exec master.dbo.xp_msver
exec master.dbo.xp_instance_regread N'HKEY_LOCAL_MACHINE', N'SOFTWARE\Microsoft\MSSQLServer\Setup', N'SQLPath', @SmoRoot OUTPUT
exec master.dbo.xp_instance_regread N'HKEY_LOCAL_MACHINE', N'SOFTWARE\Microsoft\Windows NT\CurrentVersion', N'ProductName', @osname OUTPUT
exec master.dbo.xp_instance_regread N'HKEY_LOCAL_MACHINE', N'SOFTWARE\Microsoft\Windows NT\CurrentVersion', N'CSDVersion', @osversion OUTPUT
SELECT
SERVERPROPERTY('InstanceName'),
(select Value from #SVer where Name = N'ProductName') AS [Product],
SERVERPROPERTY(N'ProductVersion'),
(select versionname from @mysqlversions where versionnumber = SERVERPROPERTY(N'ProductVersion')) AS [VersionString],
(select Value from #SVer where Name = N'Language') AS [Language],
(select Value from #SVer where Name = N'Platform') AS [Platform],
CAST(SERVERPROPERTY(N'Edition') AS sysname) AS [Edition],
(select Internal_Value from #SVer where Name = N'ProcessorCount') AS [Processors],
(select Value from #SVer where Name = N'WindowsVersion') AS [OSVersion], @osname, @osversion,
(select cast(Internal_Value as INT)/1024 from #SVer where Name = N'PhysicalMemory') AS [PhysicalMemory],
(SELECT cast(value_in_use as int)/1024
FROM sys.configurations
WHERE name = 'max server memory (MB)') [SQLServerMemory],
(select count(*) from tempdb.dbo.sysfiles where name not like '%log%') TempFiles,
CAST(SERVERPROPERTY('IsClustered') AS bit) AS [IsClustered],
@SmoRoot AS [RootDirectory],
convert(sysname, serverproperty(N'collation')) AS [Collation]
drop table #SVer
Viewing 8 posts - 1 through 7 (of 7 total)
You must be logged in to reply to this topic. Login to reply