Thursday, April 28, 2011

3 Days Remaining Before The Next SQLRally Price Increase

If you've been considering registering for the first ever SQLRally May 11-13 in Orlando it's coming down to crunch time. We're less than two weeks away and there are only 3 days left to sign up for the 2 day portion of the conference at $349 - starting May 1 the price goes up to $399. That's still a bargain for what we've got lined up, but why wait? Register now, save $50, and put that towards a precon or some of the after hours events that we're getting lined up.

Speaking of precons, the price is still just $199. You'd pay more than that anywhere else for 8 hours of deep dive training from some of the best in the business. One other thing you may not know about - you don't have to register for the 2 day conference to attend a precon…so if training budgets are tight or you can only get one day off work you've got options.

See you in a few weeks!

SQLRally_Banner_728x90

Wednesday, April 27, 2011

Presenting On DR & HA Twice This Week

Just a quick mention that I'm presenting Paging DR Availability, You're Wanted in the Recovery Room twice this week  - today, April 27, at noon Eastern for the PASS DBA Virtual Chapter and Saturday, April 30, at SQLSaturday #74 in Jacksonville.

Register for the DBA Virtual Chapter presentation here, or just go here at noon to join the presentation.

Seats are still available for SQLSaturday Jacksonville. Visit the event website to see the full schedule and register.

Here's what I'll be talking about:

There are a lot of options when it comes to disaster recovery and high availability with regards to SQL Server. Most business owners (and many DBA’s) lump disaster recovery (DR) & high availability (HA) together and while they do share some pieces, they call for different strategies. Do you pick one or more than one of these options, and based on what? It’s incredibly important that you understand the decision tree that helps you pick the right one(s) for your business, and that you can explain the choices clearly to the stakeholders. It’s not as complicated as it sounds, but it is complicated – and in this hour presentation we’ll give you a high level understanding of the options, the costs, complexities, & reasons for using each of them.

Monday, April 18, 2011

Meet The SQLRally Precon Presenters

SQLRally_BadgeThere's just a few more weeks to go until the inaugural PASS SQLRally (May 11-13) in Orlando and there are still seats available for the Pre-conference seminars. Attendance is capped at 100 people per room and we're just a few seats shy of selling out both the BI and DBA tracks.

All of our precon presenters are well known within the SQL industry, most have written at least one book, and almost all are SQL Server MVPs. $199 for a full-day deep dive session on any of the four tracks we're running is a bargain (and it includes coffee, tea, and a boxed lunch - bonus!). Also, keep in mind that you don't have to register for the entire 3 day conference if all you're interested in is a one day precon.

If you're still on the fence, take a minute to read the bios of each of our speakers below. If you haven't already asked the boss for the money to go, check out our ROI justification page for tips. At worst, the answer is no, at which point I'd still consider taking a day off work and paying out of my own pocket to go given that similar training classes around the country cost a lot more.

Ready to register? Click here!

Business Intelligence Workshop

Patrick LeBlanc, Devin Knight, Mike Davis, & Adam Jorgensen

Patrick LeBlanc
Patrick LeBlanc, SQL Server MVP and author, is currently a Business Intelligence Architect for Pragmatic Works. He has worked as a SQL Server DBA for the past 9 years. His experience includes working in the Educational, Advertising, Mortgage, Medical and Financial Industries. He is also the founder of TSQLScripts.com, SQLLunch.com and the President of the Baton Rouge Area SQL Server User Group. Patrick is a regular speaker at various SQL Server community events, including SQL Saturday’s, User Groups and the SQL Lunch. Patrick is also a regular speaker for at various Microsoft events in the US.

Devin Knight
Devin is a Senior BI consultant at Pragmatic Works Consulting.  Previously, he has tech edited the book Professional Microsoft SQL Server 2008 Integration Services and was an author in the books Knight's 24-Hour Trainer: Microsoft SQL Server 2008 Integration Services and Knight's Microsoft Business Intelligence 24-Hour Trainer.  Devin has spoken at past conferences like PASS and at several SQL Saturday events. He is a contributing member to the Business Intelligence Special Interest Group (SIG) for PASS as a leader in the SSIS Focus Group.  Making his home in Jacksonville, FL, Devin is the Vice President of the local users’ group (JSSUG).

