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   Download: http://www.microsoft.com/en-us/download/details.aspx?id=21101

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).  

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 candidate 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.











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

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 :)

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 2^nd 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.

Another Dive into Transaction Log File Forensics

 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.

Quebec Human Rights Commission Ignores Racism Documented by CRT Judge (Civil Evidence), Ignores E-mail Evidence & Botches Up Investigation

After seeking justice in this matter for years, I am obliged to bring this issue up again (since it affects millions of people in our province), regarding multiple incidents that occurred, while working as a Database Administrator for the Provincial Government.

This is just a quick post to make public the details of the failure for the Rule of Law to be upheld by the Quebec Government,
and especially the Quebec Human Rights Commission (QCHRC). Note that this is the second Commission in Quebec I have been working with to seek justice, since this is litigation with a double recourse.

The composition of this registered letter is in response to the QCHRC impression they give to ignore Civil Evidence and Failing to Conduct a Proper Investigation regarding my complaint against the Caisse de dépôt et placement du Québec (QC's deposit and investment Fund, also known as CDP Capitol), a public (with sensitive info masked) signed copy you can find here below on SkyDrive.

For working links in the web-based version of Microsoft Word, please use this link:

Latest Presentation on SQL Server Mirroring Given at Transcontinental's Offices

With a handful of System Administrators, Network Specialists and DBAs, I gave my third presentation on SQL Server Mirroring this afternoon.
Please find the Presentation Slide Deck here on SkyDrive.



Contenu - Contents

Introduction to-à Microsoft SQL Server Mirroring – Haut disponibilité avec des bases de données en mirroir. An updated version of this presentation will be given (mais en bilingue) based on several Blog posts, including: http://dbhive.blogspot.ca/2010/10/vermont-sql-server-user-group.html


- Connection Strings: http://dbhive.blogspot.ca/2010/10/connection-strings-database-mirroring.html
- Deep Dive (for those who are not satisfied with an intro, and need to use certificates, and complete code version of a mirror setup - mise en place d'un mirror par script, et sans domaine) : http://dbhive.blogspot.ca/2010/12/notes-from-mirroring-deep-dive-session.html

Note that if you want to group Mirrored databases, to mimmick Availability Groups in SQL 2012, you simply have to create a SQL Agent Job with a step that has the ALTER Database DBName Set Partner Failover; for each database you want in the group.

Two Ways to Document Your SQL Server Infrastructure Quickly

To collect information for your SQL Server Infrastructure, there are two ways I can recommend.
 
The first, as mentioned on Technet, is to execute the following parameter details on any SQL Server installation (I tested back to 2000), by run the following command.

exec xp_msver "ProductName", "ProductVersion", "Language", "Platform", "WindowsVersion", "PhysicalMemory", "ProcessorCount"

-- result set is a table, with a row for each parameter

The second, and my preference as best pratice 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,

case

when  exists (select * from msdb.dbo.backupset where name like 'data protector%') then 'HPDPused'

else 'Local Copy_Only & Commvault' -- where you would replace the

-- strings with your respective third party or native backup solution

end

To run either of these queries across multiple servers in SSMS 2008 (assuming that you have more than one), under Registered Servers, right click on Local Server Groups, and select New Query.

References:  See all the recent Technet SQL Server Tips

Optimise Your Disk Subsystem I/O with An Index File Group and Index Compression

As a DBA who is always seeking solutions to performance bottlenecks, amidst the daily rituals of validating backups and other regular tasks, profiting from File Groups on differing disk subsystems, in this case for Indexes, arose as flavor of the week. With this option, note that I am assuming one has setup the SQL Server instance with several disks available. If you are not on Microsoft SQL Server Enterprise Edition the usual Index option, ONLINE=ON, will unfortunately not be available, however if you perform this task during a maintenance window with Standard edition, this problem is moot.  The distinction must be made that this is not a regular maintenance task, and this is not to rebuild an index, but to recreate an index. We are replacing the index location on the disk subsystem with the useful option DROP_EXISTING=ON while referring to a different Filegroup location (ON [Indexes]) at the end of the script.

Here is the example:

/****** Object:  First Add an Index File Group Script Date: 06/06/2012 4:55:16 PM ******/
Create FILEGROUP [Indexes]

( NAME = N'DBname_Indexes', FILENAME = N'DiskName:\DataFileFolder\DatabaseName_Indexes.ndf' ,

SIZE = 10GB , MAXSIZE = 50GB , FILEGROWTH = 1GB )

/****** Object:  Index [SampleTableName _IDX_000]    Script Date: 06/06/2012 4:55:16 PM ******/

CREATE NONCLUSTERED INDEX [SampleTableName_IDX_000] ON [dbo].[ SampleTableName]

(

            [ClientId] ASC,

            [InvNo] ASC,

            [TxNo] ASC

)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, SORT_IN_TEMPDB = OFF,

DROP_EXISTING = ON, ONLINE = ON, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [Indexes]

GO

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 T1

REBUILD

WITH ( DATA_COMPRESSION = PAGE )

GO

Happy recreation of Indexes and I/O balancing across your disks.

Bartholeme Island Boardwalk, Galapagos Islands, Ecuador