How to document logins with server roles

Recently a customer asked me if I could write a script that could document which logins on the sql SQL Server that was member of a server role.

Select
CASE
WHEN isntgroup=1 THEN 'Windows Group'
WHEN isntuser=1 THEN 'Windows User'
WHEN isntuser=0 THEN 'SQL Login '
END LoginType,
loginname as [Login Name],
CASE sysadmin WHEN 1 THEN 'Yes' ELSE 'No' END isSysAdmin,
CASE securityadmin WHEN 1 THEN 'Yes' ELSE 'No' END isSecurityAdmin,
CASE serveradmin WHEN 1 THEN 'Yes' ELSE 'No' END isServerAdmin,
CASE processadmin WHEN 1 THEN 'Yes' ELSE 'No' END isProcessAdmin,
CASE diskadmin WHEN 1 THEN 'Yes' ELSE 'No' END isDiskAdmin,
CASE dbcreator WHEN 1 THEN 'Yes' ELSE 'No' END isDBCreator,
CASE bulkadmin WHEN 1 THEN 'Yes' ELSE 'No' END isBulkAdmin,
createdate LoginCreatedDate
from sys.syslogins
where hasaccess=1

How to make your SQL Server create database files instantly

The trick is "Instant File Initialization" - an NTFS feature that, when enabled for the SQL Server service account, creates the database files almost instantly - even with TB of size.

Note that this trick will also reduce your restore time !
How to: http://www.mytechmantra.com/LearnSQLServer/Database_Instant_File_Initialization_Feature.html

Remember that this feature does not work with TDE enabled.

Script Server Roles when migrating to new SQL Server

A handy script for scripting out Server Roles that logins belongs to when migrating to another server.
/**************************************************
Script Server Roles
Author:        Rasmus Glibstrup
Company:    it-Craft Denmark
Date:        25-08-2010
Version:    1.0
Script to generate all Server Roles for SQL Server
in the case of migrating logins to other SQL Server
May very well be used together with sp_help_revlogin
Output is in text and can be cut'n'pasted to the
new server.
***************************************************/
SET NOCOUNT ON
DECLARE @SQLCmd nvarchar(1000)
DECLARE @RoleName sysname
DECLARE @Login sysname
DECLARE @Count int
Create table #ServerRoles (
ServerRole sysname,
MemberName sysname,
MemberSID varbinary(85))
INSERT INTO #ServerRoles
exec sp_helpsrvrolemember
DECLARE ServerRoleCursor Cursor
FOR SELECT ServerRole,MemberName
FROM #ServerRoles
WHERE MemberName not like 'NT SERVICE%' AND
MemberName <> 'sa' AND
MemberName not like 'NT AUTHORITY%'
OPEN ServerRoleCursor
FETCH NEXT FROM ServerRoleCursor
INTO @RoleName, @Login