Adam Jorgensen
Adam Jorgensen, MBA, MCITP: BI, MCDBA is the President of Pragmatic Works Consulting, and has been delivering SQL Server and Business Intelligence Solutions for over a decade.  His passion is finding new and innovative avenues for clients and the community to embrace business intelligence and lower barriers to implementation. Adam is a regional mentor and member of the regional mentor committee for the Professional Association of SQL Server (PASS) and a Virtual Technology Specialist for Microsoft. He is also an active member of the South Florida SQL Server User Group (SFSSUG) and is a co-founder of BIDN.com (Business Intelligence Developer Network). Adam co-hosts SQLLunch.com and is also a co-chair of the SQLPASS Virtual Chapter for Business Intelligence and serves on other groups and boards. He regularly speaks in person and virtually at industry group events, major conferences, Code Camps, and SQL Saturday. He has co-authored white papers and books on business intelligence and business topics.

Mike Davis
Mike Davis is a Senior BI consultant and Trainer at Pragmatic Works. He was an author on the book Knight's 24-Hour Trainer: Microsoft SQL Server 2008 Integration Services. Mike is an experienced speaker and has presented at many events such as several SQL Server User Groups across the US, Code Camps, SQL Server Launches, and SQL Saturday events. Mike is an active member at his local user group (JSSUG) in Jacksonville, FL.

 

Query Performance Tuning, Start to Finish

Grant Fritchey

Grant Fritchey is a SQL Server MVP with over 20 years experience in IT including time spent in support and development.

He has worked with SQL Server since 6.0 back in 1995. He has developed in VB, VB.Net, C# and Java. Grant volunteers at PASS and is president of the Southern New England SQL Server Users Group (SNESSUG). He has authored books for Apress and Simple-Talk and joined Red Gate as a Product Evangelist in January 2011.

 

Maximize Your SQL Server 2008 Coding Skills

Plamen Ratchev

Plamen is a SQL Server MVP and founder of Tangra, specializing in relational database applications analysis, implementation and tuning. His particular interest is in design patterns, performance and SQL code optimization. He consults and teaches on best practices and writing SQL code with style.

 

Leadership and Team Management Skills for the Database Professional

Kevin Kline

