Friday, September 13, 2013

How to Avoid the 'Abuse' of SysAdmin by Applying User Defined Roles in SQL 2012 (and keep Exec.s and Auditors Happy)

This will not be a typical post, just a dive right away into Data Access Language Code, which provides a method to avoid the SysAdmin fixed Server role in SQL Server for DBAs and Monitoring/Auditing Accounts, thanks to extensive explicit permissions and taking full advantage of SQL 2012-4 User Define Server Roles or Flexible Server Roles.

-- This script would be a required step to do post instance install and to apply flexible server roles
-- We are to apply this as a security policy in production environments, and then perform validation 
-- Could be applied on some Dev/UAT servers

-- start with a rollback / back-out - or clean out roles to start again (alternatively skip to line 50)
USE [master]
GO
Drop Server Role DBAs;
Drop Server Role Monitoring;
go
-- Add DBAs back to fixed server role sysadmin, unless on servers that will not be managed by DBAs
ALTER SERVER ROLE [sysadmin] ADD MEMBER [Group1]
ALTER SERVER ROLE [sysadmin] ADD MEMBER [MonitoringAccount1]
ALTER SERVER ROLE [sysadmin] ADD MEMBER [MonitoringAccount2]
ALTER SERVER ROLE [sysadmin] ADD MEMBER [AuditingAccount]
GO
-- drop explicit rights in master, model and msdb also
USE [msdb]
GO
ALTER ROLE [ServerGroupAdministratorRole] DROP MEMBER [DBAGroup2]
ALTER ROLE [ServerGroupAdministratorRole] DROP MEMBER [DBAGroup1]
GO
ALTER ROLE [SQLAgentOperatorRole] DROP MEMBER [DBAGroup2]
ALTER ROLE [SQLAgentOperatorRole] DROP MEMBER [DBAGroup1]
GO-- not supposed to exist, but just in case
ALTER ROLE [db_owner] DROP MEMBER [DBAGroup2]
ALTER ROLE [db_owner] DROP MEMBER [DBAGroup1]
GO
use model
go
GRANT select, insert, TAKE OWNERSHIP, view definition, update, execute, CONTROL, REFERENCES
         on schema::dbo to [DBAGroup2]
GRANT select, insert, TAKE OWNERSHIP, view definition, update, execute, CONTROL, REFERENCES
         ON SCHEMA::[dbo] TO [DBAGroup1]
GO -- don't forget you can easily enough do DENY permissions too to prevent data modificación 
use master
go
-- All sections of this Security Hardening should correspond to a master Document/ed procedure
ALTER LOGIN [sa] enable
GO
use [Master]
go
drop USER [AuditingAccount] FOR LOGIN [AuditingAccount]
drop USER [MonitoringAccount1] FOR LOGIN [MonitoringAccount1]
drop USER [MonitoringAccount2] FOR LOGIN [MonitoringAccount2]
--- END CLEAN UP / Rollback of Role Security hardening


-- BEGIN SQL Security Hardening
--- disable SA, but do not drop it, maybe needed for service packs or for backout in Startup -m option (single-user mode)
ALTER LOGIN [sa] disable  -- ALTER LOGIN [sa] enable
GO
-- before applying any security policy, ensure BUILTIN\Administrators not there
USE MASTER
GO
IF EXISTS (SELECT * FROM sys.server_principals
WHERE name = N’BUILTIN\Administrators’)
DROP LOGIN [BUILTIN\Administrators]
GO


-- New in SQL 2012 - User Defined Server Roles, begin to take advantage of them
-- using roles rather than granting access to individuals, is a best practice in itself,
-- and the flexibility of user defined roles has become essential for many reasons
-- not limited to but including auditing, compliance, best management practices
Create Server Role DBAs Authorization [securityadmin]; 
-- where SecurityAdmin contains just a few who have FireCall IDs (elevated accounts, for rare use)

-- example with a few DBAs
IF NOT EXISTS (SELECT * FROM sys.server_principals
WHERE name = N’SeniorSQLDBA1’)
CREATE LOGIN [SeniorSQLDBA1] FROM WINDOWS WITH DEFAULT_DATABASE=[master]

IF NOT EXISTS (SELECT * FROM sys.server_principals
WHERE name = N’SeniorSQLDBA2’)
CREATE LOGIN [SeniorSQLDBA2] FROM WINDOWS WITH DEFAULT_DATABASE=[master]
go
-- add a few senior dbas to [securityadmin] only for now (unless Super User/System Admin account exists)
ALTER SERVER ROLE [securityadmin] ADD MEMBER [SeniorSQLDBA2]
ALTER SERVER ROLE [securityadmin] ADD MEMBER [SeniorSQLDBA1]
-- these senior dbas should backup each other in case of role issues and revised grants have to be applied
GO

IF NOT EXISTS (SELECT * FROM sys.server_principals
WHERE name = N’DBAGroup1’)
CREATE LOGIN [DBAGroup1] FROM WINDOWS WITH DEFAULT_DATABASE=[master]

IF NOT EXISTS (SELECT * FROM sys.server_principals
WHERE name = N’DBAGroup2’)
CREATE LOGIN [DBAGroup2] FROM WINDOWS WITH DEFAULT_DATABASE=[master]

