Pages

Friday, January 30, 2015

Installing the MDW automatically

If we have hundreds of SQL Servers, a centralized MDW may not scale, see my previous post here. So we will need to install it on each SQL Server.

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:
  1. Create the MDW database called msmdw
  2. Install the supporting objects
  3. Compress the tables if Enterprise edition is used
  4. Create an index to speed up purges
  5. Enable data collection
  6. Grant access to the reports to guest, so users can have access to it
  7. 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 $_}  

No comments:

Post a Comment