Sunday, July 26, 2009

List User permissions

Another common question I found in several user forums

WITH cte
             USER_NAME ( p.grantee_principal_id ) AS principal_name ,
             dp.principal_id ,
             AS principal_type_desc ,
             p.class_desc ,
             OBJECT_NAME ( p.major_id )
             AS object_name ,
             p.permission_name ,
             AS permission_state_desc
         FROM sys.database_permissions p
         INNER JOIN sys.database_principals dp
         ON p.grantee_principal_id = dp.principal_id )
        p.principal_name ,
        p.principal_type_desc ,
        p.class_desc ,
        p.[object_name] ,
        p.permission_name ,
        p.permission_state_desc ,
        CAST ( NULL AS sysname ) AS role_name
    FROM cte p
    WHERE principal_type_desc <> 'DATABASE_ROLE'
    UNION --role members
        rm.member_principal_name ,
        rm.principal_type_desc ,
        p.class_desc ,
        p.object_name ,
        p.permission_name ,
        p.permission_state_desc ,
    FROM cte p
                           role_principal_id ,
                           dp.type_desc AS principal_type_desc ,
                           member_principal_id ,
                           user_name ( member_principal_id ) AS member_principal_name ,
                           user_name ( role_principal_id ) AS role_name
                       sys.database_role_members rm
                       INNER JOIN sys.database_principals dp
                       ON rm.member_principal_id = dp.principal_id ) rm
    ON rm.role_principal_id = p.principal_id
    ORDER BY principal_name

Saturday, July 25, 2009

Step By Step Guide to Database Mirroring

Principal Server: SQL8

Mirrored Server: SQL 4


- Ensure that SQL Service /SQL Agent Services on all the machines start with the same domain account, otherwise mirroring wont work.

Step 1 : Install database on Mirrored Server

In order to get the database onto the mirrored server, we do a full backup of the ‘YourDatabase’ database on the Principal server, followed by a backup of the Transaction Log.

  • Perform a full backup of the database on the Principal server.
BACKUP DATABASE YourDatabase TO DISK = 'C:\YourDatabase_Full.Bak'
  • Perform a Transaction Log backup on the Principal server.

    BACKUP DATABASE YourDatabase TO DISK = 'C:\YourDatabase_Full.Bak'

  • Copy the backup files to the Mirror.

  • Do a restore of the full backup With NORECOVERY (if you use the visual tool, go to Options, then ensure you check the No Recovery option! This is vital! )

