How to add a sql administrator if you find yourself locked out.
(replace the server name and service names with your own!)
1. Stop the SQL Service -
Microsoft Windows [Version 6.3.9600]
(c) 2013 Microsoft Corporation. All rights reserved.
C:\Users\Administrator>net stop mssql$sql2012
The following services are dependent on the SQL Server (SQL2012) service.
Stopping the SQL Server (SQL2012) service will also stop these services.
SQL Server Agent (SQL2012)
Do you want to continue this operation? (Y/N) [N]: Y
The SQL Server Agent (SQL2012) service is stopping.
The SQL Server Agent (SQL2012) service was stopped successfully.
The SQL Server (SQL2012) service is stopping.
The SQL Server (SQL2012) service was stopped successfully.
2. Start the SQL Service in single user mode -
C:\Users\Administrator>net start mssql$sql2012 /m
The SQL Server (SQL2012) service is starting.
The SQL Server (SQL2012) service was started successfully.
3. Add a user -
C:\Users\Administrator>osql -E -Sxeros\sql2012
1> CREATE LOGIN mylogin WITH PASSWORD = 'mypassword' , CHECK_POLICY=OFF;
2> GO
1> ALTER SERVER ROLE sysadmin ADD MEMBER mylogin;
2> GO
1> EXIT
4. Stop the Sql service (you'ved finished with single user mode)
C:\Users\Administrator>net stop mssql$sql2012
The SQL Server (SQL2012) service is stopping.
The SQL Server (SQL2012) service was stopped successfully.
5. Start the service fully again
C:\Users\Administrator>net start mssql$sql2012
The SQL Server (SQL2012) service is starting.
The SQL Server (SQL2012) service was started successfully.
Thursday, 29 January 2015
Thursday, 15 January 2015
Auditing SQL Server - The Basics
I've audited a lot of SQL Servers in my current role and have developed templates and scripts to assist me. So what do I check?
Audit Summary
Is the server patched?
Is the OS and Service Pack supported?
Is the edition appropriate? *
* Ever found a 32bit OS installed on a 64bit server? I have...
Is the SQL edition appropriate i.e are we using it's features?
Memory
Networking
For Each NIC -
Other Settings
SQL Configuration
Basics
Service Account
Processor Cores
Memory Configuration
Review Logs -
Drive Configuration , Look for -
Audit Summary
- Date & Time
- Auditor (usually me!)
- Location
- Server name
- IP Address
- Physical or Virtual ?
- Virtual Platform
- Clustered ?
- Processor Type
- Clockspeed
- 32/64 bit
- OS Version
- OS Edition
- Service Pack
Is the server patched?
Is the OS and Service Pack supported?
Is the edition appropriate? *
* Ever found a 32bit OS installed on a 64bit server? I have...
Is the SQL edition appropriate i.e are we using it's features?
Memory
- Physical Size
- Swap file Configuration - Location, Min Size, Max Size, Recommended & Current Size *
Networking
For Each NIC -
- Use
- IP Address Subnet
- Max Speed
- Full Duplex? (Occasionally old servers prevail)
Other Settings
- Power Saving - Yes, I've found this incorrectly configured on live servers!
- Antivirus - Are SQL file types and executables excluded from scanning activity?
- Are any Roles installed?
- Are any Features installed?
- Other Applications - Does SQL have to share the server?
- Are any scheduled tasks present?
- No errors / misidentified hardware in device manager?
- Is the server optimised for background services?
SQL Configuration
Basics
- Instance Name
- SQL Version
- SQL Edition
- SQL Service Pack
- SQL Build
- Collation
Service Account
- What account does the SQL Service run under?
- Does it have the correct Group Policy settings?
Processor Cores
- How many are there?
- How many are configured for SQL?
- Cost Threshold of Parallelism?
- Max degree of Parallelism
- Affinity
- Affinity I/O
Memory Configuration
- Physical Available to Server
- SQL Allocation - Dynamic / Fixed
- SQL Minimum Memory
- SQL Maximum Memory
Networking
- Which protocols are enabled?
- Are they used / needed?
- What is the binding order?
SQL High Availability
Are these present ? Are they monitored?
- Clustering
- Mirroring
- Replication
- Log Shipping
Review Logs -
- SQL Server Logs
- SQL Agent Logs
- Windows Event Logs
Drive Configuration , Look for -
- Alignment (if pre Windows 2008)
- Cluster Size
- NTFS Compression
Drive Capacity
- Disk Total
- Disk Free
- Data Allocated
- Data Used
- Data Free
- Logs Allocated
- Logs Used
- Logs Free
File Locations
- Data
- Logs
Data & Logs should be on different volumes for performance.
Database Configuration
- Database Compatibility Mode - Does it match the server? Should it?
- Data & Log Allocated
- Data & Log Used
- Data & Log Free
- Data & Log Growth intervals
- Autoshrink Enabled?
Database Maintenance
Backups
- Last Successful Full Backup
- Last Successful Log Backup
- Backup Locations
Look in the backup path to ensure they are present.
Are they archived away from the server too?
Data, Logs & their respective backups should be on different volumes for safety.
Other Maintenance
How often are these tasks performed?
- Index Maintenance
- Integrity Checks
- Manage History tables
What period do these tables cover?
- Backup History
- Agent History
- Maintenance Plan History
Don't allow them to get unnecessarily large.
Wednesday, 14 January 2015
Identifying Unused Databases
Today we looked at an old server we suspected was no longer used.
It actually had multiple SQL Instances, was poorly patched and was sitting on SQL 2005, but that isn't the point of this post. With zero documentation and a large company, we couldn't be sure this wasn't in use.
Whilst simply looking at the date & time stamps of the .MDF and .LDF files could help in some cases, maintenance jobs had been left running on this server so the smallest change in statistics meant the file dates were recent. Similarly, jobs populating the Reporting Services database were still being run, meaning data old data was being repeatedly loaded.
To evaluate whether it was worth keeping the databases online, I had some investigation to do.
Step 1 : Who is Connected?
The following TSQL summarises who is connected. It ignores the currently connected user (@@SPID) and system processes (spid's up to 50)
Step 2 : Is there any data there?
Although not related to recent use, this gives an idea of size, historic and the table names might help decide if it is worth keeping. You never know, it might even be empty!
Step 3 : When were tables used?
Table usage can be gathered from the index usage statistics. This query shows the usage for all user tables in the current database. It excludes the system tables.
It actually had multiple SQL Instances, was poorly patched and was sitting on SQL 2005, but that isn't the point of this post. With zero documentation and a large company, we couldn't be sure this wasn't in use.
Whilst simply looking at the date & time stamps of the .MDF and .LDF files could help in some cases, maintenance jobs had been left running on this server so the smallest change in statistics meant the file dates were recent. Similarly, jobs populating the Reporting Services database were still being run, meaning data old data was being repeatedly loaded.
To evaluate whether it was worth keeping the databases online, I had some investigation to do.
Step 1 : Who is Connected?
The following TSQL summarises who is connected. It ignores the currently connected user (@@SPID) and system processes (spid's up to 50)
SELECT loginame , nt_username, COUNT(*) AS Connections FROM sys.sysprocesses WHERE spid > 50 and spid != @@SPID GROUP BY loginame , nt_username ORDER BY COUNT(*) DESC
Step 2 : Is there any data there?
Although not related to recent use, this gives an idea of size, historic and the table names might help decide if it is worth keeping. You never know, it might even be empty!
SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED SELECT DB_NAME(DB_ID()) AS [Database] , s.name AS [Schema] , o.name AS [Table] , i.name AS [Index] , p.partition_number , p.rows AS [Row Count] , i.type_desc AS [Index Type] FROM sys.partitions p INNER JOIN sys.objects o ON o.object_id = p.object_id INNER JOIN sys.schemas s on s.schema_id = o.schema_id INNER JOIN sys.indexes i ON p.object_id = i.object_id AND p.index_id = i.index_id WHERE s.name != 'sys' ORDER BY [Schema], [Table], [Index]
Step 3 : When were tables used?
Table usage can be gathered from the index usage statistics. This query shows the usage for all user tables in the current database. It excludes the system tables.
SELECT i.[database_id], DB_NAME(i.[database_id]) AS [Database] , s.name AS [SchemaName] , o.name AS [TableName] , MAX(i.[last_user_lookup]) AS [last_user_lookup] , MAX(i.[last_user_scan]) AS [last_user_scan] , MAX(i.[last_user_seek]) AS [last_user_seek] FROM sys.dm_db_index_usage_stats AS i INNER JOIN sys.objects o ON o.object_id = i.object_id INNER JOIN sys.schemas s ON o.schema_id = s.schema_id WHERE i.[database_id] = DB_ID() AND s.name <> 'sys' GROUP BY i.[database_id], s.name, o.name ORDER BY i.[database_id], s.name, o.name
Subscribe to:
Posts (Atom)