CREATE
USER [DBAGroup1] FOR LOGIN [DBAGroup1]
CREATE USER [DBAGroup2] FOR LOGIN [DBAGroup2]
GO
-- add DBA groups to the role
Alter server role DBAs add member [DBAGroup2]
-- we could use a subset of the following grants for different 'levels' of DBAs
-- (i.e. a JuniorDBA flexible server role)
Alter server role DBAs add member [DBAGroup1]

-- now lock down for operations we as DBAs should not be doing anyway
-- do not forget to give WITH GRANT rights when necessary
-- (please validate in the GUI afterwards that DBAs role has the correct DAL)

-- (all necessary permissions to do DBA job, minus unnecessary privileges)
GRANT ADMINISTER BULK OPERATIONS TO [DBAs] WITH GRANT OPTION
GRANT ALTER ANY CONNECTION TO [DBAs] WITH GRANT OPTION
GRANT ALTER ANY CREDENTIAL TO [DBAs] WITH GRANT OPTION
GRANT ALTER ANY DATABASE TO [DBAs] WITH GRANT OPTION
GRANT Shutdown to DBAs
GRANT control server TO [DBAs]
-- Luckily Control Server permission respects the following DENYs
-- which is not the case for sysadmin fixed role
GRANT ALTER ANY EVENT NOTIFICATION TO [DBAs] WITH GRANT OPTION
GRANT ALTER ANY EVENT SESSION TO [DBAs] WITH GRANT OPTION
GRANT ALTER RESOURCES TO [DBAs]
GRANT ALTER SERVER STATE TO [DBAs]
GRANT ALTER SETTINGS TO [DBAs]
GRANT AUTHENTICATE SERVER TO [DBAs]
-- Grants or denies the ability to use a particular signature across all databases on the server
-- when impersonation is used.
GRANT CONNECT SQL TO [DBAs] WITH GRANT OPTION
-- Grants or denies the ability to connect to the SQL Server.
-- All logins, when newly created, are granted this permission automatically
GRANT CREATE ANY DATABASE TO [DBAs] WITH GRANT OPTION
-- GRANT CREATE AVAILABILITY GROUP TO [DBAs] (if you have Av. Groups at all)
GRANT CREATE DDL EVENT NOTIFICATION TO [DBAs]
GRANT CREATE TRACE EVENT NOTIFICATION TO [DBAs]
GRANT VIEW ANY DATABASE TO [DBAs] WITH GRANT OPTION
GRANT VIEW ANY DEFINITION TO [DBAs]
GRANT VIEW SERVER STATE TO [DBAs]
GRANT ALTER ANY EVENT NOTIFICATION TO [DBAs]
GRANT ALTER ANY EVENT SESSION TO [DBAs]
GRANT ALTER ANY LOGIN TO [DBAs]  -- some may want this as a DENY
--- Now the explicit denys (options)
DENY ALTER ANY AVAILABILITY GROUP TO [DBAs]
DENY ALTER ANY ENDPOINT TO [DBAs]
DENY ALTER ANY LINKED SERVER TO [DBAs]
-- debatable regarding linked servers
DENY ALTER ANY SERVER ROLE TO [DBAs] 
--obviously, we want control on number or roles
DENY ALTER TRACE TO [DBAs]
DENY CREATE ENDPOINT TO [DBAs]
Deny impersonate on login::sa to DBAs
-- add any other accounts that are individual users on the server with elevated rights,
-- and the Service Account(s)

Deny impersonate on login::[SERVICEaccountNameHere] to [DBAs]
Deny Alter any Server Audit to DBAs
Deny Unsafe Assembly to DBAs;
GO
-- resolve master grants
USE [master]
GO
GRANT select, view definition, execute, CONTROL, REFERENCES on schema::dbo to [DBAGroup2]
GRANT select, view definition, execute on schema::sys to [DBAGroup2]
GRANT EXECUTE ON xp_readerrorlog TO [DBAGroup2]  -- helpful to get DBAs to query the error log
GRANT EXECUTE ON sp_readerrorlog TO [DBAGroup2]
ALTER ROLE [db_datareader] ADD MEMBER [DBAGroup2]
GO
GRANT select, view definition, execute, CONTROL, REFERENCES on schema::dbo to [DBAGroup1]
GRANT select, view definition, execute on schema::sys to [DBAGroup1]
GRANT EXECUTE ON xp_readerrorlog TO [DBAGroup1]
GRANT EXECUTE ON sp_readerrorlog TO [DBAGroup1]
ALTER ROLE [db_datareader] ADD MEMBER [DBAGroup1]
GO
-- resolve MSDB grants
USE [msdb]
GO
CREATE USER [DBAGroup1] FOR LOGIN [DBAGroup1]
CREATE USER [DBAGroup2] FOR LOGIN [DBAGroup2]
go
ALTER ROLE [ServerGroupAdministratorRole] ADD MEMBER [DBAGroup1]
ALTER ROLE SQLAgentOperatorRole ADD MEMBER [DBAGroup1]
GO 
ALTER ROLE [ServerGroupAdministratorRole] ADD MEMBER [DBAGroup2]
ALTER ROLE SQLAgentOperatorRole ADD MEMBER [DBAGroup2]
GO -- after roles, grant explicit rights to be sure nothing is missing
GRANT select, execute, CONTROL, REFERENCES on schema::dbo to [DBAGroup2]
GO
GRANT select, execute, CONTROL, REFERENCES on schema::dbo ON SCHEMA::[dbo] TO [DBAGroup1]
GO -- allow DBAs to be part of msdb ownership if you like
--ALTER ROLE [db_owner] ADD MEMBER [DBAGroup1]
GO -- not necessary since in above Admnistrator and Operator roles
ALTER ROLE [db_ssisadmin] ADD MEMBER [DBAGroup2]
go

