The following PowerShell script loops through each SQL Server name stored in a table named msmdw_central.core.instances, and for each instance does the following:
- Create the MDW database called msmdw
- Install the supporting objects
- Compress the tables if Enterprise edition is used
- Create an index to speed up purges
- Enable data collection
- Grant access to the reports to guest, so users can have access to it
- Ignore some waits, from Paul Randal’s blog
Note that the T-SQL for the supporting objects is located in the SQL Server install directory e.g.
C:\Program Files\Microsoft SQL Server\MSSQL10.SQLEXPRESS\MSSQL\Install\instmdw.sql
So I just copy it to a local path C:\scripts along with the stored procedure script for rpt_query_stats, see my post here.
function MDWInstall($SrcSrv)
{
$SrcCon = New-Object System.Data.SqlClient.SQLConnection("Data Source=$SrcSrv;Initial Catalog=master;Integrated Security=True;Connection Timeout=60")
$SrcCon.Open()
$SrcCmd = New-Object system.Data.SqlClient.SqlCommand("select @@version", $SrcCon)
$SrcCmd.CommandTimeout = 300
$query = "
begin try
if not exists(select name from sys.databases where name = 'msmdw')
begin
create database msmdw
alter database msmdw modify file (name='msmdw', size = 5000MB, maxsize = UNLIMITED, filegrowth = 100MB)
alter database msmdw modify file (name='msmdw_log', size = 500MB, maxsize = UNLIMITED, filegrowth = 100MB)
alter database msmdw set recovery simple
exec msmdw.dbo.sp_changedbowner @loginame = N'sa', @map = false
select compatibility_level from sys.databases where name = 'master'
end
"
Start-Sleep -s 1
$r = Invoke-Sqlcmd -ServerInstance "$SrcSrv" -ConnectionTimeOut 10 -Query "$query" -AbortOnError -QueryTimeout 300
if ($r.compatibility_level -eq 100)
{
"install mdw 2008"
sqlcmd -S $SrcSrv -d msmdw -i "C:\scripts\instmdw2008.sql" -x > null
}
elseif ($r.compatibility_level -eq 110)
{
"install mdw 2012"
sqlcmd -S $SrcSrv -d msmdw -i "C:\scripts\instmdw2012.sql" -x > null
sqlcmd -S $SrcSrv -d msmdw -i "C:\scripts\rpt_query_stats.sql" > null
}
elseif ($r.compatibility_level -eq 120)
{
"install mdw 2014"
sqlcmd -S $SrcSrv -d msmdw -i "C:\scripts\instmdw2014.sql" -x > null
sqlcmd -S $SrcSrv -d msmdw -i "C:\scripts\rpt_query_stats.sql" > null
}
$query = "
if cast(serverproperty('Edition') as varchar) like 'Enterprise%'
begin
-- enable page compression
exec msmdw.sys.sp_MSforeachtable 'alter index all on ? rebuild with (data_compression=page)'
-- create index to speed up purge job
create index IDX_query_stats on msmdw.snapshots.query_stats(sql_handle) with (data_compression=page)
end
else
-- create index to speed up purge job
create index IDX_query_stats on msmdw.snapshots.query_stats(sql_handle)
-- set up data collection
exec msdb.dbo.sp_syscollector_set_warehouse_database_name @database_name = 'msmdw'
exec msdb.dbo.sp_syscollector_set_warehouse_instance_name @instance_name = @@servername
exec msdb.dbo.sp_syscollector_set_cache_directory @cache_directory = 'c:\temp'
exec msdb.dbo.sp_syscollector_update_collection_set @name = 'Disk Usage', @days_until_expiration = 2
exec msdb.dbo.sp_syscollector_update_collection_set @name = 'Server Activity', @days_until_expiration = 2
exec msdb.dbo.sp_syscollector_update_collection_set @name = 'Query Statistics', @days_until_expiration = 2
-- start collection creates the jobs needed before enabling the collector
exec msdb.dbo.sp_syscollector_start_collection_set @name = 'Server Activity'
exec msdb.dbo.sp_syscollector_start_collection_set @name = 'Query Statistics'
exec msdb.dbo.sp_syscollector_start_collection_set @name = 'Disk Usage'
exec msdb.dbo.sp_syscollector_enable_collector
-- change purge schedule to avoid blocking with collection sets
exec msdb.dbo.sp_update_schedule @name='mdw_purge_data_schedule', @active_start_time=20500
-- grant access on reports to guest
use msmdw
grant connect to guest
grant view definition to mdw_reader
exec msmdw.sys.sp_addrolemember N'mdw_reader', N'guest'
use msdb
create role dc_report_reader authorization dbo
grant select on dbo.syscollector_collection_sets to dc_report_reader
grant select on dbo.syscollector_execution_log to dc_report_reader
grant select on dbo.syscollector_config_store to dc_report_reader
grant connect to guest
exec msdb.sys.sp_addrolemember N'dc_report_reader', N'guest'
-- waits to ignore
delete msmdw.core.wait_types where wait_type IN (
N'BROKER_EVENTHANDLER', N'BROKER_RECEIVE_WAITFOR',
N'BROKER_TASK_STOP', N'BROKER_TO_FLUSH',
N'BROKER_TRANSMITTER', N'CHECKPOINT_QUEUE',
N'CHKPT', N'CLR_AUTO_EVENT',
N'CLR_MANUAL_EVENT', N'CLR_SEMAPHORE',
N'DBMIRROR_DBM_EVENT', N'DBMIRROR_EVENTS_QUEUE',
N'DBMIRROR_WORKER_QUEUE', N'DBMIRRORING_CMD',
N'DIRTY_PAGE_POLL', N'DISPATCHER_QUEUE_SEMAPHORE',
N'EXECSYNC', N'FSAGENT',
N'FT_IFTS_SCHEDULER_IDLE_WAIT', N'FT_IFTSHC_MUTEX',
N'HADR_CLUSAPI_CALL', N'HADR_FILESTREAM_IOMGR_IOCOMPLETION',
N'HADR_LOGCAPTURE_WAIT', N'HADR_NOTIFICATION_DEQUEUE',
N'HADR_TIMER_TASK', N'HADR_WORK_QUEUE',
N'KSOURCE_WAKEUP', N'LAZYWRITER_SLEEP',
N'LOGMGR_QUEUE', N'ONDEMAND_TASK_QUEUE',
N'PWAIT_ALL_COMPONENTS_INITIALIZED',
N'QDS_PERSIST_TASK_MAIN_LOOP_SLEEP',
N'QDS_CLEANUP_STALE_QUERIES_TASK_MAIN_LOOP_SLEEP',
N'REQUEST_FOR_DEADLOCK_SEARCH', N'RESOURCE_QUEUE',
N'SERVER_IDLE_CHECK', N'SLEEP_BPOOL_FLUSH',
N'SLEEP_DBSTARTUP', N'SLEEP_DCOMSTARTUP',
N'SLEEP_MASTERDBREADY', N'SLEEP_MASTERMDREADY',
N'SLEEP_MASTERUPGRADED', N'SLEEP_MSDBSTARTUP',
N'SLEEP_SYSTEMTASK', N'SLEEP_TASK',
N'SLEEP_TEMPDBSTARTUP', N'SNI_HTTP_ACCEPT',
N'SP_SERVER_DIAGNOSTICS_SLEEP', N'SQLTRACE_BUFFER_FLUSH',
N'SQLTRACE_INCREMENTAL_FLUSH_SLEEP',
N'SQLTRACE_WAIT_ENTRIES', N'WAIT_FOR_RESULTS',
N'WAITFOR', N'WAITFOR_TASKSHUTDOWN',
N'WAIT_XTP_HOST_WAIT', N'WAIT_XTP_OFFLINE_CKPT_NEW_LOG',
N'WAIT_XTP_CKPT_CLOSE', N'XE_DISPATCHER_JOIN',
N'XE_DISPATCHER_WAIT', N'XE_TIMER_EVENT')
"
if ($r.compatibility_level -ge 100)
{
"enable data collection"
$SrcCmd.CommandText = $query
$r = $SrcCmd.ExecuteNonQuery()
}
$SrcCon.Close()
$SrcCon.Dispose()
}
# execute on a list of servers
("Server1", "Server2", "Server3") | foreach{MDWInstall $_}