ALTER USER DEFINED DATATYPE

We cannot alter the User-defined types after creation.

PROCEDURE

CIS Benchmark : Center for internet security

1 Installation, Updates and Patches
 Ensure Latest SQL Server Service Packs and Hotfixes are Installed

SELECT SERVERPROPERTY('ProductLevel') as SP_installed, SERVERPROPERTY('ProductVersion') as Version;

2. Ensure Single-Function Member Servers are Used

Ensure that no other roles are enabled for the underlying operating system and that no excess tooling is installed, per enterprise policy.

3.Surface Area Reduction 

Ensure 'Ad Hoc Distributed Queries' Server Configuration Option is set to '0'

SELECT name, CAST(value as int) as value_configured, CAST(value_in_use as int) as value_in_use FROM sys.configurations WHERE name = 'Ad Hoc Distributed Queries';

EXECUTE sp_configure 'show advanced options', 1;
RECONFIGURE; EXECUTE sp_configure 'Ad Hoc Distributed Queries', 0;
RECONFIGURE;
 GO
EXECUTE sp_configure 'show advanced options', 0; RECONFIGURE;

4 Ensure 'CLR Enabled' Server Configuration Option is set to '0'

SELECT name, CAST(value as int) as value_configured, CAST(value_in_use as int) as value_in_use FROM sys.configurations WHERE name = 'clr enabled';

 EXECUTE sp_configure 'clr enabled', 0; RECONFIGURE;

Move Database

(A) Move USER database :3 methods

1 Backup and restore on new location

2. attach and detach database

3. Alter Command

First check current location

sp_helpdb 'database name'
or
SELECT name,physical_name FROM AdventureWorks.sys.database_files

ALTER DATABASE AdventureWorks SET OFFLINE

ALTER DATABASE AdventureWorks
MODIFY FILE (NAME = AdventureWorks_Data, FILENAME ='D:\MSSQL\Data\databasename.mdf');
ALTER DATABASE AdventureWorks
MODIFY FILE (NAME = AdventureWorks_Log, FILENAME ='D:\MSSQL\Log\databasename_Log.ldf');

ALTER DATABASE AdventureWorks SET ONLINE

(B) Move master Database to another drive

(C) Move Temp Database to another drive  

SQL Query Performance

common issues with performance such as:

• deadlocks
• blocking
• missing and unused indexes
• I/O bottlenecks
• poor query plans
• statistics
• wait stats
• fragmentation

Tools to collect performance related data.

• Dynamic Management Views (DMVs) and System Catalog Views
• Profiler and Server Side Traces
• Windows Performance Monitor
• Built in performance reports in SSMS
• Query Plans
• Database Tuning Advisor

Server Health Check Checklist

1  SQL Server Service Health Check

exec master.dbo.xp_servicecontrol 'QUERYSTATE', 'MSSQLServer'
exec master.dbo.xp_servicecontrol 'QUERYSTATE', 'SQLServerAgent'
exec master.dbo.xp_servicecontrol 'QUERYSTATE', 'SQLBrowser'

2 SQL Server Backup Health Check

SELECT d.name AS "Database",
 ISNULL(CONVERT(VARCHAR,b.backupdate,120),'NEVER') AS "Last Full Backup"
FROM sys.databases d
LEFT JOIN (SELECT database_name,type,MAX(backup_finish_date) backupdate FROM backupset
 WHERE type LIKE 'D'
 GROUP BY database_name,type) b on d.name=b.database_name
WHERE (backupdate IS NULL OR backupdate < getdate()-1)
SELECT d.name AS "Database",
 ISNULL(CONVERT(VARCHAR,b.backupdate,120),'NEVER') AS "Last Log Backup"
FROM sys.databases d
LEFT JOIN (SELECT database_name,type,MAX(backup_finish_date) backupdate FROM backupset
 WHERE type LIKE 'L'
 GROUP BY database_name,type) b on d.name=b.database_name
WHERE recovery_model = 1
 AND (backupdate IS NULL OR backupdate < getdate()-1)

3. Check for Available Disk Space
exec master.dbo.xp_fixeddrives

4. Check for free memory available for SQL Server

SELECT available_physical_memory_kb/1024 as "Total Memory MB",
 available_physical_memory_kb/(total_physical_memory_kb*1.0)*100 AS "% Memory Free"
FROM sys.dm_os_sys_memory

5. Check the size of the transaction log

DBCC SQLPERF(LOGSPACE)

6. Check for Index Fragmentation

SELECT OBJECT_NAME(OBJECT_ID), index_id,index_type_desc,index_level,
avg_fragmentation_in_percent,avg_page_space_used_in_percent,page_count
FROM sys.dm_db_index_physical_stats
(DB_ID(N'<YOUR DATABASE>'), NULL, NULL, NULL , 'SAMPLED')
ORDER BY avg_fragmentation_in_percent DESC