« August 2009 | Main | October 2009 »

It's a Good Time to be a DBA

It's always a good time to be a DBA (assuming you don't mind a healthy dose of stress here and there).

But it would appear that being a DBA right now also makes sense while the economy is in the crapper.

And, since a picture is worth a thousand words, check out the great graph by the fine folks out at indeed.com:

ItsGoodToBeTheDBA

 

Maybe it's just me, but when the economy was doing well (or seemingly doing well) a while back, it seems like companies were getting rid of DBAs at alarming rates. Now it seems like companies are paying a premium to keep their data safe?



Power Up with SQL Server: SQL Server Performance

Here's a list of additional resources for attendees of the SQL Server Performance session from today's Power Up with SQL Server Event.

Feel free to contact me if you're looking for a specific resource, or had additional questions that weren't answered in our session.

Performance Background

B-Trees (Wikipedia)
http://en.wikipedia.org/wiki/B-tree

SQL Server Indexing Basics
http://www.sqlteam.com/article/sql-server-indexes-the-basics
http://www.simple-talk.com/sql/learn-sql-server/sql-server-index-basics/
http://msdn.microsoft.com/en-us/library/aa964133%28SQL.90%29.aspx

Clustered Indexes vs Heaps
http://sqlcat.com/whitepapers/archive/2007/12/16/comparing-tables-organized-with-clustered-indexes-versus-heaps.aspx

The Benefits of Clustered Indexes
http://www.sqlservercentral.com/articles/Performance+Tuning/clusterthatindex/952/

Index Fragmentation
http://www.brentozar.com/archive/2009/02/index-fragmentation-findings-part-1-the-basics/
http://www.sql-server-performance.com/articles/per/index_fragmentation_p1.aspx

BookMark Lookups
http://www.sqlservercentral.com/articles/Performance+Tuning/bookmarklookups/1899/
http://sqlblog.com/blogs/linchi_shea/archive/2008/07/06/performance-impact-bookmark-lookup-is-expensive-even-in-memory.aspx

Performance Troubleshooting

Generic KB for Performance Troubleshooting

http://support.microsoft.com/?id=298475

Troubleshooting SQL Server 2008 Performance Problems
http://sqlcat.com/whitepapers/archive/2009/04/14/troubleshooting-performance-problems-in-sql-server-2008.aspx

Troubleshooting SQL Server 2005 Performance Problems

http://sqlcat.com/whitepapers/archive/2007/11/19/troubleshooting-performance-problems-in-sql-server-2005.aspx

 

Performance Monitor

Setting up Traces (on Win2000,2003)
http://www.sqlserveraudits.com/instructions/perfmon.aspx

Trace Templates
http://www.sqlserveraudits.com/instructions/PerfmonTraceTemplates.zip

Analyzing Performance Counters
http://msdn.microsoft.com/en-us/library/ms998579.aspx
Distilled version of above counters:
http://www.sqlserveraudits.com/instructions/PerformanceCounters.doc


SQL Server Profiler

Using SQL Server Profiler
http://msdn.microsoft.com/en-us/library/ms187929.aspx
http://www.sql-server-performance.com/tips/sql_server_profiler_tips_p1.aspx

Establishing a baseline
http://www.sqlserveraudits.com/instructions/BaseLine.sql.txt

Finding Top Offenders
http://www.sqlserveraudits.com/instructions/TopOffenders.sql.txt

ClearTrace
http://www.cleardata.biz/cleartrace/

 

Index Tuning Problems

SARGability
http://weblogs.sqlteam.com/dang/archive/2009/03/07/Low-Hanging-Fruit-of-Sargable-Expressions.aspx
http://www.sqlmag.com/articles/index.cfm?articleid=99148&

Index Selectivity
http://www.sql-server-performance.com/articles/per/index_not_equal_p1.aspx

Index Selectivity and Column Order
http://sqlserverpedia.com/wiki/Index_Selectivity_and_Column_Order



Power Up with SQL Server: SQL Server High Availability