FROM DISK = 'C:\YourDatabase_Full.Bak' WITH NORECOVERY,
MOVE 'YourDatabase_Data' TO 'D:\Data\YourDatabase.mdf',
MOVE 'YourDatabase_Log' TO 'C:\Data\YourDatabase.ldf'
  • Perform another restore of the Transaction Log, also with the NORECOVERY option. (This is important; otherwise you'll get an error when starting the mirror).

    RESTORE LOG YourDatabase FROM DISK ='C:\YourDatabase_Full.Bak' WITH NORECOVERY

You'll notice that the database on the Mirror server now is marked as "Restoring..." and can't be accessed. This is normal and expected! The Mirror is always in a permanent Restoring state to prevent users accessing the database, but will be receiving synchronization data. If the database fails over to the Mirror, then it will become an active database and the old Principal will go into the Recovering state.


Step 2: Setting up SQL Server Service impersonation

Make sure that the sql/ sql Agent services on both the principal and the mirrored servers are running under the same local user account and not Local system account.

  • Create a local user on both the Principal and the Mirror server with the same username and password. For example, "sqluser".
  • Edit the SQL Server Service and change the Logon to this user.
  • Do the same for the SQL Server Agent service.
  • Change the SQL Server Agent service to be Automatic.
  • Re-start the SQL Server Service
  • Do this on both the Principal and the Mirror

***Create a SQL Login on both SQL Servers for this user you created.

Step 3: Setting up the Mirror

Now it is time to setup Mirroring.

· Right click on YourDatabase database and select properties

· Click "Configure Security"

· Click Next on the wizard

· Choose whether you want a Witness server or not, choose “no” and click Next

· In the Principal Server Instance stage, leave everything as its default (you can't change anything anyway)




In the Mirror Server Instance stage, choose your Mirror server from the dropdown and click Connect to provide the credentials. Click Next.


  • In the next dialog about Service Accounts, leave these blank (you only need to fill them in if the servers are in a domain or in trusted domains)


  • Click Next and Finish
  • Click "Do not start mirroring"


  • Enter in the FQDN of the servers if you want, but this is not necessary (as long as it will resolve)
  • Change the operating mode to “High performance (asynchronous)”, otherwise the principal database will become slower.
  • Click Start Mirroring (if you do not have a FQDN entered, then a warning will appear, but you can ignore it)
  • The mirror should then start, and within moments, the Status should be "synchronized: the databases are fully synchronized"


Doing a forced failover

--Run on mirror if principal isn't available


Within 10 seconds the Mirrored database will come online, if you need to change the default failover time, run this on the principal server

ALTER DATABASE YourDatabase SET PARTNER TIMEOUT <Value in Secs, should be greater than 5>

that’s it, do some testing :)

Thursday, July 23, 2009

How to kill a profiler trace

Let's say the DBA ran a trace on a server and left for a long lunch, and the client server where the profiler is running is suffering badly with poor performance on the applications running on it and someone in the admin team want to kill that trace even in the absence of the DBA.


Running the following will give you the status of running traces. You may see one running, usually traceid of 1, this is a system trace which needs to stay running:


SELECT * FROM ::fn_trace_getinfo(0) ;



Once you've found your trace, you need to stop it: 20 being the TraceID and 0 being the command to stop it.


EXEC sp_trace_setstatus 20, 0

Wednesday, July 8, 2009

Forgot/Lost ‘sa’ password

You may have faced the issue of losing the SQL Server SA password. Perhaps you followed the security best-practice of removing the  builtin\Administrators from the sysadmin server role,  and no one  you can find is in the sysadmin role.   At this point you may think that your only options are to reinstall SQL Server and attach the databases, or to  directly access the master database files, which may potentially damage the data.


SQL Server 2005 provides a better disaster recovery option for this scenario that is non-intrusive for master DB and that will help you preserve any objects and data stored in master DB (such as logins, certificates, Service Master Key, etc.) intact. Members of the Windows Administrators group now have access to SQL Server when SQL Server is in started in single-user mode, also known as "maintenance mode ".


Using the single-user mode, SQL Server 2005 prevents a  Windows Administrator to abuse this privilege to act on behalf of the sysadmin without being noticed. This allows Windows Administrator accounts to perform certain maintenance tasks, such as installing patches.


In order to start SQL Server in single-user mode, you can add the parameter "-m" at the command line. You can also use the SQL Server Configuration Manager tool, which provides proper controls for the file access and other privileges. To use the Configuration Manager tool to recover your system, use the following steps:


1. Open the Configuration Manager tool from the "SQL Server 2005| Configuration" menu

2. Stop the SQL Server Instance you need to recover

3. Navigate to the "Advanced" tab, and in the Properties text box add ";–m" to the end of the list in the "Startup parameters" option

4. Click the "OK" button and restart the SQL Server Instance


NOTE: make sure there is no space between ";" and "-m", the registry parameter parser is sensitive to such typos. You should see an entry in the SQL Server ERRORLOG file that says "SQL Server started in single-user mode."


5. After the SQL Server Instance starts in single-user mode, the Windows Administrator account is able to connect to SQL Server using the sqlcmd utility using Windows authentication. You can use Transact-SQL commands such as "sp_addsrvrolemember" to add an existing login (or a newly created one) to the sysadmin server role.

The following example adds the account "Buck" in the "DOMAIN" domain to the SQL Server "sysadmin" role:


EXEC sp_addsrvrolemember 'DOMAIN\Aneesh', 'sysadmin';



6. Once the sysadmin access has been recovered, remove the ";-m" from the startup parameters using the Configuration Manager and restart the SQL Server Instance



Important Security Notes:

This process should only be used for disaster recovery when no other method to access the system with a privileged (i.e. sysadmin or equivalent) is available.


This process allows a Windows Administrator account to override their privileges within SQL Server. It requires explicit and intrusive actions that can be monitored and detected, including:

         Stop SQL Server and restart it in single use mode

         Connecting to SQL Server using Windows credentials

Friday, July 3, 2009

Rename Schema / Transfer schema

   1: CREATE SCHEMA new_schema 
   2: GO 
   3: -- Now run these queries one by one, copy and paste the result of this in a new window and run it 
   4: SELECT 'ALTER SCHEMA new_schema TRANSFER ' + SCHEMA_NAME(schema_id) + '.' + name 
   5: FROM sys.tables WHERE schema_id = SCHEMA_ID('old_schema'); 
   7: SELECT 'ALTER SCHEMA new_schema TRANSFER ' + SCHEMA_NAME(schema_id) + '.' + name 
   8: FROM sys.views 
   9: WHERE schema_id = SCHEMA_ID('old_schema'); 
  11: SELECT 'ALTER SCHEMA new_schema TRANSFER ' + SCHEMA_NAME(schema_id) + '.' + name 
  12: FROM sys.procedures 
  13: WHERE schema_id = SCHEMA_ID('old_schema');

Thursday, July 2, 2009

Maintenance Plans in SQL 2005 Express

I found lot of questions on the missing in ability of SQL Server Express to automatically perform database backups/integrity checks/reindexing regularly with a Maintenance Plan. Since SQL Express doesn't have Maintenance Plans, it takes a bit of extra effort to set that up. Here's how I do it:

A Windows Scheduled Task runs daily, kicking off sqlexpressmaintplan.cmd:

sqlcmd -S SQLSERVER\INSTANCE -i d:\scripts\sqlexpressmaintplan.sql
gzip -fNr e:\SQLBackups

(The second line simply compresses (using gzip) the .BAK files to conserve space.)

This executes sqlexpressmaintplan.sql, which has the configuration for my "maintenance
plan". At a glance, it looks like a lot, but I've got it broken down into separate sections/tasks, so it's easy to comprehend and maintain.

-- integrity check on system databases; save reports for 7 days

exec expressmaint
@database = 'ALL_SYSTEM',
@optype = 'CHECKDB',
@reportfldr = 'e:\SQLReports',
@rptretainunit = 'weeks',
@rptretainval = 1,
@report = 1

-- integrity check on user databases; save reports for 7 days

exec expressmaint
@database = 'ALL_USER',
@optype = 'CHECKDB',
@reportfldr = 'e:\SQLReports',
@rptretainunit = 'weeks',
@rptretainval = 1,
@report = 1

-- reindex my database; save reports for 7 days

exec expressmaint
@database = 'MyDatabase',
@optype = 'REINDEX',
@reportfldr = 'e:\SQLReports',
@rptretainunit = 'weeks',
@rptretainval = 1,
@report = 1

-- backup system databases; verify; save backups for 2 weeks; save reports for 7 days

exec expressmaint
@database = 'ALL_SYSTEM',
@optype = 'DB',
@backupfldr = 'e:\SQLBackups',
@reportfldr = 'e:\SQLReports',
@verify = 1,
@dbretainunit = 'weeks',
@dbretainval = 2,
@rptretainunit = 'weeks',
@rptretainval = 1,
@report = 1

-- backup user databases; verify; save backups for 4 weeks; save reports for 7 days

exec expressmaint
@database = 'ALL_USER',
@optype = 'DB',
@backupfldr = 'e:\SQLBackups',
@reportfldr = 'e:\SQLReports',
@verify = 1,
@dbretainunit = 'weeks',
@dbretainval = 4,
@rptretainunit = 'weeks',
@rptretainval = 1,
@report = 1

The real magic happens in the expressmaint stored procedure, created by SQL Server MVP Jasper Smith. (Also available as a standalone executable.) You can read more about this great tool on in two articles covering the stored procedure and the standalone app.

It's been a really long time since I originally set this up, and I'm having to revisit it now (and write about it) because I just rebuilt my primary workstation and server at home. Now that I'm reading back over this article, I can't remember why I chose to use the stored procedure instead of the executable version -- the exe would be a bit easier to drop into place, along with the batch file (the SQL script would no longer be necessary), next time I have to set this up on a fresh SQL
Express install. I'm going to try out the executable version this time around to see if it's any easier to set up and maintain (it looks like it will be).

Wednesday, June 24, 2009

Recently executed SQL Statements

SELECT last_execution_time , [text] AS [Statement]



BY last_execution_time DESC

Thursday, June 4, 2009

Failover Clustering

Failover Clustering

SQL Server 2005

  • Multi-instance clusters – each instance requires its own resources
  • Multi-node – more nodes per configuration, active/active, active/passive, N+1 – plann carefully
  • Analysis Services clusterable
  • See
  • Integration Services – Can be clustered. Discussion - Where to store the packages?
  • Reporting services not clusterable  - Consider using network load balancing instead
  • Can rename a clustered instance
  • Majority Node Set
  • See

SQL Server 2008

  • Cluster Validation tool
  • Support for up to 16 nodes
  • New quorum model: File Server Witness, Node Majority
  • Edit subnet mask, DHCP, IPv6
  • GPT disks, support for >2TB partitions
  • Improved cluster setup, error reporting
  • Rolling upgrades, one node at a time
  • See

Failure Detection

  • Node failure
  • Resource failure detected
  • LooksAlive – SQL Server status – default every 5 seconds
  • IsAlive – SELECT @@SERVERNAME – default every 60 seconds
  • See 
  • Upon failover – new instance comes up on other node
  • SQL Server 2005+ – Enterprise Edition – service available after Redo phase completes
  • Failback – Available using use preferred owner


  • Verify failover
  • Careful – Post-install tasks like manual IP ports, additional disks – Check resources
  • Logs: Windows Event Logs, Cluster log, SQL Server Setup log
  • Books Online: Failover Cluster Troubleshooting
  • See

Clustering Details

  • Client design: To the app, failover process looks like server taking a long while to respond. Consider adding retry logic.
  • Planning: Verify hardware solution, run validation, plan security for service accounts, plan SQL Tools location
  • Migration: Verify OS settings (CSP, Kerberos), MSDTC, pre-requisites
  • Heartbeat: TCP/IP configuration, binding order, remove NETBIOS
  • Virtualization: Check support policy (KB below), guest failover not supported, SVVP
  • See
  • See
  • Adding a node: Set SQL setup, select add cluster node
  • Removing a node: Set SQL setup, select remove cluster node
  • SQL Server setup can be fully scripted, including cluster install, add cluster node, etc.
  • Example: SETUP.EXE /q /ACTION=InstallFailoverCluster /INSTANCENAME=...
  • Example: SETUP.EXE /q /ACTION=AddNode /INSTANCENAME=...
  • See 
  • Careful – SQL Server 2008 cluster setup is now run one node at a time
  • Cluster log: Use CLUSTER.EXE command line to obtain text log
  • See

Combining with other features

Clustering vs. Mirroring


SQL Server 2008 Database Mirroring

Database Mirroring

  • Software solution for high availability.
  • Per database and not per server.
  • Increase data protection, availability, upgrade availability.
  • Careful – A lot of things live outside the database
  • SQL HA options for the masses, without the high-end hardware requirement.
  • Very popular option.
  • See

Requirements and Limitations

  • Full recovery model
  • Express (witness only), Standard (can't do async) or Enterprise
  • Logins (logins are not in the database, you need to make sure they are on the other side)
  • Cannot have multiple mirrors – use log shipping if you need this
  • Not intended for a large number of databases – Depends on traffic
  • Careful – In multi-database applications, failover behavior
  • Cannot mirror master, msdb, tempdb, model




  • Clients: ADO.NET v2 required for automatic failover: Failover partner in connection string.
  • Careful – Client will ask the primary upon connect. Can try the partner if primary not there.
  • In SQL Server 2008: Failover partner cached in registry upon first connect to primary (no change to connection string required if primary available on first connect)
  • See

Reporting on Mirror

  • Mirror can be access as read-only.
  • Database snapshots can be created on mirror, you can use snapshot for reports.
  • See
  • Snapshot cannot be restored to mirror. Snapshot performance implications.
  • Careful – refreshing snapshot, finding the correct snapshot, failover situations
  • Consider using snapshot as source for a separate reporting database.
  • Consider having a process that keeps track of which is the latest snapshot.

Clustering and Mirroring

SQL Server 2008


Upgrade Steps

  • Switch to high safety (sync). Make sure it's in sync.
  • Disable the witness.
  • Perform a rolling upgrade (upgrade mirror, manual failover to mirror, upgrade original principal, manual failover to original principal).
  • Switch back to high performance (async)
  • Re-enable the witness.
  • See
  • Careful – Some data loss may occur

TechNet Best practices

Performance counters

Failover process

  • Failover occurs
  • Some time to detect the failure (hard failures (network outage) are quicker than soft failures)
  • Some time to coordinate with the witness
  • Decision to failover
  • Some time (order of a second) to actually fail over
  • Database available on new principal
  • Database Undo continues
  • Time to failover typically just a few seconds (varies depending on case – pull network cable, power off principal, stop sql server service, shutdown principal, manual failover)


Automatic page repair


  • Database in full recovery mode
  • Backup original database
  • Restore both backups with NO RECOVERY
  • Careful – Consider options to migrate and keep other objects in sync (Logins, SQL Server Agent jobs (disabled), SSIS packages, linked servers, backup devices, maintenance plans, database mail profiles, etc.)
  • In SSMS, Database "Recovering…", click on Tasks, Mirror…
  • Witness – can be enabled from principal or partner, but keep in separate server
  • Principal – Listener port, encryption, endpoint
  • Partner – Listener port, encryption, endpoint
  • Security – Service accounts for principal, partner
  • Review screen – Principal and Mirror endpoints – format is TCP://computer:port
  • Option to start mirroring right away…
  • SELECT * FROM sys.endpoints
  • SELECT * FROM sys.dm_db_mirroring_connections
  • Notice that the wizard made a few calls on your behald, like encryption protocol
  • Careful – If status is "Synchronizing…" all the time, you're running behind (not keeping up)
  • Adding a witness
  • Launching "Database Mirroring Monitor"

Demo with TDE 

Mirroring Details

  • Backup: Can't backup mirror, can't backup snapshot, Consider log shipping for those
  • Monitoring: Check if partner is keeping up
  • Monitoring GUI: GUI tool, dm_monitor monitor role
  • Monitoring SP:  sp_dbmonitorupdate, once per minute by default, updates internal msdb table
  • See
  • States: Synchronized, Synchronizing, Suspended (pause),  Disconnected
  • See
  • Cross domain security: Use certificate security
  • See
  • Client design: Consider the added latency when working with high safety
  • Network issues: No set limits for async, compression helps, consider the consequences
  • Currently no support for combining filestream and mirroring (consider Clustering)
  • Careful - Mirroring and multi-dabase apps. Mirror is per database.

Mirror and other technologies

  • Mirroring and Replication – Supported
  • Mirroring and Database Snapshots – Supported
  • Mirroring and Clustering – Supported. Typical: Cluster local, async mirroring to remote
  • Mirroring and Log Shipping – Supported. Consider carefully
  • See

Related blog posts