SET @Count = 0
WHILE @@FETCH_STATUS = 0
BEGIN
    SET @SQLCmd = 'exec sp_addsrvrolemember ''' + @Login + ''' , ''' +  @RoleName + ''''
    PRINT @SQLCmd
    SET @Count = @Count + 1
    FETCH NEXT FROM ServerRoleCursor
    INTO @RoleName, @Login
END
IF @Count=0
Print 'No logins with serverroles, besides SA'
ELSE
Print CAST(@Count as varchar(5)) + ' Roles scriptet'
CLOSE ServerRoleCursor
DEALLOCATE ServerRoleCursor
DROP TABLE #ServerRoles

A couple of nice DATE functions

First day of month:
SELECT
DATEADD(month, DATEDIFF(month, 0, GETDATE()), 0)

Last day of month:
SELECT
DATEADD(day,-1,DATEADD(month, DATEDIFF(month, 0, GETDATE())+1, 0))

Just the date of any datetime field:
SELECT
CAST(FLOOR(CAST(GETDATE()
AS
FLOAT))AS
DATETIME)


I Can't take credit for these, but they are just SOO handy - and fast.

Performance tuning counters for SQL Server

As an instructor I always tell my students to make a performance baseline on their SQL Server so that they have something to compare against, when (not if) the users complain about performance. But my students and customers often ask – which counters should I monitor then?

Well here is, to my experience, a good list to start with. Arguably you could narrow or widen the list – but for a start this list has in my experience proven to be sufficient. 

 

Performance object	Counter	Warning	Alert	Notes
Memory	Pages/sec	100	200	I know that many sites say 20 – but that was from the NT4 days with 128 MB RAM
Memory	Available MBytes	200	100
SQL Server: Buffer Manager	Buffer Cache Hit Ratio	90	85
SQL Server: Buffer Manager	Page Life Expectancy	350	300
These should be per physical disk
Physical Disk	Disk Reads/sec	No levels – just monitor these
Physical Disk	Disk Writes/sec	No levels – just monitor these
Physical Disk	% Disk time	No levels – just monitor these
Physical Disk	% Disk time	No levels – just monitor these
Physical Disk	Avg. Disk Queue Length	1	2
Processor	% Processor Time	70	80
System	Processor Queue Length
Network Interface	Bytes Received/sec	No levels – just monitor these
Network Interface	Bytes Sent/sec	No levels – just monitor these
Network Interface	Output Queue Length	No levels – just monitor these
SQL Server: General	User Connections	No levels – just monitor these
SQL Server: Memory Manager	Connection Memory (KB)	No levels – just monitor these
SQL Server: Memory Manager	Total Server Memory (KB)	No levels – just monitor these

 

You don't have to monitor the counters all the time to create a good baseline, the warning and alert levels are included for those of you who want to implement the counters in your normal performance monitoring system like MOM.

Import and Export SQL 2005 Maintenance plans

The way SQL 2005 produces Maintenance plans is very different from SQL 2000. Instead of using the extended stored procedure xp_sqlmaint to execute and the system tables of the MSDB database to store the package – SQL 2005 uses SSIS (SQL Server Integration Services) to create and store the Maintenance plans.

To Export an existing maintenance plan from an SQL Server 2005

• Click connect in the Object Explorer
  
• Select "Integration Services"
  
• Enter credentials for the server
  
• Open the folder: "Stored Packages" -> "MSDB" -> "Maintenance Plans"
  
• Right click the package you want to export
  
• Select Export package
  
• If you want to export the package to another server or edit it – using BIDS (SQL Business Intelligence Studio) – select "File system" in the Package Placement dropdown menu.
  

To Import or copy an existing maintenance plan from one server to another

• On the server you want the maintenance plan copied TO.
  
• Click connect in the Object Explorer
  
• Select "Integration Services"
  
• Enter credentials for the server
  
• Open the folder: "Stored Packages" -> "MSDB"
  
• Right Click the folder "Maintenance Plans" and select "Import Package"
  
• In the dropdown "Package Placement" select SQL Server
  
• Enter the name of the SQL server where the source maintenance package exists.
  
• Click the … besides the Package Placement box and select the package you want to import from the Maintenance Plan folder.
  

Moving system databases

When installing SQL Server 2000/2005/2008 many often forget where the system databases go on the filesystem. They are by default placed into C:\Program Files\Microsoft SQL Server \.... – Which we properly all can agree is not the optimal placement.

Note that to move the system databases require SQL server downtime, it doesn't take long though (depending on the size of course)

DISCLAIMER:

Please test this recipe on a test server before trying it in production – failing at any step will invalidate or totally stop you SQL server installation.

 

To move the system databases do as follows:

1. Shut down the SQL Server services – all of them
   
2. Start a command prompt (and keep it open)
   
3. Start the SQL server service in this nonstandard trace mode that skips recovery of the databases (except master)
   
4. NET START MSSQLSERVER /f /T3608 (or NET START MSSQLSERVER$Instancename /f /T3608 if you have a named instance)
   
5. Now modify the logical file placement by script:
   DO NOT MOVE THE FILES PHYSICALLY YET
   

Adapt the new file location to match YOUR filesystem

ALTER DATABASE MODEL MODIFY FILE (NAME = modellog , FILENAME = 'G:\SQLLogFiles\modellog.ldf')
GO
ALTER DATABASE MODEL MODIFY FILE (NAME = modeldev , FILENAME = 'H:\SQLDataFiles\model.mdf')
GO

ALTER DATABASE TEMPDB MODIFY FILE (NAME = templog , FILENAME = 'G:\SQLLogFiles\templog.ldf')
GO
ALTER DATABASE TEMPDB MODIFY FILE (NAME = tempdev , FILENAME = 'H:\SQLDataFiles\tempdb.mdf')
GO

ALTER DATABASE MSDB MODIFY FILE (NAME = MSDBlog , FILENAME = 'G:\SQLLogFiles\MSDBlog.ldf')
GO
ALTER DATABASE MSDB MODIFY FILE (NAME = MSDBData, FILENAME = 'H:\SQLDataFiles\MSDBData.mdf')
GO

ALTER DATABASE master MODIFY FILE (NAME = master , FILENAME = 'H:\SQLDataFiles\master.mdf')
GO
ALTER DATABASE master MODIFY FILE (NAME = mastlog, FILENAME = 'G:\SQLLogFiles\Mastlog.ldf')
GO

As Per SQL Server 2008 the MSSQLSYSTEMRESOURCE database cannot be moved ! But in SQL Server 2005 the database location must be the same as the Master database.


For SQL 2005 use:

ALTER DATABASE mssqlsystemresource MODIFY FILE (NAME=data, FILENAME= 'H:\SQLDataFiles\mssqlsystemresource.mdf')
GO
ALTER DATABASE mssqlsystemresource MODIFY FILE (NAME=log, FILENAME= 'H:\SQLDataFiles\mssqlsystemresource.ldf')
GO

The MSSQLSYSTEMRESOURCE database file and log file must remain on the same drive !

 

1. Now stop the MSSQLSERVER Service: NET STOP MSSQLSERVER (or NET STOP MSSQLSERVER$Instancename)
   
2. Move the files physically to the new location you have specified in the script
   
3. Change the startup parameters of the the SQL Server service in SQL Server Configuration Manager to match the new location of the master database. Eg:
   -dH:\SQLDataFiles\master.mdf
   -eH:\SQLDataFiles\log\ErrorLog
   -lG:\SQLLogFiles\mastlog.ldf
   
4. Start the MSSQLSERVER services normally
   

Please test this recipe on a test server before trying it in production – failing at any step will invalidate or totally stop you SQL server installation.