SQL Server – Assessment for Intake

In order to assess any existing SQL Server to be taken over and managed by Production Operation’s SQL DBA, the SQL Server needs to go through the acceptance process.  The process is as listed:

• SQL Server audit – Production Operation’s SQL DBA will audit the condition of the existing server to determine what is required to bring the SQL Server into compliance with standards.  This includes things such as security, database sizing and file layout, service accounts, maintenance jobs, etc.
• Audit Review – Production Operation’s SQL DBA will review results of audit with stakeholders.  During the review process, the DBA recommendations will be signed off on, the SLA (Service Level Agreements) established, and the Change Management Process approved. Below are the Audit details :-

Assuming that the installation is not done by SQL DBA, we will check the following parameters at Server level \database level:

Server level checks:

•  Verify that you have newest hardware\newest OS with latest SP\patches
• Verify that if SQL Server was installed properly from BOOTSTRAP\logs.
• Verify that ports(1433,1434) opened or see if any dynamic ports used for SQL Server
• Verify whether its running with newest SQL Server version with latest SP & Hot Fixes
• Verify whether Instant File Initialization is enabled on the server
• Verify whether the disk space has been properly distributed for binaries, data \ log files, backups,tempDB,Quorom and MSDTC
• Verify whether backup compression enabled
• Verify the authentication of the sql server either  windows or Mixed
• Verify whether do we have domain service accounts for SQL Services
• Identify the users who has SA permission
• Verify if BUILDIN\ADMINISTRATORS,NTAUTHORITY\SERVICE has SA on SQL Server
• Verify if the environment has high availability/ DR supported, if No. Go to next step.

If yes,

Check whether it’s Log shipping\Mirroring\Replication or clustering or AlwaysON and understand whether the implemented techniques working properly irrespective of HA/DR methods used and find out the configurations \ replicas etc.

Verify whether any configurations enabled after installation of SQL Server such as

i)        Fillfactor

ii)      Min & MaxServerMemory

iii)    Max. degree of Parallelism

iv)    FileStream

v)      SQLAgentXPS

vi)    Optimize for ad hoc workloads 

vii)  SQLMail

viii)xp_cmdshell etc.

Database :

• Identify the recovery models of the databases \ compatibility level of all databases
• Check the current size of all databases, understand the growth since a month.
• Check whether Auto Create Statistics and Auto Update Statistics are set to true for all databases.
• Check if there any backup jobs (full\differential\tlog)  and their schedules
• Verify that the old backups are stored on local disk or on file share to access them when required.
• Verify if the databases got ChangeDataCapture , TDE etc are enabled
• Verify if any health checks are being implemented on all the databases
• Go through the SQL Agent jobs, history and errorlogs etc for more understanding of the activities .
• Verify whether we are checking fragmentation on the databases. If there are no maintenance plans, determining the level of fragmentation level, we will setup a maintenance plan to re-organize (5%-30%) \rebuild(Above 30%) on weekly basis.
• Check if there are any maintenance plans for running the following :-

i)      Deleting old backup files\history of the backup jobs

ii)    Database consistency checks

iii)  Index re-organize\rebuild

iv)     Update statistics

Things to know from the application owner(Good to have):

1)      What is the functionality of the application?

2)      At any given point of time, how many users connected to the database?

3)     Approx avg. reads\avg. writes per sec on the database

4)     What are peak\non-peak hours

5)     Application Priority (LOW\MED\HIGH)

6)     Escalation Matrix

Upon noting down all the details, give a DBA report on what is good and where it requires improvements.

Performance of SQL Server can be monitored for at least 2 months to understand the flow of the application and then we can have our recommendations to make it better.

Upon Intake,work on all the improvement areas to make your instance consistent wrt availability, performance etc.