Here's a list of additional resources for attendees of the SQL Server High Availability session from the Power Up with SQL Server event.

Feel free to contact me if you're looking for a specific resource, or had additional questions that weren't answered in our session.

Decreasing Down Time

SQL Server Enterprise Edition
http://www.microsoft.com/sqlserver/2008/en/us/editions-compare.aspx

Partial Database Availability
http://sqlcat.com/whitepapers/archive/2007/11/21/partial-database-availability.aspx

Moving Logins for Log Shipping and other HA needs
http://www.sqlservervideos.com/video/copying-and-moving-sql-server-logins/


SQL Server Clustering

Stretch Clusters
http://blogs.technet.com/rob/archive/2009/03/15/sql-server-2005-multi-site-clustering-with-windows-server-2008.aspx

Multi-Site Clusters
http://www.microsoft.com/windowsserver2008/en/us/failover-clustering-multisite.aspx

Failover Cluster Documentation
http://download.microsoft.com/download/6/9/D/69D1FEA7-5B42-437A-B3BA-A4AD13E34EF6/SQLServer2008FailoverCluster.docx

SQL Server 2008 Failover Clustering Whitepaper
http://sqlcat.com/whitepapers/archive/2009/07/08/sql-server-2008-failover-clustering.aspx

Denny Cherry's SQL Server 2008 Clustering Articles in SQL Magazine
http://www.sqlmag.com/Article/ArticleID/102461/sql_server_102461.html
http://sqlmag.com/articles/index.cfm?articleid=100400
http://sqlmag.com/articles/index.cfm?articleid=100398

 

SQL Server Log Shipping

Log Shipping Overview
http://msdn.microsoft.com/en-us/library/ms187103.aspx

Step By Step to Configuring Log Shipping
http://msdn.microsoft.com/en-us/library/ms190640.aspx

Log Shipping Fail Over
http://msdn.microsoft.com/en-us/library/ms191233.aspx

Log Shipping and Additional Dependencies
http://msdn.microsoft.com/en-us/library/ms187580.aspx

 

SQL Server Database Mirroring

Database Mirroring Overview
http://msdn.microsoft.com/en-us/library/ms189852.aspx

Providing High Availability with Database Mirroring
http://sqlcat.com/whitepapers/archive/2008/09/02/sql-server-replication-providing-high-availability-using-database-mirroring.aspx

Implementing Failover with Database Mirroring
http://sqlcat.com/whitepapers/archive/2007/12/16/implementing-application-failover-with-database-mirroring.aspx

Performance Best Practices for Database Mirroring
http://sqlcat.com/whitepapers/archive/2007/11/19/database-mirroring-best-practices-and-performance-considerations.aspx

Using Database Mirroring AND Log Shipping Together
http://sqlcat.com/whitepapers/archive/2008/01/21/database-mirroring-and-log-shipping-working-together.aspx

Combining Log Shipping and Replication
http://msdn.microsoft.com/en-us/library/ms151224.aspx

Log Shipping for Reporting Purposes
Think house-boat: Log Shipping is good for HA OR Reporting. Don't try to blend both into same solution.
http://msdn.microsoft.com/en-us/library/ms189572.aspx

 

SQL Server Replication

Books Online Overview
http://msdn.microsoft.com/en-us/library/ms151198.aspx

Management of Replication Agents
This is where the BULK of your more ADVANCED performance and configuration options will be.
http://msdn.microsoft.com/en-us/library/ms152762.aspx

Great Resource for Replication Troubleshooting
http://www.replicationanswers.com



Power Up with SQL Server: SQL Server Virtualization

Here's a list of additional resources for attendees of my SQL Server Virtualization Session. Feel free to contact me if anything you were hoping to find here isn't listed.

There were also lots of questions towards the end of this session – if I wasn't able to answer your question, just contact me directly.

Virtualization Background

Demystifying SQL Server Virtualization
http://www.sqlmag.com/Article/ArticleID/99841/sql_server_99841.html

SQL Server Virtualization FAQs
http://www.sqlmag.com/Article/ArticleID/97264/SQL_Server_Virtualization_FAQs.html