-- resolve issues for all new databases created - fix MODEL database to include DBAs.
Use Model
GO
-- setup DenyData reader role by default for groups to cover Prod data constraint
CREATE USER [DBAGroup1] FOR LOGIN [DBAGroup1]
CREATE USER [DBAGroup2] FOR LOGIN [DBAGroup2]
-- all user databases for production, will have deny read on the data
GO
-- use roles even at the database level
CREATE ROLE [DBAs] AUTHORIZATION [dbo]
GRANT VIEW DATABASE State, execute, view definition TO [DBAs]
-- remove read access when necessary, but allow administration
-- add appropriate groups
ALTER ROLE [DBAs] ADD MEMBER [DBAGroup2]
ALTER ROLE [DBAs] ADD MEMBER [DBAGroup1]
-- and finally, requirements for some prod. environments
Deny select, insert, update TO [DBAs] 
GO
-- or deny select in prod user dbs this way
ALTER ROLE [db_denydatareader] ADD MEMBER [DBAGroup2]
ALTER ROLE [db_denydatareader] ADD MEMBER [DBAGroup1]
GO


-- WE DO NOT STOP THERE, CONTINUE with Monitoring Accounts
-- which traditionally have way too many Privileges
-- and the goal is to adhere to the principle of least privileges
use master
GO
-- create monitoring role and add accounts the monitor the servers
Create Server Role [Monitoring] Authorization [securityadmin]; 
-- the right logins in the container
CREATE LOGIN [MonitoringAccount1] FROM WINDOWS WITH DEFAULT_DATABASE=[master], DEFAULT_LANGUAGE=[us_english]
CREATE LOGIN [MonitoringAccount2] FROM WINDOWS WITH DEFAULT_DATABASE=[master], DEFAULT_LANGUAGE=[us_english]
CREATE LOGIN [AuditingAccount] FROM WINDOWS WITH DEFAULT_DATABASE=[master], DEFAULT_LANGUAGE=[us_english]
ALTER SERVER ROLE [Monitoring] ADD MEMBER [MonitoringAccount1]
ALTER SERVER ROLE [Monitoring] ADD MEMBER [MonitoringAccount2]
ALTER SERVER ROLE [Monitoring] ADD MEMBER [AuditingAccount]
-- minimum WMI permissions required, so add to local admins on the server too
GRANT CONNECT SQL TO [Monitoring]
GRANT CONTROL Server TO [Monitoring]
GRANT ALTER TRACE TO [Monitoring]
GRANT VIEW ANY DATABASE TO [Monitoring]
GRANT VIEW ANY DEFINITION TO [Monitoring]
GRANT VIEW SERVER STATE TO [Monitoring]
Grant CREATE DDL EVENT NOTIFICATION TO [Monitoring]
GRANT CREATE TRACE EVENT NOTIFICATION TO [Monitoring]
-- because we have granted control server, we must apply these DENY statements
DENY ALTER ANY AVAILABILITY GROUP TO [Monitoring]
DENY ALTER ANY ENDPOINT TO [Monitoring]
DENY ALTER ANY LINKED SERVER TO [Monitoring]
DENY ALTER ANY LOGIN TO [Monitoring]
DENY ALTER ANY SERVER ROLE TO [Monitoring] 
--obviously, we want control on number or roles
DENY CREATE ENDPOINT TO [Monitoring]
Deny impersonate on login::sa to [Monitoring]
-- add any other accounts that are individual users on the server that have Sysadmin rights
Deny impersonate on login::[SERVICEaccountNameHere] to [Monitoring]
Deny Alter any Server Audit to [Monitoring]
Deny Unsafe Assembly to [Monitoring];

