updates.ssv.com

SQL Server Community

Michael K. Campbell — 2009-10-08T13:41:43-07:00

My personal philosophy is that when you give, you actually don't lose anything. Instead, you gain something. Sometimes putting that philosophy into practice can be tough. But I'm convinced that putting the true spirit of giving to the test is a great way to build character – and make a better world.

Consequently, my most recent DevProConnections Update was about unsung benefits of SQL Server. Specifically, I addressed three aspects of the SQL Server Community that I really love and appreciate.

Two other recent events have also helped increase my respect for the SQL Server Community as well.

Interactions with Other Consultants
Over the past few weeks I've had the opportunity to speak with a couple of other SQL Server Consultants – that I didn't know previously.

During these interactions we've talked shop a bit and/or talked about ways to lend each other a hand with various projects here and there.

In each case though, these interactions have been great – meaning not only professional but cool in the sense that it almost seemed like I was talking with instant/old friends. Which is all the more cool given that these other consultants are technically my competition.

Brent Ozar's Interviews of Pass Candidates
Another great aspect of the SQL Server Community that I've enjoyed over the past few weeks has been Brent's great series of interviews with PASS Board Member Candidates. Brent sent off a questionnaire to this year's candidates, and has been posting their replies on his blog:

Reading through these replies so far has been very enlightening – and it's great to see these candidates express similar sentiments about how great the SQL Server community is and how great it is to belong to that community.

It's a Good Time to be a DBA

Michael K. Campbell — 2009-09-21T17:21:50-07:00

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:

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

Michael K. Campbell — 2009-09-15T12:45:25-07:00

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

Michael K. Campbell — 2009-09-15T11:14:22-07:00

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

Michael K. Campbell — 2009-09-15T08:43:27-07:00

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

Michael K. Campbell — 2009-09-14T13:19:01-07:00

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.

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:

After:

Security Non-Issue/Publicity Stunt

Michael K. Campbell — 2009-09-03T10:06:59-07:00

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.

Worthwhile SQL Server Blogs

Michael K. Campbell — 2009-08-27T19:26:41-07:00

Not sure what my deal is, but I subscribe to tons of SQL Server blogs, yet actually read very few. Actually, probably part of why I don't blog too much on my own is that it seems like so much about SQL Server is already covered. And, sadly, many blog posts out there are commonly just a re-hash of things that have already been covered (to some degree or another). Obviously, there are some big exceptions, and so on.

But over the past few months I've really noticed that I've enjoyed two blogs on a very consistent basis, so I thought I'd send out links to them as well.

The Rambling DBA
Haven't quite figured out why, but Jonathan's blog posts at The Rambling DBA almost always get my full attention. He's usually writing about great topics that seem to be aligned with things I'm frequently working on or with. So make sure to check out his blog (though he's apparently been called up to active duty – so get him before his… hiatus kicks in).

Brian Egler's SQL Server Strategies
I've been keeping an eye on Brian's posts at his SQL Server Strategies blog for quite a while now. He occasionally hits… drier spells where there isn't as much coming from his spoutlet, but whenever something comes out, it's almost always awesome. That, and he's really been on a kick lately – with a definite up-tick in the amount posts of late.

My Blog?
And.. for those of you wondering what's up with my blog here? I'll let you know when I get it figured out. ;)

Largely (i.e., my excuse), I've just been swamped with a couple of big projects, some well-earned vacation this summer, and preparing for 3 hours of kick-ass content for my upcoming Power-Up with SQL Server presentations. So… my goal is to start blogging a bit more here and there over the next few weeks, and then by mid September I'm also hoping to get started on a couple new serieseseses (wish there was a clean way to pluralize the word series) of videos.

Tip: Make sure SQL Server AWE Memory is Enabled

Michael K. Campbell — 2009-08-06T16:47:06-07:00

If you're running a 32-bit version of SQL Server, you should make sure that AWE Memory is enabled. Amazingly, I find that pretty much half of the systems I audit aren't configured correctly.

Accordingly, I wrote about it in my latest DevProConnections Update/Newsletter. So check it out – as you could be hosing yourself.

Hyper-V Unleashed: Resources

Michael K. Campbell — 2009-07-21T11:11:47-07:00

I just finished up my Hyper-V Unleashed online presentation – and was happy to see that there were over 600 attendees.

At the end of my presentation I provided a slide with a list of additional resources – and a number of people have asked for those links. There were also a couple of great questions that I've touched upon with additional resources as well.

------------------------------------------------------------

Hyper-V Home on Technet:
- http://technet.microsoft.com/en-us/library/cc753637.aspx

Understanding Microsoft Virtualization Solutions eBook:
- http://csna01.libredigital.com/?urmvs17u33

Error Code Dictionary:
- http://technet.microsoft.com/en-us/library/dd578337.aspx

John Howard / HVRemote:
- http://blogs.technet.com/jhoward/archive/2008/11/14/configure-hyper-v-remote-management-in-seconds.aspx
- http://blogs.technet.com/jhoward/archive/2008/03/28/part-1-hyper-v-remote-management-you-do-not-have-the-requested-permission-to-complete-this-task-contact-the-administrator-of-the-authorization-policy-for-the-computer-computername.aspx

VHD Servicing Tool 2.0
- http://blogs.msdn.com/taylorb/archive/2009/01/06/vhd-servicing-tool-2-0.aspx
- http://technet.microsoft.com/en-us/library/cc501231.aspx

VHDTool
- http://code.msdn.microsoft.com/vhdtool

QUESTION: Transferring OEM licenses to work as guest licenses on Hyper-V hosts:
- http://blogs.technet.com/jhoward/archive/2005/05/27/405432.aspx
The above information is QUITE dated – but still accurate: You typically can NOT transfer OEM licenses to new hardware – so consolidating OEM licenses runs afoul of licensing terms in most cases. However, SOME OEM terms will allow you to transfer licenses to new hardware - you just need to consult your specific terms from your vendor.
Microsoft will let you look up those terms here: (Only works in IE)
- http://www.microsoft.com/about/legal/useterms/
Likewise, you can also call 800-426-9400 to get more information/help.

QUESTION: Hyper-V R2
- http://technet.microsoft.com/en-us/library/dd446676(WS.10).aspx

QUESTION: More Details on Enlightenments (including Win2k3 Device Drivers)
- http://blogs.msdn.com/tvoellm/archive/2008/01/02/hyper-v-integration-components-and-enlightenments.aspx
- http://social.technet.microsoft.com/Forums/en-US/winserverhyperv/thread/1b755fa5-1023-4817-98b3-263368edc022