HyperVisors (Wikipedia)
http://en.wikipedia.org/wiki/Hypervisor

Hardware Assisted Virtualization
http://virtualizationreview.com/articles/2009/01/01/hardwareassisted-virtualization.aspx

Hyper-V vs VMware
(though published earlier this year, this article is already out of date)
http://searchservervirtualization.techtarget.com/news/article/0,289142,sid94_gci1346926,00.html

SQL Server 2008 Virtualization Page
http://www.microsoft.com/sqlserver/2008/en/us/virtualization.aspx

 

Virtualization Benefits

VMware's vSphere 4 and SQL Server
http://blogs.vmware.com/performance/2009/06/sql-sever-performance-on-vsphere.html
http://www.vmware.com/files/pdf/perf_vsphere_sql_scalability.pdf

VMware Marketing about Virtualization/SQL Server Benefits
http://www.vmware.com/solutions/business-critical-apps/sql/resources.html

Microsoft Marketing about Virtualization/SQL Server Benefits
http://blogs.technet.com/dataplatforminsider/archive/2009/09/02/microsoft-virtualization-best-choices-for-sql-server.aspx


Virtualization Concerns

Microsoft's Official Support statement for Virtualized SQL Server
http://support.microsoft.com/KB/956893

Detailed Licensing Information from Microsoft
http://www.microsoft.com/sqlserver/2005/en/us/Special-Considerations.aspx

Less Detailed Licensing (but still overly complex)
http://download.microsoft.com/download/6/c/c/6ccc82b3-d254-4cb7-bada-62a720ae4598/Licensing_Microsoft_Server_Products_in_Virtual_Environments.doc

 

Performance Monitoring/Tuning

Performance Monitoring on VMware Guests
http://sqlblog.com/blogs/rick_heiges/archive/2009/06/03/perfmon-for-vmware-esx.aspx

Performance Monitoring on Hyper-V Guests
http://msdn.microsoft.com/en-us/library/cc768535%28BTS.10%29.aspx
http://blogs.msdn.com/tvoellm/archive/2009/04/23/monitoring-hyper-v-performance.aspx

 

Stress Testing