-- Monitoring users need to be in Master and Model, MSDB as DataReader
Go
CREATE
USER [MonitoringAccount1] FOR LOGIN [MonitoringAccount1]
CREATE USER [MonitoringAccount2] FOR LOGIN [MonitoringAccount2]
CREATE USER [AuditingAccount] FOR LOGIN [AuditingAccount]
--
ALTER ROLE [db_datareader] ADD MEMBER [MonitoringAccount1]
ALTER ROLE [db_datareader] ADD MEMBER [MonitoringAccount2]
ALTER ROLE [db_datareader] ADD MEMBER [AuditingAccount]
-- or more explicitly
grant select, view definition, execute on schema::dbo to [AuditingAccount]
grant select, view definition, execute on schema::sys to [AuditingAccount]
GRANT EXECUTE ON xp_readerrorlog TO [AuditingAccount]
GRANT EXECUTE ON sp_readerrorlog TO [AuditingAccount]
GO
grant
select, view definition, execute on schema::dbo to [MonitoringAccount1]
grant select, view definition, execute on schema::sys to [MonitoringAccount1]
GRANT EXECUTE ON xp_readerrorlog TO [MonitoringAccount1]
GRANT EXECUTE ON sp_readerrorlog TO [MonitoringAccount1]
GO
grant select, view definition, execute on schema::dbo to [MonitoringAccount2]
grant select, view definition, execute on schema::sys to [MonitoringAccount2]
GRANT EXECUTE ON xp_readerrorlog TO [MonitoringAccount2]
GRANT EXECUTE ON sp_readerrorlog TO [MonitoringAccount2]
GO
USE
[msdb]
GO -- the options here for system databases are to either continue to create user defined roles, or used fixed if possible
CREATE USER [MonitoringAccount1] FOR LOGIN [MonitoringAccount1]
CREATE USER [MonitoringAccount2] FOR LOGIN [MonitoringAccount2]
CREATE USER [AuditingAccount] FOR LOGIN [AuditingAccount]
GO
grant
execute on schema::dbo to [MonitoringAccount1]
grant
execute on schema::dbo to [MonitoringAccount2]
grant
execute on schema::dbo to [AuditingAccount]
GO
-- improve this by adding appropriate role?
ALTER ROLE [db_datareader] ADD MEMBER [AuditingAccount] -- may need to use more elevate fixed roles here
ALTER ROLE [db_datareader] ADD MEMBER [MonitoringAccount1]
ALTER ROLE [db_datareader] ADD MEMBER [MonitoringAccount2]
GO
-- add model for defaults similar DBA groups
Use Model
GO
CREATE USER [AuditingAccount] FOR LOGIN [AuditingAccount]
CREATE USER [MonitoringAccount1] FOR LOGIN [MonitoringAccount1]
CREATE USER [MonitoringAccount2] FOR LOGIN [MonitoringAccount12]
-- all user databases for production, will have deny read on the data
GO  -- create  monitoring database role
CREATE ROLE [Monitoring] AUTHORIZATION [dbo]
GRANT VIEW DATABASE State, execute, view definition TO [Monitoring]
-- remove read access when necessary, but allow administration
Deny select, insert, update TO [Monitoring]  -- requirements for some prod. environments
-- add appropriate groups or accounts for auditing or monitoring
ALTER ROLE [Monitoring] ADD MEMBER [AuditingAccount]
ALTER ROLE [Monitoring] ADD MEMBER [MonitoringAccount1]
ALTER ROLE [Monitoring] ADD MEMBER [MonitoringAccount1]

GO

USE [master]
GO
-- cleanup those who were in sysadmin
ALTER SERVER ROLE [sysadmin] DROP MEMBER [DBAGroup2]
ALTER SERVER ROLE [sysadmin] DROP MEMBER [DBAGroup1]
ALTER SERVER ROLE [sysadmin] DROP MEMBER [AuditingAccount]
ALTER SERVER ROLE [sysadmin] DROP MEMBER [MonitoringAccount1]
-- you can truly state that you have reduced the SysAdmin elevate account access to your auditors
GO
-- validate that only those who you want are noe in sysadmin

use master
go
exec sp_helpsrvrolemember 'sysadmin'
-- reduce members to bare minimum !!


-- new for sql 2012: check which services are listed:
select * from sys.dm_server_services


-- for each new application added, one should verify the wanted/identifiable
-- (and not unwanted) logins are viewed
select * from sys.dm_exec_connections


--final review of permissions 
SELECT * FROM sys.fn_builtin_permissions('SERVER')
ORDER BY permission_name;

-- again but at a different level
SELECT * FROM sys.fn_builtin_permissions('default')
ORDER BY permission_name;

-- database securable
SELECT * FROM sys.fn_builtin_permissions('database')
ORDER BY permission_name;




Friday, August 02, 2013

Managing Multiple Servers Thanks to Registered Server Lists & the Benefits

Since I changed organisations in January this year, I asked one of my colleagues to export his SQL Server Management Studio Registered Server list. Ever since March I have been updating our list on nearly a daily basis, since I believe this is very important to managing multiple SQL Servers. For DBA teams, I believe one should make the registered server list available for the rest of the team via a network share.
Note that you can open REGSRVR file with a text editor to search through the XML data, such as to figure out within which group a specific server resides.

Multiple people cannot update this list unfortunately (unless manually) thus I do my best to keep it up to date. Maybe, at a later date, we`ll be able to have this managed under a Central Management Server (CMS), but that`ll have to wait until we begin to manage through Policies from a single CMS server.

If you have not used Registered Servers in SQL Server Management Studio before, simply click View, Registered Servers. You will also notice the option Types, which groups by Database Engine, Analysis Services, Reporting Services, SQL Server Compact, and Integration Services. Our list has very few updated for anything other than the Database Engine Type, but that will change over time!
Remember to re-import it frequently if this is the case in your organisation: to do so, right click on top of the Registered Server list, Local Server Groups, and import from the a shared folder.

One of the nice features to take advantage of with a Registered Server list is querying across multiple servers.  By right clicking on any group, even the entire Local Group, you can select New Query to view, for example, Server Infrastructure details quickly.

 T
he first method to going essential server properties, as mentioned on Technet, is to execute the following parametr details on any SQL Server installation (I tested back to SQL 2000), by executing the following command.
xp_msver "ProductName", "ProductVersion", "Language", "Platform", "WindowsVersion", "PhysicalMemory", "ProcessorCount", "ProcessorType"
-- result set is a table, with a row for each parameter
-- thus if you execute
xp_msver, you will have twenty rows