Kevin Kline is Technical Strategy Manager for SQL Server Solutions at Quest Software. A Microsoft SQL Server MVP since 2004, Kevin is a founding board member and past president of the international Professional Association for SQL Server (PASS). He has written or co-written several books including SQL in a Nutshell (O'Reilly & Associates), SQL Server MVP Deep Dives (Manning), Pro SQL Server 2008 Database Design and Optimization (APress) and Database Benchmarking: Practical Methods for Oracle & SQL Server (Rampant). Kevin is a columnist for SQL Server Magazine and Database Trends & Applications Magazine. Kevin is a popular blogger and top-rated speaker at conferences worldwide such as Microsoft TechEd, the PASS Community Summit, Microsoft IT Forum, DevTeach, and SQL Connections, and has been active in IT for over 20 years.

SQLRally_Banner_728x90

Datetimeoffset And Time Zone Awareness

I've been working a lot with the datetimeoffset data type recently so today's SQLServerCentral.com article The SQL Server 2008 Datetimeoffset Data Type caught my attention. It's a good read if you're unfamiliar with datetimeoffset, as is The Death of DateTime? from Bart Duncan's blog.

Both articles fall short of mentioning one downside of datetimeoffset: while it's easy to switch offsets, it's not easy to switch time zones without understanding the nuances of Daylight Saving Time (though to be fair it's not easy with any other datetime format in SQL Server either).

For example, if I want to convert 2011-03-12 07:30:00.0000000 +00:00 and 2011-03-14 07:30:00.0000000 +00:00 to Eastern time I need to understand that the offset for the 12th is 5 hours but the offset for the 14th is 4 hours. And what about geographic areas that don't observe DST? Does a -7 hour offset represent Phoenix at any time in the year or Seattle during DST?

System functions for time zone awareness were not included in SQL 2008 along with datetimeoffset. It's an unfortunate exclusion considering that SQL Server has spatial data support and this is something that's built into .NET, Java, and most other platforms\languages. It would be incredibly useful to supply a datetime value, a spatial value (or a time zone name), and get a datetimeoffset with the correct offset in return.

I can write my own functions in TSQL to convert between time zones but I shouldn't have to. Here's why:

  1. As previously mentioned, this is something already available in other languages\platforms
  2. DST start\end dates are subject to change - and recently have in the United States. I'd rather get those updates via OS\product patches than have to update custom code.
  3. Time zone awareness is part of the SQL-92 standard.

Erland Sommarskog (Blog) submitted a Connect feature request for this (http://connect.microsoft.com/SQLServer/feedback/details/293933/add-a-set-timezone-command). Please consider voting for it to be included in a future version of SQL Server.

Wednesday, April 6, 2011

Posting Code? Trust, But Verify

Here's a tip for bloggers (or anyone else) who use third party tools to convert T-SQL code into a publishing  friendly format…

Yesterday I posted scripts to identify overlapping statistics and I used a tool to convert the T-SQL into something that retained the formatting you'd see in Management Studio. This morning I received an email from a keen-eyed reader who brought a misspelling to my attention. Of course I had checked my code before I used the tool but not after, and sure enough it had added an extra letter into one table alias in the script. Here's what it looked like before and after formatting was applied:

Trust But Verify

So today's lesson: Trust, but verify. Even if you check that your code works before you apply formatting make sure to copy it back to Management Studio afterwards and see that it still runs like you think it's supposed to.

(And for what it's worth I've fixed the error in the original blog post. If you copied the scripts yesterday you may want to revisit the post and copy them again)

Tuesday, April 5, 2011

Updated Overlapping Statistics Scripts

Last year I published a script for identifying automatically generated column statistics that overlapped index statistics. I've updated the script to take filtered indexes into consideration in SQL 2008 and am sharing both the 2005 and 2008 versions. Also, a friendly reminder that the scripts run within the scope of the currently selected database. If you run into any problems with them let me know by leaving a comment below.


/* For SQL 2008 and up - takes filtered indexes into consideration */
WITH    cteAutostats ( object_id, stats_id, name, has_filter, filter_definition, column_id )
         
AS ( SELECT   ColumnStats.object_id ,
                       
ColumnStats.stats_id ,
                       
ColumnStats.name ,
                       
ColumnStats.has_filter ,
                       
ColumnStats.filter_definition ,
                       
StatsColumns.column_id
              
FROM     sys.stats AS ColumnStats
                       
INNER JOIN sys.stats_columns AS StatsColumns ON ColumnStats.object_id = StatsColumns.object_id
                                                             
AND ColumnStats.stats_id = StatsColumns.stats_id
              
WHERE    ColumnStats.auto_created = 1
                       
AND StatsColumns.stats_column_id = 1
            
)
   
SELECT  OBJECT_SCHEMA_NAME(ColumnStats.object_id) AS SchemaName ,
           
OBJECT_NAME(ColumnStats.object_id) AS TableName ,
           
ObjectColumns.name AS ColumnName ,
           
ColumnStats.name AS Overlapped ,
           
cteAutostats.name AS Overlapping ,
           
'DROP STATISTICS ' + QUOTENAME(OBJECT_SCHEMA_NAME(ColumnStats.object_id)) + '.' + QUOTENAME(OBJECT_NAME(ColumnStats.object_id)) + '.' + QUOTENAME(cteAutostats.name) + ';' AS DropStatement
   
FROM    sys.stats AS ColumnStats
           
INNER JOIN sys.stats_columns AS StatsColumns ON ColumnStats.object_id = StatsColumns.object_id
                                                           
AND ColumnStats.stats_id = StatsColumns.stats_id
           
INNER JOIN cteAutostats ON StatsColumns.object_id = cteAutostats.object_id
                                      
AND StatsColumns.column_id = cteAutostats.column_id
           
INNER JOIN sys.columns AS ObjectColumns ON ColumnStats.object_id = ObjectColumns.object_id
                                                      
AND StatsColumns.column_id = ObjectColumns.column_id
   
WHERE   ColumnStats.auto_created = 0
           
AND StatsColumns.stats_column_id = 1
           
AND StatsColumns.stats_id != cteAutostats.stats_id
           
AND ( ( cteAutostats.has_filter = 1
                   
AND ColumnStats.has_filter = 1
                   
AND cteAutostats.filter_definition = ColumnStats.filter_definition
                 
)
                  OR (
cteAutostats.has_filter = 0
                      
AND ColumnStats.has_filter = 0
                    
)
                )
            AND
OBJECTPROPERTY(ColumnStats.object_id, 'IsMsShipped') = 0
   
ORDER BY OBJECT_SCHEMA_NAME(ColumnStats.object_id) ,
           
OBJECT_NAME(ColumnStats.object_id) ,
           
ObjectColumns.name ;
GO

/* For SQL 2005 only */
WITH    cteAutostats ( object_id, stats_id, name, column_id )
         
AS ( SELECT   ColumnStats.object_id ,
                       
ColumnStats.stats_id ,
                       
ColumnStats.name ,
                       
StatsColumns.column_id
              
FROM     sys.stats AS ColumnStats
                       
INNER JOIN sys.stats_columns AS StatsColumns ON ColumnStats.object_id = StatsColumns.object_id
                                                             
AND ColumnStats.stats_id = StatsColumns.stats_id
              
WHERE    ColumnStats.auto_created = 1
                       
AND StatsColumns.stats_column_id = 1
            
)
   
SELECT  OBJECT_SCHEMA_NAME(ColumnStats.object_id) AS SchemaName ,
           
OBJECT_NAME(ColumnStats.object_id) AS TableName ,
           
ObjectColumns.name AS ColumnName ,
           
ColumnStats.name AS Overlapped ,
           
cteAutostats.name AS Overlapping ,
           
'DROP STATISTICS ' + QUOTENAME(OBJECT_SCHEMA_NAME(ColumnStats.object_id)) + '.' + QUOTENAME(OBJECT_NAME(ColumnStats.object_id)) + '.' + QUOTENAME(cteAutostats.name) + ';' AS DropStatement
   
FROM    sys.stats AS ColumnStats
           
INNER JOIN sys.stats_columns AS StatsColumns ON ColumnStats.object_id = StatsColumns.object_id
                                                           
AND ColumnStats.stats_id = StatsColumns.stats_id
           
INNER JOIN cteAutostats ON StatsColumns.object_id = cteAutostats.object_id
                                      
AND StatsColumns.column_id = cteAutostats.column_id
           
INNER JOIN sys.columns AS ObjectColumns ON ColumnStats.object_id = ObjectColumns.object_id
                                                      
AND StatsColumns.column_id = ObjectColumns.column_id
   
WHERE   ColumnStats.auto_created = 0
           
AND StatsColumns.stats_column_id = 1
           
AND StatsColumns.stats_id != cteAutostats.stats_id
           
AND OBJECTPROPERTY(ColumnStats.object_id, 'IsMsShipped') = 0
   
ORDER BY OBJECT_SCHEMA_NAME(ColumnStats.object_id) ,
           
OBJECT_NAME(ColumnStats.object_id) ,
           
ObjectColumns.name ;
GO

Monday, April 4, 2011

Meme Monday

Replication: It's Not Really As Bad As SQLRockStar Says It Is

(Wondering what the heck this is supposed to be about? Check out Meme Monday, Thomas LaRock's (Blog | Twitter) challenge to the SQL blogosphere to find out!)

Friday, April 1, 2011

Announcing The Association For SQL Server

Recently there have been a number of PASS members who have become unhappy with the direction of the organization and are wishing for something different - something less political, less structured, maybe a bit more people-focused, edgy, and fun. I've thought about how to channel that unhappiness in a way that does good without harming PASS along the way and I've come up with an idea.

Today I'm announcing a new social group for PASS members who want less professional and more fun from it: the Association For SQL Server, or A.S.S.

A.S.S doesn't really have an agenda - after all it's just a social group - except to make the most of when we all get together at SQLSaturday and the Summit. But what we lack in direction we more than make up for in marketing coolness. Here's a couple of ideas I had so far:

  • Get branded apparel. I'm thinking hats for starters, and we wear it to identify that we're part of the cool kids club. Want to know who's in? Just look for the A.S.S hats at any event!
  • If we ever needed to vote on something that required quorum we'll say that we have to do it with at least half A.S.S. effort.
  • We could have our own Women In Technology group - we'll call them Sassy A.S.S., or maybe just sASSy for short.
  • When the board meets to discuss the future of the group we can say they've got their heads in their A.S.S.
  • Members need a sense of humor, and the light hearted ones amongst us will be known to everyone as the A.S.S. clowns.

Sounds great, right? On second thought…

(Happy April Fools' Day!)