SQLIO
Stupid Name. Fantastic tool (one that I recommended for SQL Mag's recent list of free tools)
http://www.microsoft.com/DOWNLOADS/details.aspx?familyid=9A8B005B-84E4-4F24-8D65-CB53442D9E19&displaylang=en

IOMeter
Not a bad tool – harder to set up and use than SQLIO though
http://www.iometer.org/

Memory Testing
As Linchi points out: data is old/dated. But the concepts here are great.
http://sqlblog.com/blogs/linchi_shea/archive/2009/07/29/performance-impact-memory-effectiveness-of-virtualization.aspx 

Virtualized IO

SQL Server and SAN Interactions
http://blogs.msdn.com/joesack/archive/2009/01/28/sql-server-and-hba-queue-depth-mashup.aspx

SQL Server, SANs, and Virtualization
http://www.sqlservercentral.com/articles/SAN/65101/

VMware Guidance on SAN Design/Implementation
(a bit dated, but concepts are still great)
http://www.vmware.com/pdf/vi3_san_design_deploy.pdf

 

SQL Server Best Practices

Best Practices Home
http://msdn.microsoft.com/en-us/sqlserver/bb671432.aspx

Predeployment IO Best Practices
http://technet.microsoft.com/en-us/library/cc966412.aspx

SQL Server on Hyper-V Best Practices and Performance Recommendations
http://sqlcat.com/whitepapers/archive/2008/10/03/running-sql-server-2008-in-a-hyper-v-environment-best-practices-and-performance-recommendations.aspx

Performance Best Practices for vSphere 4.0
http://www.vmware.com/pdf/Perf_Best_Practices_vSphere4.0.pdf

Microsoft SQL Server and VMware Virtual Infrastructure (Best Practices)
http://www.vmware.com/resources/techresources/10002

SQL Server Performance in VMware 3
http://www.vmware.com/files/pdf/resources/sql_server_performance_vi3_wp.pdf



Poor Man's Scripting Tip

I love that you can script Publications for SQL Server Replication. I find that this provides a really good way to 'back up' all of your settings in case you need to re-initialize, make any big changes, or run into any problems – because serialized configuration details are tons easier to manage in the form of a script than trying to re-create a replication topology by virtue of remembering all of the options and configuration details that you specified (sometimes up to a few months) earlier on.

I also find that the UI and wizards used to set up publications also don't provide enough control or detail. But, I don't like generating entire publications from hand either. So generating basic publication details with SQL Server Management Studio and then outputting those details to script is an ideal way to get me 90% of where I need to be. And then I can just modify the generated scripts as needed.

Repl-Before

One thing I don't like though, is that these generated scripts suffer from one-line-itis – in the sense that SSMS generates scripts with entire calls to Replication sprocs on a single line. Which can be a total pain to sort through when you're dealing with sprocs that have 10, 20, or more parameters.

Accordingly, a trick I use when playing around with SSMS generated scripts (not just replication scripts either) is to use poor-man's reformatting. And to do that, I just do a Find and Replace, specify that I want to use Regular Expressions, and then tell it to push the start of every parameter on to its own line – by replacing the literal text of @ with an @-sign preceeded by a carriage return and a tab – resulting in instantly formatted text. Or, as you can see from the screenshot below, I just replace \@ with \n\t\@.

 

Find and Replace:

Repl-Find

After:

Repl-after



Security Non-Issue/Publicity Stunt

I keep seeing tweets about how Microsoft is ignoring a security flaw. One that involves a potential risk for passwords to be exposed.

I think the folks who found this problem are likely VERY intelligent. And I think raising this flaw was a good move on their part. And I don't think that they're guilty of a publicity stunt. I think folks like these are. I also think they're dead wrong – because this isn't an issue. 

How can Exposing Passwords NOT be a security issue?
At the heart of this problem is the fact that passwords for SQL Server Authentication users are stored, in memory, in plain text. Granted, that seems like a bit of a problem and I wouldn't be surprised to see it corrected some day (maybe in future service packs and/or future versions). But I certainly agree that it doesn't need any immediate attention. Nor, in fact, do I think it really needs any attention.

Why don't I care? Because in order to take advantage of this information, you would need to be a part of the Trusted Computing Base (TCB) on a server to gain access to these passwords. In other words, you'd either have to be running code as an Administrator or as Local System/etc. So, in other words, to be able to take advantage of this exploit, you'd need to be an administrator on the box you're targeting.

An Analogy
My kids all have bedroom doors that lock. My bathrooms all have doors that lock.

Those locks offer some great security – when you close the door from the inside, and turn the little knob inside the door-knob, you're locked in.

Sadly though, because my children (when they were smaller) were able to accidentally lock either their bedroom door or the bathroom door without being able to figure out how to unlock it, I've also got little 'keys' scattered in my house on top of door frames. And these keys are either nothing more than little thin slats of metal provided by door knob-manufactures, or cannibalized sardine-can openers. Point is: any parent with small children knows exactly what I'm talking about.

And in relation to this, what this security exploit is crying wolf about is analogous to saying that if thieves are able to break into your home, they might be able to unlock your kid's doors.

But what about…?
And as for any arguments that people testers or others might have admin perms when they shouldn't have, those are are all moot. If you trust those people enough to give them access to your systems at TCB level, or if you trust code enough to run it at that level, then you've got way bigger problems than the fact that code or a rogue admin might find out that Ted's password over in accounting is hotdog1. And if Ted's using that password everywhere within your enterprise, then he should be connecting to your SQL Server with Windows Authentication – not SQL Server Authentication. So this shouldn't even be a problem – and if it is, it's not because of this exploit, but because you haven't secured your system as you should. In other words, about the only time when using SQL Server Authentication makes sense is when you're letting web users or other applications access your system that can't (for one reason or another) use Integrated/Windows Authentication. And being able to discover those people's or application's passwords is a moot point.