The second, and my preference as best practice for gathering essential server information in a single row with more details, is the following, including the Collation, Clustering, Service Pack Level (product level):
select serverproperty('MachineName') MachineName
,serverproperty('ServerName') ServerInstanceName
,replace(cast(serverproperty('Edition')as varchar),'Edition','') EditionInstalled
,serverproperty('productVersion') ProductBuildLevel
,serverproperty('productLevel') SPLevel
,serverproperty('Collation') Collation_Type
,serverproperty('IsClustered') [IsClustered?]
,convert(varchar,getdate(),102) QueryDate
,
serverproperty('ComputerNamePhysicalNetBIOS') AS [CurrentNodeName],
case
when exists (select * from msdb.dbo.backupset where name like '%backup%') then 'SQLBackupUsed'
else 'Local Copy_Only'
-- where you would replace the
-- strings with your respective third party or native backup solution
End

More examples? Sure:
I was asked the other day to find out how many physical sockets (not logical) that are used across servers, so one would write this:


SELECT  cpu_count / hyperthread_ratio AS physical_cpu_sockets
FROM    sys.dm_os_sys_info ;


How about all cluster node details across a group of clustered servers:

SELECT * FROM sys.dm_os_cluster_nodes

--All non-background processes and their wait times across servers:
select * from sysprocesses where status <>'background'order by waittime desc
-- Read Error logs across servers
exec master.dbo.xp_readerrorlog

Thanks to SQL Server Central forums for the physical socket query, no wonder I enjoy blogging there also Grin
...and thanks to Microsoft Premier Field Engineers for the last three during a recent Clustered Server resolution analysis issue.

Managed Service Accounts - The Saviour for the Domain-based SQL Server Service Account?

For those Database Administrators seeking to lock-down security related to a Service  Account(s), there is an option starting with Windows Server 2008 R2:
a Managed Service Account.
This type of account is tied to a machine, and cannot be locked out, and seems to be a saviour for vigilant DBAs wanting to achieve a higher level of SQL Server Instance isolation:

http://blogs.technet.com/b/askds/archive/2009/09/10/managed-service-accounts-understanding-implementing-best-practices-and-troubleshooting.aspx 

Would you agree? Or have a proposal for an even better solution? 

This is a short post (originally posted June 21st), since as you can see below, it's summer and getting some wave skiing in is essential to maintaining sanity :)

Managing Critical Server Lists with Remote Desktop Connection Manager

You don't climb with out the right tools,
so use RDCMan to get higher
Most Server Administrators, whether Database Administrators or not, are quite familiar with the Remote Desktop Connection (a.k.a. RDP) tool, which is formerly known as the Microsoft Terminal Services Connector accessible from the command mstsc in Run, or from a Command Prompt.  What most do not realise, however, is that there is a free tool that manages a list RDP connections, titled Remote Desktop Connection Manager (sometimes referred to as RDCMan). Since free is the best price self-evidently, this little add-on to Remote Desktop, requiring no different security settings than RDP itself, is a welcoming time saver to those who are responsible for a long list of servers - in our case hundreds of servers to potentially manage.  And when I say little, Microsoft certainly has made this 789KB download the best value for under one megabyte I have ever seen (very sorry VNC, I much prefer a RDP connection).

 Why use RDCMan?

▪ If you enjoy optimised productivity, and maintaining a current server list, then you will want this tool.
▪ Saves administrative burden, since you can save connection preferences and stores your current password in an encrypted format.
▪ RDCMan is a simple add-on to Remote Desktop; it only stores a list of RDP connections with respective profile, and has the same options you see with an individual RDP saved connection.
▪ Options can be set for the group (such as connecting to local resources, etc.), and then can be changed per individual server
▪ Importing and adding servers to the list is easy, through simple text file import. Duplicates are automagically eliminated.


If you are on a team, as are most of us, I am pretty sure your DBA colleagues, would appreciate an updated RDG be provided to them, which is the Remote Desktop Group saved configuration, and list SQL Server Hosts. 
Prerequisites:  For the firewall conscious people out there, RDP uses Port TCP 3389, and on the server itself, Remote Desktop Connections must be enabled.

Nota bene: As I mentioned here on Simple Talk, as the first commenter - do not click on Connect Group, since you will find yourself, all of a sudden, making an RDP connection to every single server in your list!
References: Here's the official literature from Microsoft about RDCMan  
PS: Online versions of RDCMan would be LogMeIn.com which I use to manage and aid my family with remote support. There are free or professional versions, and is simply another flavor of Join.Me (which is the very same company).  

Friday, February 08, 2013

Executive Visibility for DBAs: Use SQL 2008 Row+Page Compression, or SQL 2005 (SP2+) vardecimal Conversion, to Reduce Space ($/GB SAN) & Logical Reads

