Letting Go—SQL Server 2005 Deprecation

Published On: 2016-04-11By:

SQL Server 2005 is deprecated this week, specifically tomorrow. That means if you have a problem, or a new bug is discovered, Microsoft support will require you to upgrade in order for support to help you with your problem. This is particularly a big deal when a new security bug is found, and it is not patched for the release of SQL Server that your organization is running

But I Work in Healthcare, Banking, Government

If you work in government, I don’t really have any advice for you—I”m not familiar with how to get through the government machinations to get off of really old software. However, if you work in health care, or banking you want to make sure your boss, and your quality assurance people know that you are now running an unsupported version of the software. When I worked in pharmaceuticals and device this worked pretty well—what really helped was getting our RBDMS classified as part of infrastructure so it didn’t have to be qualified like application software. If you work in banking or finance, the better option to follow up is the security angle—if there is a new zero day attack, your servers won’t be patched until your upgrade.

SQL Server 2005—A Pretty Awesome Release

As much as I’ve been working on SQL Server 2016 lately (and it really is awesome), but SQL Server 2005 was a groundbreaking release that really gave Microsoft market share in the big enterprise database space. A few of the features that were introduced in SQL 2005 include:

  • Database Mirroring
  • Dynamic Management Views (DMVs)
  • Service Broker
  • SSIS
  • Modern Consistency Checks
  • SQL Server Management Studio

SQL Server 2005 was an excellent release, however if you are using it, you are running effectively 11 year old software to host your applications. That’s a bad idea—remember what cell phones were like in 2005?

image

That’s what your database looks like now. Just remember—if you are doing an upgrade skip 2008, 2008 R2 and 2012, and just go straight to SQL Server 2014 (or 2016 if you are reading this after RTM), there’s no reason to upgrade to software that’s going to be desupported in two years. Also, install it on the latest version of Windows, so you don’t have to do this again when Windows 2008R2 is deprecated.


Contact the Author | Contact DCAC

Challenges of Speaking—Being Ready for Anything..

Published On: 2016-02-21By:

I have the good fortune to have a job that allows me to travel around the world speaking about technology. I love this—it gives me a way to see new places, experience other cultures, and meet a lot of wonderful people how have similar interests to me. I always say about half of my job is working on computers and databases, and the other half is talking and writing about them. I’ve been speaking professionally for almost 10 years now, and have had a lot of unique situations.

One of the biggest challenges is that I speak a lot about cloud computing—generally speaking this involves connecting over a myriad of ports to internet connections that may or may not be blocked. Here are some tips that I’ve used to deal with getting to the cloud in various bad network configurations.

  • Use a VPN to remote desktop to another machine that can get out
  • When you realize the network is blocking RDP packets use your phone to tether
  • Set up a VM in Azure with RDP running on Port 443 (https port) and RDP to that
  • Finally, use screenshots, if all else fails
    All of these are complicated by foreign countries, different networks, different network policies, etc.
    The one that takes the cake for challenges though is losing the projector. I had this happen for the first time at a SQL Saturday in Houston—I was doing an session on SQL Server High Availablity and Disaster Recovery and projector locked up. It was a session I easily could have done as a white board. However, the staff was able to get me up and running again.
    Recently, I had my machine lock up, during a session at SQL Saturday Cleveland, so while I was trying to get it to work, I calmly pulled out my second machine, fired it up while I got my other machine working, and kept going. I had another failure, so I restarted on my DR machine. I’m not saying you need to have two laptops in your bag, but it is a nice to have.
    Finally, this week while I was teaching in India, I lost video again—this time there weren’t good white boards. My topic was networking in Azure—so I worked with another presnter to do a live session with each of use representing parts of the network. The session went over quite well.

So what are the tips you need to know:

  • Always have a DR plan (this goes for travel, presenting, IT, and life in general)
  • Stay calm, getting overly nervous does you or the audience no good
  • If things go really sideways, don’t be scared to co-present with someone. It’s a good rescue mechanism
  • Your audience wants you to succeed, they aren’t against you. So relax.
    When all else fails, scotch.

