• Categories

  • Recent Posts

  • Archives

  • Copyright Notice

    Copyright © Nancy Hidy Wilson, 2010-2011. Unauthorized use and/or duplication of this material without express and written permission from this blog’s author and/or owner is strictly prohibited. Excerpts and links may be used, provided that full and clear credit is given to Nancy Hidy Wilson and nancyhidywilson.wordpress.com with appropriate and specific direction to the original content.

SQLSaturday #107 – Schedule Posted!

The session schedule for SQLSaturday #107 is now posted!  We have several MVPs as well as local speakers who will be presenting in Application Development, Business Intelligence, Database Administration and Professional Development tracks. Take advantage of this FREE day of training in Houston, April 21, 2012.  Lunch will be catered again this year by Hinze’s BBQ (and we have a vegetarian option) – for which we are requesting attendees pay $10 via Paypal when registering.  Space is limited, so register soon! Then click on the Schedule option and choose the Schedule Builder to plan your day (and help us determine which sessions will require the auditorium!).

See you there!  And plan to attend the “after party” networking event Saturday evening – more info coming soon!

SQLSaturday #107 – Sessions Announced

Schedule is now proposed (http://www.sqlsaturday.com/107/schedule.aspx) and speakers have been notified that the following sessions (updates in bold below) have been selected for presentation at SQLSaturday #107 in Houston, April 21!  Register soon as seating is limited for this event! http://www.sqlsaturday.com/107/register.aspx

AD Sessions

Boles, Kevin: SQL Server 2012 TSQL Enhancements and Additions

Boles, Kevin:  Windowing Functions: THE Reason to Upgrade to 2012

Corbett, Jack:  Introduction to Triggers

Harp, Vicky:  Edge Case Testing for the Database Professional

Hokanson, Kris:  Beyond the Where: Full Text Search tips and trick

Loski, Russel:  Introduction to the XML source

McCown, Jennifer:  Unraveling Tangled Code – A Spellbinding Tale

McCown, Jennifer:  T-SQL Brush-up:The Best Things You Forgot You Knew

Morela, Rick:  Avoid Errors on Errors

Pastrick, Wendy:  Read Dirty to Me – Isolation Levels/User Impact

Rosenberg, Mark:  Optimizing Stored Procedures

Van Dyke, Kendal: Working with XML in SQL Server

Warren, Andy:  SQL Server Security for Developers

BI Sessions

Costello, Tim:  Data Visualization primer:  What you need to know

Hotek, Michael:  Build a Data Warehouse in Less than 1 Hour

LeBlanc, Thomas:  Dimensional Modeling 101

Mitchell, Tim:  Top 10 New Features of SSIS in 2012

Mitchell, Tim:  Parent/Child Structures in SSIS

Newby, Michael D.: EXCEL @ SQL Reporting

Pearson III, William E.:  Becoming DAX: An Introduction …

Pearson III, William E.:  Many-to-Many: Multiple Calendars in a Single Cube

Serra, James:  Scaling SQL Server to HUNDREDS of Terabytes

Smith, Bryan:  Implementing Columnstore Indexes

Smith, Bryan:  An Introduction to Big Data for BI

Stein, David:  Data Warehousing – How to Convince “The Bobs”

DBA Sesssions

Assaf, William:  SQL Admin Best Practices with DMV’s

Brown, Wesley:  Benchmarking, Baselines and Monitoring Storage

Brown, Wesley:  Solid State Storage Deep Dive

Cook, John:  SQL Server Tips and Tricks

Davis, Robert:  Replication Magic: Initializing From Backups

Davis, Robert:  Strategies for Working with VLDBs

Delaney, Kalen:  Costs of Concurrency

Fal, Michael:  Eating the Elephant: SQL Server Table Partitioning

Fal, Michael: The Fabulous Query Plan Primer

Fernandez, Argenis:  Rolling Upgrades Leveraging Database Mirroring

Fernandez, Argenis:  Troubleshooting SQL Server with SysInternals Tools

Griffin, Janis:  Don’t Panic, DBAs!  Databases on VMware made easy.

Hotek, Michael:  Knowledge from Data–FileTable and Semantic Search

Hotek, Michael:  Recover from a Disaster–What to do When All Fails

Humphrey, Eric:  .NET Powers Activate! Form of PowerShell!

LeBlanc, Thomas:  Execution Plan Basics – Beginners

Leo, Christina:  SQL Server Internals:  It’s What’s for Dinner!

Mendo, AJ:  Standardize Your Environment With Best Practices

McCown, Sean:  Beginning Powershell for DBAs 1.0

McCown, Sean:  Understanding Backups

Murphy, Jim:  AlwaysOn – Finally, A usable ‘mirror’!

Nevarez, Benjamin:  Top Query Optimizer Topics for Better Performance

Nevarez, Benjamin:  Inside the SQL Server Query Optimizer

Ormrod, Steven:  SQL Server Amnesia

Pastrick, Wendy:  Transactional Replication 101

Radney, Tim:  It is TEMPDB, Why Should You Care?

Radney, Tim:  You Inherited a Database, Now What?

Saleme, Mike:  A consolidation architecture to host 1000s of DBs

Sterrett, John:  Evaluate your daily Checklist with PBM and CMS

Professional Development Sessions

Wilson, Nancy Hidy:  WIT Discussion Panel (Kalen Delaney, Janis Griffin, Jen McCown, Wendy Pastrick, Christina Leo)

Warren, Andy:  Building Your Professional Development Plan

McCown, Sean and Jen:   Mouth Wide Shut: Coherent Interviewing

Van Dyke, Kendal:  Building Your Own SQL Server Virtual Playground

Moreign, Anthony {Tex}:  The {Microsoft} Cloud and I

Wong, Jason:  A Comparison of SQL Server and Oracle


SQLSaturday #107 – Houston – Wanted: Sponsors! Speakers! Attendees!

SQLSaturday #107 is scheduled April 21, 2012, in Houston at YES Prep School – North Central Campus. We (Houston Area SQL Server User Group) are currently still accepting sponsors, speakers, attendees and volunteers for this event! 

Sponsors, click sponsors to find out how you can engage with us. This is a great way for you to interact with SQL Server professionals from the Greater Houston area and surrounding environs. At our previous SQLSaturday in January 2010, we had over 200 attendees from all over Texas, Louisiana and Arkansas, plus a few visitors from other states as well. We are expecting at least 300 attendees this year.

The Call for Speakers is open until March 15. We already have over 90 sessions submitted, so we have a lot of great sessions to choose from already, but we want more! We are looking for sessions in the “track” areas of Database Administration, Database Application Development, Business Intelligence (SSAS, SSIS, SSRS, etc.), and Professional Development. Additional topic areas to consider when submitting sessions are: PowerShell, SQL Azure, and SQL Server 2012.

Attendees, please register as soon as possible – this is a FREE event (although we request $10 to help cover lunch/snack costs), but it really helps us with our planning if you all don’t wait until the very last minute to sign up!  We have a lot of great speaker submissions already, so we expect the content provided to exceed last year’s SQLSaturday #57 event.  Seating will be limited and you don’t want to miss out on this great training event. And, when you are signing up, consider volunteering. We have several committees which will need additional volunteers to perform tasks leading up to and on the day of the event. If you already registered and didn’t check the volunteer box and would now like to volunteer, please email the steering team.

If you are attending from out of town (or across town), the recommended hotel is the Hilton Garden Inn Houston / Bush Intercontinental Airport.  Mention “PASS” when registering for the discount rate.

Finally, don’t forget to register separately for the all-day preconference seminar, SQL Server Query Plans: Tuning & Management with Kalen Delaney, to be held on Friday, April 20, 2012. Details here! Early bird registration is available through February 29, 2012.  

SQLSaturday #107 – Pre-Conference Seminar Announced!

In conjunction with SQLSaturday #107, scheduled April 21, 2012, in Houston, the Houston Area SQL Server User Group will be hosting a full-day pre-conference seminar, SQL Server Query Plans: Tuning & Management by Kalen Delaney, on Friday, April 20, 2012. The seminar is sponsored by Idera, provider of SQL Server management solutions, helping us to provide this seminar at a low cost to you.

Kalen is a long-time Microsoft SQL Server MVP, author, consultant, and trainer. She is generally recognized as the foremost authority on SQL Server internals per her series of “Inside SQL Server” books, articles in SQL Server Magazine, training classes and conference presentations.

This low-cost single-day seminar will be held at the Hilton Garden Inn near Houston’s Bush Intercontinental Airport which is also very convenient to the SQLSaturday #107 event location for those attending both events from out of town.  A continental breakfast and boxed lunch will be provided on Friday to pre-con seminar attendees.

To find out more information about the pre-con seminar agenda, hotel information, and to register, please click: SQLSaturday #107 Pre-Con Registration. There is an early bird registration rate ($88) available through the end of February and seating is limited, so sign up now and save!  The regular rate of $99 will be charged starting March 1.  

If you have already registered for SQLSaturday #107 and want to attend the pre-con, that is great!  If you want to attend both events, please realize that there are two separate registrations…

SQLSaturday #107 Pre-Con Registration (April 20)

SQLSaturday #107 (April 21)

If you need to take your performance tuning skills to the next level, then Kalen Delaney’s Query Plan seminar will be invaluable in developing your knowledge of how SQL Server works and how you can impact (for better or worse) its performance. This is your opportunity to get that knowledge from one of the gurus in our industry!


Have Your Say – SQLRally Pre-con Sessions up for Vote!

SQLRally is a PASS-sponsored 2-day training event scheduled May 10-11, 2012, in Dallas, Texas, and hosted by the North Texas SQL Server User Group with support from all the South Central Region PASS Chapters.  For additional in-depth learning opportunities, there will be 2 days of pre-conference sessions (aka pre-cons) offered on May 8 and May 9.  

6 pre-conference sessions have already been selected and the remaining 2 sessions will be decided by community vote. Voting will close on February 9, 2012, at 5pm Central time – so what are you waiting for – go vote now and plan to attend SQLRally Dallas 2012!

Register for the main 2-day event before March 15, 2012, for only $349!

TSQL2sday #026 – Second Chances

What is TSQL2sday? Back in late 2009, Adam Machanic (blog | twitter) had this brilliant idea for a monthly SQL Server blogger event (the origin of TSQL2sday).  This month’s event is hosted by David Howard (blog | twitter) and this month Dave is letting us chose our topic from any of the prior 25 topics! As my first foray into this event wasn’t until the 14th occurrence, I really like this idea and selected “TSQL2sday #007 Summertime in the SQL” as my second chance topic. Okay, so it is January, but it was 70+ degrees in Houston today, so quite balmy. However, that wasn’t why I chose this topic; I really chose it because this topic was about what is your favorite “hot” feature in SQL Server 2008 or R2. I thought about “updating” the topic to SQL Server 2012, but I’m really not sure yet which new “hot” feature of SQL Server 2012 will turn out to be my favorite – and after 3 years, I definitely know which SQL Server 2008 set of features is my personal favorite – CMS and PBM.

The Central Management Server (CMS) and Policy-Based Management (PBM) features have made the overall management of large numbers of SQL Server instances, well, manageable.

The CMS enables us to organize instances into multiple different classifications based on version, location, etc. We rebuild the CMS on a regular schedule based on the data in our asset management system. This ensures that all DBAs have access to a CMS with all known instances. If you are not familiar with the CMS – it does not grant any access to the instances themselves and connectivity using it only works with Windows Authentication, so there are no security loopholes here.

We then use these CMS groups as input into our various meta-data and compliance collection processes. Approximately 90% of our technical baseline compliance evaluation is accomplished via policies in PBM. We’ve incorporated all of this using the EPM (Enterprise Policy Management) Framework available on Codeplex with a few tweaks of our own to work better in our environment.

If you haven’t yet checked out the CMS and PBM features, I encourage you to do so today. I have two previous blog entries relating to this topic – “Managing the Enterprise with CMS” and “Taking Advantage of the CMS to Collect Configuration Data”.  I’d also highly recommend that you watch the SQL Server MCM Readiness Videos on the Multi-Server Management and PBM topics.

And, it is good to know that by the time this entry is posted – we should be back to our normal 50 degree January weather in Houston!  

Deprecated Features in SQL Server 2012

Earlier in the year, I wrote a series of posts on Deprecated Features with a promise to revisit as the final version of SQL Server “Denali”, now known as SQL Server 2012, neared. With RC0 recently released, now is the time for that review!

To recap, deprecated features are still available in a version, but are targeted for removal in a future version. Thus, you should avoid using these features in any new development work and consider replacing them where currently used as soon as convenient. So without further delay, the features which are still available in SQL Server 2012 Database Engine, but now deprecated include:

Data programmability features which should be replaced with Windows Communications Foundation (WCF) or ASP.NET:

Slipstream functionality will go away – sort of. Starting with SQL Server 2012, the command line parameters /PCUSource and /CUSource should be replaced with the Product Update feature (/UpdateSource) which I have previously discussed. Although the old parameters are still valid, I’d switch to the new parameters ASAP as they are much easier to use and maintaining your source is also simplified.  

There are also Database Engine Backup and Restore T-SQL command changes: RESTORE { DATABASE | LOG } WITH [MEDIA]PASSWORD is deprecated. While BACKUP { DATABASE | LOG } WITH PASSWORD and BACKUP { DATABASE | LOG } WITH MEDIAPASSWORD are actually discontinued in SQL Server 2012. These commands have no replacement.

Database Compatibility level 90 and upgrades from SQL Server 2005 will not be supported in the version after SQL Server 2012.  This is in keeping with Microsoft’s stated support model of only supporting 2 down-level versions for upgrades. You should be well aware of this by now, but I’m including it as a reminder for those new to SQL Server version management. For example, SQL Server 2000 (or database compatibility level 80) is not a supported version to upgrade to SQL Server 2012.   

The ability to return results sets from triggers will not be supported post-SQL Server 2012.

As stronger encryption algorithms are now available, encryption using RC4 and RC4_128 has been deprecated. However, decrypting RC4 and RC4_128 has not been deprecated.

Several stored procedures, a global variable, and SET command related to remote servers have been deprecated. It has been recommended for several versions now to replace remote servers with linked servers.  Hopefully you have already done this task.

SET ROWCOUNT for INSERT, UPDATE, and DELETE has been replaced by the TOP keyword, so if you have been using SET ROWCOUNT to limit the number of rows affected by your INSERT, UPDATE, or DELETE statements, you’ll need to switch these to use the TOP keyword instead.

If you’ve been using the table hint HOLDLOCK without parenthesis, then you will need to add the parenthesis in the next release of SQL Server.

On the tools side of things, the SQLMAINT utility has been deprecated with the recommendation to use SQL Server maintenance plans as the replacement.

And if you want to really get a jump on things, along with the list of deprecated features just described for the “next” version of SQL Server (after 2012), you can start eliminating the usage of features listed in “Features Not Supported in a Future Version of SQL Server” – both can be found here:  http://msdn.microsoft.com/en-us/library/ms143729(v=SQL.110).aspx

If you are using the Full-Text feature of SQL Server then you’ll want to check-out the list of deprecated items specific to that feature here: http://msdn.microsoft.com/en-us/library/cc646010(v=SQL.110).aspx. These include many stored procedures (e.g. sp_fulltext_catalog) which have been replaced with new T-SQL syntax (e.g. CREATE\ALTER).

And, finally, if you are using components other than the Database Engine (i.e. SSAS, SSIS, SSRS), then you’ll need to review the deprecated/discontinued lists for them in their respective Backward Compatibility sections of Books Online.

As I pointed out in Part 3 of my earlier blog series, you can detect most deprecated features by using the SQL Server: Deprecated Features object performance counter and trace events.   A future blog will discuss which previously deprecated features have actually been discontinued in SQL Server 2012 – meaning you will have to address them before upgrading.

SQL Server 2012 RC0 – SSMS Review

Microsoft released SQL Server 2012 RC0 (Release Candidate) this week – just in time for those of us traveling over the holidays to have something to do as we head to Grand-Ma’s (assuming we aren’t the one driving).  With any new release, I suspect most of us do the same thing – run as fast as we can through the install process and open SSMS and start playing!  If you haven’t been previously playing with “Denali” CTP3, then I encourage you to get the RC0 install (on a test server, of course) – if only for the new SSMS.

The first time you connect to an instance in Object Explorer, you’ll immediately notice differences from SSMS 2008 R2.

Oh look! There’s a folder called “AlwaysOn High Availability” – let’s go click it!

Yikes! That wasn’t nice. It seems like SSMS should already know this and have a “not available” icon showing like it does for my SQL Server Agent which is currently disabled.

OK – let’s try the other new folder “Integration Services Catalog”.  That’s interesting because I do have SSIS 2008 installed, but haven’t upgraded it. The only option here when I right-click is to “Create Catalog”. However, then I see that I need CLR enabled and once I do that then I need to make some other decisions.

Ok – I guess this will be something I need to do some more research on to know when we’ll want to use this and exactly how to use it. But, that is the whole purpose of this exercise, seeing what is new and where to focus future in-depth investigation.

So, back to drilling down through Object Explorer to see what else looks new…

Looks like I’m going to have to study up on Extended Events now that they’ve made it into SSMS; and Microsoft appears to be setting up some default entries here.

Now, I’ll start drilling down to the next layer of some of these objects. We use PBM (Policy-Based Management), so let’s see if anything is new there. Yep! Looks like Microsoft is installing policies by default in preparation for AlwaysOn usage.  So, when I get around to playing with AlwaysOn, it will be interesting to revisit these and see if they are automatically enabled when setting up AlwaysOn.

I didn’t show it in the image, but there are new Conditions created upon install for the AlwaysOn policies to use. And, obviously with new features in SQL Server 2012, new facets related to those features have also been added (76 facets in SQL Server 2008 R2 versus 84 in SQL Server 2012). Interestingly, if I connect to a SQL Server 2008 or SQL Server 2008 R2 instance from SSMS 2012, I see all the facets for SQL Server 2012. I think this means I’ll be able to keep using my central PBM repository from SQL Server 2008 R2, but run policies specific for SQL Server 2012 (using our version of EPM). More to investigate!

Lastly for this initial drive-by look, I checked out the Management\Legacy folder. Here’s the comparison of SQL Server 2008 R2 SSMS (first) and SQL Server 2012 (second):

SSMS 2008 R2 Legacy Objects

SSMS 2012 Legacy Objects

Looks like DTS and SQL Mail are finally gone as promised! 

Until next time, Happy Exploring!

PowerShell – Using Common Parameters

One of the most useful features provided in PowerShell are “Common Parameters”. These are parameters which you can utilize in your own scripts and functions to provide a similar look and feel to the standard PowerShell cmdlets which use them.

What are these Common Parameters? 

  • -Debug
  • -ErrorAction
  • -ErrorVariable
  • -OutBuffer
  • -OutVariable
  • -Verbose
  • -WarningAction
  • -WarningVariable

You can find out the basic functionality of each of these by running:

PowerShell Get-Help about_CommonParameters

But, how do you add them into your own script? It is actually pretty easy. Just include the following line in your script or function as the first executable line (you can only have comments preceding this):


You can now use these Common Parameters with your script.

To demonstrate the regular Common Parameters, I’ll show examples with the two you are most likely to start with: the -Debug and -Verbose parameters. If you check the help, you see that both are basically a switch (true/false), but you can explicitly specify $true or $false as the value (e.g. -Verbose:$true). If you want to use Write-Verbose commands in your script, but only when needed, then by using the CmdletBinding attribute, you can pass the -Verbose parameter when calling your script and the Write-Verbose statements will display.  Consider the following test script (saved as Test-Parm.ps1):

Param ()
Write-Host "Begin Testing Common Parameters" -ForeGround Green
Write-Verbose "Verbose Parm Used"
Write-Host "End Testing Common Parameters" -ForeGround Green

Execute the script as follows without any parameters:

Powershell .\Test-Parm.ps1 

You’ll just see the two output lines from the Write-Host statements in green.

Now execute the script with the -Verbose parameter:

You might think that you can just reference this parameter now in your script like $Verbose…. Nope! However, when you use the CmdletBinding attribute, the PowerShell automatic variable $PSCmdlet is set and these common parameters are considered bound parameters.

This is helpful if you want to perform specific settings or actions within your script when one of these common parameters is used as shown. For example, the default behavior for Write-Debug is to prompt the user for the action to take as shown here:

You can use the following syntax, however, to test if the -Debug parameter was passed and change the $DebugPreference variable to “Continue” in order for your script to automatically continue processing whenever a Write-Debug statement is encountered.

If ($PSCmdlet.MyInvocation.BoundParameters["Debug"].IsPresent) 
     Write-Debug "Debug Output activated"

The IsPresent property actually evaluates to true if either syntax is used: -Debug or -Debug:$true.

If -Debug:$false is the parameter or the -Debug parameter is not supplied, then IsPresent will evaluate as false.

Consider the following script (saved as Test-Parm.ps1) and note the execution examples shown following it.

Param ()
Write-Host "Begin Testing Common Parameters" -ForeGroundColor Green
Write-Verbose "The -Verbose parameter was provided" 
If ($PSCmdlet.MyInvocation.BoundParameters["Debug"].IsPresent) 
     Write-Debug "Debug Output activated"
Write-Debug "This script was called with the -Debug parameter." 
Write-Host "End Testing Common Parameters" -ForeGroundColor Green

Hopefully this tip will help you to incorporate these features into your next PowerShell script.

TSQL2sday #024 – Prox ‘n’ Funx

What is TSQL2sday? Two years ago, Adam Machanic (blog | twitter) had this brilliant idea for a monthly SQL Server blogger event (the origin of TSQL2sday).  This month’s event is hosted by Brad Schulz (blog) and the selected topic is “Prox ‘n’ Funx” (aka Procedures and Functions).

Today, I’m sharing my favorite SQL Server metadata function – SERVERPROPERTY(‘propertyname’).  This handy function has been around since at least SQL Server 2000. If you need to get a quick “report” of the high level configuration information about your SQL Server instance – this is the function to use. Of course, with each new version of SQL Server it is subject to change, so always check usage in BOL (this link is to SQL 2008 R2 Books Online, but you can get to other versions from there).

If a property isn’t valid for a particular version of SQL Server (or if you just flat out typo the property name!), then NULL will be returned. Here’s a sample query for you to try out.  

And if you need database property info – guess what?  There are DATABASEPROPERTY and DATABASEPROPERTYEX functions you should check out! Until next time – happy TSQL2sday!



Get every new post delivered to your Inbox.

Join 271 other followers