One of the great features available since SQL 2008 is Row and/or Page Compression. However, even for those of you on SQL 2005 in production who might be there for a little while: there’s a decent feature you can take advantage of too that is very expensive to do in SQL 2008. Why? Well, Compression in SQL 2008/12 requires that Entreprise edition to be installed, so if you want to benefit from this mega space saver (like 1.3TB down to 650GB as I have seen, plus queries running at as little as 40% the time they took before) you will be happy with VARDECIMAL conversion in SQL 2005 (SP2+). Both have stored procedures that you can run to estimate how much space you will save: In 2008 it's sp_estimate_data_compression_savings (from Microsoft Best Practices) -- which randomly takes rows and gives you quite an accurate estimation of space saved/gained.  Be aware that the cost for compression is CPU usage, so optimise on a server that has relatively low CPU percentage average.

Another condition you should be aware of, if you are input/output pressured on your system, meaning that you are waiting on your disks, then you can benefit from compression. With respect to performance gains, for the most accurate logical reads savings, you run this command while running your queries against the database ca
ndidate for compression:
SET STATISTICS IO ON
 This is best to evaluate for details of the page reads while doing the comparison before and after compression. Using the shortcut Control-M is also an alternative to see the Actual Execution Plan for the queries you typically run on the large objects.  For detail, see Brad's [McGehee] Compression examples here, as well as an updated version for 2012.image











In SQL 2005, from sp2 onwards, you can do this:

 (ref: http://msdn.microsoft.com/en-us/library/bb508963.aspx )
-- first run the estimate, and be patient, this shows up as a console command (DBCC)
EXEC sys.sp_estimated_rowsize_reduction_for_vardecimal 'SchemaName.TableName'
GO

exec sys.sp_db_vardecimal_storage_format ''DatabaseName'', ''ON''
GO
exec sp_tableoption ''dbo.BigTableLoadedWithDecimals'', ''vardecimal storage format'', 1
GO

--(/2005 options)
--For 2008, all SQL Server internal compression is done at the table or index level
-- AND for 2008 there is much better compression capability, namely Row and Page, you see below   
EXEC sp_estimate_data_compression_savings 'SchemaName', 'TableName', NULL, NULL, 'ROW';
GO
EXEC sp_estimate_data_compression_savings 'SchemaName', 'TableName', NULL, NULL, 'PAGE';
GO 

-- example testing started on a table that was 260MB, with a check on the storage used each time
-- I/O , according to the Actual Execution Plan for a normal SELECT started at 24.17 (no compression)
--first round of compression
ALTER TABLE schema.TableName REBUILD PARTITION = ALL
WITH (DATA_COMPRESSION = ROW) -- table became 180MB
--second round of compression
ALTER TABLE schema.TableName REBUILD PARTITION = ALL
WITH (DATA_COMPRESSION = PAGE) -- table now down to 80MB
-- I/O cost, according to the Actual Execution Plan for a SELECT down to 7.62 (just under a third)
-- then, only after all the desired tables are compressed (in 2005 0r 2008 as above) run the console command
DBCC SHRINKDATABASE(DatabaseName,0)
-- recover the free space (unless you need it for short term db growth)
-- the 0 value after the DatabaseName can be replaced with the amount of space you want to leave free
-- for expansion, normally if you have huge decimal types in tables with millions or rows
-- you'll see a huge difference in size of the db after the shrink.


Please take care while you are doing your big table conversions and make sure that you have enough space for the whole table to be added onto the MDF file and relatively heavy load on the LDF [log] data file. What you can do is switch to bulk mode (set recovery level) while you are doing the compression, then re-enable Full/Bulk or whatever recovery mode you were using before.  Start with the smaller objects to compress first, so that progressively you free up space without so much risk of swelling, as if you were a pilot, easing up the throttle gracefully.

Basically what you are doing is clamping down on the wasted space per line with respect to Row level compression in SQL 2008 (characters even get dictionary compression, and also prefix compression), which is what SQL 2005 SP2 calls vardecimal (limited to that column type only however). Obviously, the 2008 higher-end editions have upped the cost for you to have this functionality, but it goes with the territory – a large organisation has major space to gain usually, and don’t forget that it’s not only the space in the database you are gaining, it’s all the cumulative space in backups over time you'll save, plus all the increased time (performance) for execution of queries - one could argue simply that by taking the huge number of times a query costs in I/O wait, multiplied by its frequency of execution to get the I/O hours saved. Hey boss, where's my bonus, I just saved the company n,000 hours of IO execution time:) Or better yet calculate your SAN $ per GB per cost, multiplied by the amount of gigabytes saved. Remember that clients will get their data result sets in less than half the time in most cases!

You could do this in the temp database also, rebuild using the temp while adding the compression to the specific object during creation, but make sure your temp is as big as the object you are rebuilding – such as the index+table data size, with a good fifty percent on top of that to be sure you’re not going to run out for other applications.

The Data Compression Wizard in SQL Server Management studio demystifies much of this, if you are already lost. Mind you, it’s not very efficient if you have to do this on a whole bunch of objects, in that case you would be better off using T-SQL (and this great script to prepare uncompressed object from SQL Solace, prepare ROW compression first, then PAGE). Sort your tables by the largest first (data pages should be in the thousands), and evaluate the space savings. If you are in the millions of data pages, use this compression – as mentioned before even in SQL 2005 post Service Pack 2 build you can take advantage of row compression by means of VARDECIMAL (but not page or row compression sadly).