Contact the Author | Contact DCAC

Getting Started with Polybase—Incorrect Syntax near ‘EXTERNAL’ Error

Published On: 2016-02-12By:

I was playing around with configuring Polybase this morning. Nothing to exotic, just installing the feature getting started. I got everything installed and was about ready to created my data source. The command to that is as follows:

CREATE EXTERNAL DATA SOURCE AzureStorage
WITH (           
TYPE = Hadoop,        
LOCATION = ‘wasb://polybase@mystorageaccountnamehere.blob.core.windows.net’,
CREDENTIAL = AzureStorageCredential);

Unfortunately, it returned the following error:

Msg 102, Level 15, State 1, Line 1

Incorrect syntax near ‘EXTERNAL’.

So what was the fix? A reboot after installing Polybase. The official documentation refers to restarting SQL Server, which I did. Nothing warned me to reboot, but rebooting fixed the error.


Contact the Author | Contact DCAC

Using SQL Sentry Plan Explorer to Shed Light on Estimated Execution Plans

Published On: 2016-02-03By:

Note: While I do have business relationships with many vendors, I do not have one with SQL Sentry aside from attending their great annual party at the PASS Summit.

SQL Server allows us to have access to two types of execution plans, estimated and actual. Actual execution plans give us the real row counts and statistics the optimizer gets when executing the query, while the estimated plan displays the estimates the SQL Server optimizer used based on statistics when it generated the plan. The ideal scenario for performance tuning is to have the actual plan, but when you are dealing with a long running query that runs hours, and a busy procedure cache, that can be a real challenge to get.

So yesterday, I was helping a friend tune a couple of queries, one of which was running hours, and one of them was running in minutes. So I got my hands on the estimated execution plans, and I loaded them quickly in SQL Sentry Plan Explorer (which has a free version). I can’t recommend this tool enough—for simple query plans Management Studio is ok, but the second you have any level of complexity, Plan Explorer allows you to quickly break it down and identify where the problems are. As in this case:

fig1

Figure 1 The Good Query

fig2

Figure 2 The Bad Query

I quickly honed into the fact that the bad query was doing a lazy spool with 6 BILLION rows versus the 229 million in the good query. However, my friend who was looking at Management Studio was asking where I was getting those numbers. This is what the XML from the original plan showed:

<RelOp AvgRowSize=”51″ EstimateCPU=”0.00889634″ EstimateIO=”0.01″ EstimateRebinds=”0″ EstimateRewinds=”139581″ EstimatedExecutionMode=”Row” EstimateRows=”48868″
LogicalOp=”Lazy Spool” NodeId=”55″ Parallel=”true” PhysicalOp=”Table Spool” EstimatedTotalSubtreeCost=”1242.86″>

I made the assumption that Plan Explorer was doing some math on the execution plan, so I contacted my good friend Aaron Betrand (b|t) who works at SQL Sentry and asked him about it. His response was that “Plan Explorer will sometimes adjust the displayed row counts for certain operators where we know execution count is greater than one”. This is a really great use case for getting better data out of estimated execution plans when that is all you have to work with.


Contact the Author | Contact DCAC
1 28 29 30 31 32 35

Video

Globally Recognized Expertise

As Microsoft MVP’s and Partners as well as VMware experts, we are summoned by companies all over the world to fine-tune and problem-solve the most difficult architecture, infrastructure and network challenges.

And sometimes we’re asked to share what we did, at events like Microsoft’s PASS Summit 2015.

Awards & Certifications

Microsoft Partner   Denny Cherry & Associates Consulting LLC BBB Business Review    Microsoft MVP    Microsoft Certified Master VMWare vExpert
INC 5000 Award for 2020    American Business Awards People's Choice    American Business Awards Gold Award    American Business Awards Silver Award    FT Americas’ Fastest Growing Companies 2020   
Best Full-Service Cloud Technology Consulting Company       Insights Sccess Award    Technology Headlines Award    Golden Bridge Gold Award    CIO Review Top 20 Azure Solutions Providers