SQL Server administration is one of the key responsibilities for every DBA to keep up the availability, performance, and scalability of the data platform. Well not an easy or small job/task to manage MultiServer administration unless the automation is included in those areas. SQL Server 2008 R2 features include important elements as part of a toolkit to automate the core database development and administration functionalities.
Automated administrative tasks doesn't mean to introduce third party tools or set of robots to perform the key activities in complex applications and databases. From my experience what I believe is that it is a key task for the Database Administrator is to be able to automate the routine tasks and develop proactive maintenance to ensure that data availability and integrity is maintained without any interruption.
Just a brief note that the life-cycle of automated processes for SQL Server administration is a large topic, and it's not possible to cover all of them in this blog post alone (I will cover the important ingredients on a seperate post). In order to accomplish the automated administration, the SQL Server Services must be setup with startup accounts with necessary privileges that will help to harden the SQL Server platform at the time of deployment.
As per the subject line MSDB is a key database to accomplish the part of MultiServer administration and automate the key platform tasks on your data platform. As you are aware each service in SQL Server must have an account configured during the installation or after the installation, they are: Built-in system, local user and domain user accounts.By default as per the naming each of these account types has various privileges on the local server and services, including network execution services.
From SQL Server 2005 onwards MSDB importance has rocketed further as SSIS is introduced (a better version of DTS hence more usage for data platform ETL needs). There are ceratin database options that are set to default when you install the SQL Server instance, they are:
| Database option | Default value | Can be modified |
|---|
|
ALLOW_SNAPSHOT_ISOLATION
|
ON
|
No
|
|
ANSI_NULL_DEFAULT
|
OFF
|
Yes
|
|
ANSI_NULLS
|
OFF
|
Yes
|
|
ANSI_PADDING
|
OFF
|
Yes
|
|
ANSI_WARNINGS
|
OFF
|
Yes
|
|
ARITHABORT
|
OFF
|
Yes
|
|
AUTO_CLOSE
|
OFF
|
Yes
|
|
AUTO_CREATE_STATISTICS
|
ON
|
Yes
|
|
AUTO_SHRINK
|
OFF
|
Yes
|
|
AUTO_UPDATE_STATISTICS
|
ON
|
Yes
|
|
AUTO_UPDATE_STATISTICS_ASYNC
|
OFF
|
Yes
|
|
CHANGE_TRACKING
|
OFF
|
No
|
|
CONCAT_NULL_YIELDS_NULL
|
OFF
|
Yes
|
|
CURSOR_CLOSE_ON_COMMIT
|
OFF
|
Yes
|
|
CURSOR_DEFAULT
|
GLOBAL
|
Yes
|
|
Database Availability Options
|
ONLINE
MULTI_USER
READ_WRITE
|
No
Yes
Yes
|
|
DATE_CORRELATION_OPTIMIZATION
|
OFF
|
Yes
|
|
DB_CHAINING
|
ON
|
Yes
|
|
ENCRYPTION
|
OFF
|
No
|
|
NUMERIC_ROUNDABORT
|
OFF
|
Yes
|
|
PAGE_VERIFY
|
CHECKSUM
|
Yes
|
|
PARAMETERIZATION
|
SIMPLE
|
Yes
|
|
QUOTED_IDENTIFIER
|
OFF
|
Yes
|
|
READ_COMMITTED_SNAPSHOT
|
OFF
|
No
|
|
RECOVERY
|
SIMPLE
|
Yes
|
|
RECURSIVE_TRIGGERS
|
OFF
|
Yes
|
|
Service Broker Options
|
ENABLE_BROKER
|
Yes
|
|
TRUSTWORTHY
|
ON
|
Yes
|
As per the above list you can see certain options can be modified as per your data platform standards and few cannot. Further to the above list the following changes cannot be performed on the MSDB database, they are:
- Changing MSDB collation, default is server-collation (why bother)
- Dropping guest user (don't worry)
- Enabling Change Data Capture (bad idea)
- Pushing into Database Mirroring, for that you cannot modify RECOVERY model to FULL (waste of time)
- Renaming database or database filegroup (you have lot more important tasks to do)
- Fiddling with database options to set OFFLINE or READ_ONLY (catastrophic idea)
Having said that MSDB is key database for all MultiServer administration and ETL activities you need to ensure to manage regular backup strategy. And also monitor the database size regularly, in order to do so take help of following TSQL:
--Obtain table size and list of tables with their index names
use msdb
go
SELECT TOP 20 DB_NAME=DB_NAME(),Systable=OBJECT_NAME(sysind.ID),'Name of Index'=sysind.NAME,INDID,USED, ROWS, SIZE_MB = ROUND((USED*8.0/1024.0),2) FROM sys.sysindexes sysind, sys.sysobjects sysobj WHERE sysind.ID = sysobj.ID AND INDID IN ( 0,1) AND XTYPE = 'U' ORDER BY rows DESC
--If not satisfied with above, get information as per SP_SPACEUSED style
SELECT object_name(ind.object_id) as TableName, ind.[name] as 'Name of Index', sum(alloc.used_pages) as UsedPages, sum(alloc.data_pages) as DataPages, sum(alloc.total_pages) as TotalPages, (sum(alloc.used_pages) * 8) / 1024 as Used_MB, (sum(alloc.data_pages) * 8) / 1024 as Data_MB, (sum(alloc.total_pages) * 8) / 1024 as Total_MB FROM sys.indexes ind INNER JOIN sys.partitions prt ON ind.object_id = prt.object_id AND ind.index_id = prt.index_id INNER JOIN sys.allocation_units alloc ON prt.partition_id = alloc.container_id GROUP BY ind.object_id, ind.index_id, ind.[name] ORDER BY object_name(ind.object_id) --ORDER BY sum(alloc.total_pages) DESC
Finally a small note about the topic, I have written few recipes in my SQL Server 2008 R2 Administration cookbook on the topic of MultiServer Administration and automation of these key tasks to manage DBA tasks efficiently.
Republished from SQLServer-QA.net [10 clicks].
Read the original version here [0 clicks].