If you are using tables with multiple non-clustered indexes, only compress those indexes that are used occasionally. Heavily used indexes are to be avoided therefore – so, as always, to be sure TEST, TEST, TEST....on several servers, your test environment, your disaster recovery environment, your dev., and even your laptop (still enjoying disk performance for databases on SSDs, and thankfully the price per GB has come down to around $1). Check out compression on your development environment just to save space on large tables –b/c Dev environments are usually starved for data, and then just watch what happens over the next while....check and see if queries are taking forever to run, and let a few of the developers know – maybe they’ll see something you missed too. SO SQL DBAs - get out there and compress!


BONUS
- After reading up on Index options, I noticed, way down at the very bottom of the Alter Index page, another option to save disk space and improve performance (SQL 2008+), was that you can compress Indexes also:

ALTER INDEX IX_INDEX1
ON TableName
REBUILD
WITH ( DATA_COMPRESSION = ROW )
GO
ALTER INDEX IX_INDEX1
ON TableName
REBUILD
WITH ( DATA_COMPRESSION = PAGE )
GO

Friday, January 11, 2013

SQL Server 2012 with SP1 Installation Step by Step Guide - Saving System Admins, DBAs, Developers and Support Technicians A Wee Bit O' Misery

Since the last guide was quite popular (over 4k distinct views for SQL Server 2008 R2), I've uploaded to SkyDrive another complete step by step guide for a Microsoft SQL Server installation. This time the Guide is for a SQL Server 2012 with Service Pack 1 installation, because I prefer to see this done correctly, rather than butchered :)
I hope it helps save some time for those dealing with this vast task, including how to install a service pack and where to find the latest downloads. Furthermore, I have incorporated post-installation settings and validation, thus as Robert Davis states, you can 'put a better SQL Server into Production.'
Here is the Guide also in an Embedded Version:




Enjoy, comments to improve are welcome :)

Wednesday, December 26, 2012

Querying the Procedural Cache: Just in Time for Montreal’s Biggest Snowstorm Since '71 (45cm)


I sincerely wish that everyone enjoyed their recent holidays and recent downtime with family, and pass on my hope for peace, happiness and health for 2013.

The goal of this post is to aid in the understanding of the procedure cache and execution plans to ensure we use fewer resources, with the desire for database queries to run optimally.  Better means higher throughput, more concurrency and fewer resources – as described by SQL Server MVP Joe Webb during a session at SQLTeach 2009 in Vancouver (my original hometown, photos from/of new convention centre below), where I first took a serious look at this subject. Of course, this is simply a quick blog post, so for those of you desiring far greater detail, please see Grant Fritchey’s 2nd edition, and FREE e-book on Execution Plans.

Vancouver's New Convention Centre (Dexigner.com)


From the result set tabs provided from the query below (thanks to the Dynamic Management View Exec Query Stats), the last column on the right produced by querying the procedural cache, provides a graphical text-based, or XML-based representation for the data retrieval methods chosen by the Query Optimiser. In understanding the execution plan, we read it from right to left, playing close attention to the relative execution costs (if high) for specific statements that are displayed: such as physical operation, logical operation, i/o cost, cpu cost, rows, and row size. For easier readability, zooming in and out are functionalities available too, as well as the properties window,which I usually have hidden unfortunately...and forget about (to my own disappointment).

-- view all the recent queries, top fifty in this case
SELECT TOP 50 DB_Name(qp.dbid) as [Database] , qp.number , qt.text as [queryText],qs.total_logical_reads as [Reads],  SUBSTRING(qt.text, (qs.statement_start_offset/2) + 1,
    ((CASE statement_end_offset
        WHEN -1
        THEN DATALENGTH(qt.text)
ELSE qs.statement_end_offset
END - qs.statement_start_offset)/2) + 1) as [StatementText],qp.query_plan
FROM sys.dm_exec_query_stats as qs
      CROSS APPLY sys.dm_exec_query_plan(qs.plan_handle) as qp
      CROSS APPLY sys.dm_exec_sql_text(qs.sql_handle) as qt
ORDER BY qs.total_logical_reads DESC

There may be a thousand ways to resolve a query, or to the find one that is good enough to return the results in the most efficient way – this is what the query optimiser (QO) does. The QO uses a cost-based algorithm which estimates the best way for the query to run as it passed from relational engine, before in binary to the storage engine.  Before it leaves the relational engine of SQL Server, it processes the query and may change the plan, or re-estimate what the execution plan would be.

For example, as part of your stored procedure, you are doing recurrent updates, and then perhaps you may have changed up to 50% of your rows, and thus the table statistics have changed consecutively. Erstwhile, the plan that created originally has changed due to the statistics. Maybe now there is a better index to use or foundation has changed to the point where the stored procedure needs to recompile. In reality, it’s best to make your decisions on the Actual plan, and not the estimated plan – to see what I mean, in SQL Server Management Studio click Query, and select Include Actual Execution Plan, or, better yet, to see the full before/after story compare with select Query, Display Estimated Plan.

You will want to watch out for clustered index scans, since they are synonymous with a Table scan, and that is to be avoided or indicates there is a problem with the query.  
If you see a table scan, change the heap to a clustered index, or add an index to the specific column necessary - clustered index seeks are a good sign optimisation wise.  If the tables are of an insignificant size however, don't bother splitting hairs. 
An interesting way to do trial and error is to make different versions of the query and let it give you estimated/actual plans for all the batches and to compare each iteration of the result set.  Furthermore, to quickly read the execution plans findings, the tooltips are a lot better and give way more details since 2008, which even tells you now, correlating with an index-related DMV, if an index needs to be created.



BTW - Alternate methods to produce execution plans are by using: Profiler, performance dashboard and 2008`s data collector.


Tuesday, November 06, 2012

Another Dive into Transaction Log File Forensics

The Stawamus Chief, Squamish B.C.

 Over the past three years since I first took a look at the insides of a Transaction Log file, I have noticed some more great products to enable forensics such as ApexSQL (which makes the job simple), Lumigent Log Explorer, and other Free tools mentioned below, as well as real world scenarios that describes every step BlackHat.com would perform in the deepest dive.

Why do you care, as a DBA, or a Developer for that matter, about what is happening in the Transaction Log? The raison d’être of a transaction log file is to write all the necessary information we need to recuperate any and all activity happening against the database, hence the interest.  Every SQL Server database must have at least one log file. Here are a few ways to take a brief look what is inside the log file itself:

SELECT * FROM  ::fn_dblog(DEFAULT, DEFAULT) AS l  -- more examples, and details, below
dbcc log(<DatabaseName>, 3)
-- 0 for minimum info, 1 for more, 2 for detailed, 3 full, 4 full+hex

Perhaps you are on auditing project, and your requirement is to be able read the active log files, as well as those that were archived.  The archived logs gave us the option, with the help of third party tools (or thanks to in part the query above), to effectively interrogate the log file as if it were one mega table. The way that Lumigent Log Explorer's documentation describes it: 'to assist you in solving or recovering from problems that may occur in a typical database system' - gets the heart of what could be a potential point-in-time restore.  Nota Bene: Please use Full recovery model if this is your requirement or if you are in doubt (thankfully it's a database model default setting) here are the many reasons why.  

And according to MVP Siddhart Mehta, another free tool is available which can read and display contents of transaction log files - Internals Viewer for SQL Server (IV). The three main components are: Allocation Map, Page Viewer, and Transaction Log Viewer. IV integrates with SSMS and works for both SQL Server 2005 and 2008. The tool is limited, so expect to pay for tools when full functionality is required.

My view is that you may end up with a mystery transaction at one point that no logic can explain, and thus your database integrity is in question - not a place where any DBA wants to be naturally. Empowering yourself to dig into the log file and resolve these types of mysteries is the main point of this post, because you will be able to find out what the values were before/after a change to the database, and who/what application has committed the change, whereas before one typically disregards anomalies for lack of forensic tools and time.  This gives us motivation to ensure that log files are archived, since we're following Erasmus' Adages proverb to 'leave no stone unturned,' with respect to resolving such mysteries.

Given that you can query the log file (you will see transactions as BEGIN_XACT , COMMIT_XACT), it is even therefore possible to raise alerts for undesirable activity, e.g. someone executing data definition language in production. Combined with Database Mail and SQL Server Agent this can be automated too, or in the case of Lumigent Log Explorer, one can configure an alert for each DDL/DML command, which is perhaps useful - to filter out problems in development.  The approach of monitoring objects during manipulation or creation will allow you to take control of an environment progressively and proactively.

Here are a few of the prominent columns you see in the
Transaction Log itself (full list here – Appendix B):

    ABORT_XACT Indicates that a transaction was aborted and rolled back.
    BEGIN_CKPT A checkpoint has begun.
    BEGIN_XACT Indicates the start of a transaction.
    BUF_WRITE Writing to Buffer.
    COMMIT_XACT Indicates that a transaction has committed.
    CREATE_INDEX Creating an index.
    DELETE_ROWS Rows were deleted from a table.
    DELETE_SPLIT A page split has occurred. Rows have moved physically.
    DELTA_SYSIND  SYSINDEXES table has been modified.
    DROP_INDEX Dropping an index.
    END_CKPT Checkpoint has finished.
    EXPUNGE_ROWS row physically expunged from a page, now free for new rows.
    FILE_HDR_MODIF  SQL Server has grown a database file.
    FORGET_XACT Shows that a 2-phase commit transaction was rolled back.
    FORMAT_PAGE  Write a header of a newly allocated database page.
    INSERT_ROWS  Insert a row into a user or system table.
    MARK_DDL Data Definition Language change - table schema was modified.
    MARK_SAVEPOINT designate that an application has issued a 'SAVE TRANSACTION' command.
    MODIFY_COLUMNS  Designates that a row was modified as the result of an Update command.
    MODIFY_HEADER  A new data page created and has initialized the header of that page.
    MODIFY_ROW  Row modification as a result of an Update command.
    PREP_XACT Transaction is in a 2-phase commit protocol.
    SET_BITS Designates that the DBMS modified space allocation bits as the result of allocating a new extent.
    SET_FREE_SPACE  Designates that a previously allocated extent has been returned to the free pool.
    SET_MASK_BITS
    SORT_BEGIN A sort begins with index creation. - SORT_END end of the sorting while creating an index.
    SORT_EXTENT Sorting extents as part of building an index.
    UNDO_DELETE_SPLIT The page split process has been dumped.
    XACT_CKPT during the Checkpoint, open transactions were detected.