Best & Worst Practices

The Winds of Change are a Blowin’

For six years I have been an avid and outspoken fan and paying customer of SourceGear products…from Vault to Dragnet to Fortress and on to Vault Professional, but that is all changing now.  Not the fan part, but the paying customer part.  I’m still a huge fan.  I think that SourceGear does a great job with their product and support has been fantastic when needed (which is not very often).  I think that Eric Sink has done a fine job building a quality company and products, and I appreciate his contributions to the tech community through this blogging and books. ...

Broken Views

“SELECT *” isn’t just hazardous to performance, it can actually return blatantly wrong information. There are a number of blog posts and articles out there that actively discourage the use of the SELECT * FROM …syntax.  The two most common explanations that I have seen are: Performance:  The SELECT * syntax will return every column in the table, but frequently you really only need a few of the columns, and so by using SELECT * your are retrieving large volumes of data that you don’t need, but the system has to process, marshal across tiers,...

SQL Server Developer Tools – Codename Juneau vs. Red-Gate SQL Source Control

So how do the new SQL Server Developer Tools (previously code-named Juneau) stack up against SQL Source Control?  Read on to find out. At the PASS Community Summit a couple of weeks ago, it was announced that the previously code-named Juneau software would be released under the name of SQL Server Developer Tools with the release of SQL Server 2012.  This replacement for Database Projects in Visual Studio (also known in a former life as Data Dude) has some great new features.  I won’t attempt to describe them all here, but I will applaud Microsoft for making major improvements. ...

SQL Source Control and Custom Change Scripts

In part 2 of my previous series regarding Red-Gate’s tool, SQL Source Control, I warned about an aspect of the tool that could cause you to lose data if you were not careful.  A few days ago I was allowed a sneak peak* at the upcoming fix which Red-Gate has termed SQL Migrations. To recap, because SQL Source Control does a point-in-time comparison of your database with the current version in your source control system, there are some types of changes that you can perform which SQL Source Control will interpret differently and which could result in loss of...

The Tipping Point - DISTINCT Causes Timeouts

Everything worked fine for the last six years…and then today we hit the tipping point.  I lost many hours today due to the misuse of DISTINCT which was the root cause of timeout errors in our web application. In one of my most frequently read posts, Why I Hate Distinct, I talk about how this SQL keyword is often abused by SQL developers who are just trying to get their job done, but don’t know enough about either the SQL language or the data architecture to really do it right.  Now, I realize that in the development world (of...

Regaining SysAdmin Access after a Lockout

Today I had to find a way to regain SysAdmin access to an instance of SQL Server when I technically had no permissions.  Here is how I did that. Every developer on my team is setup to be able to work 100% stand-alone.  That is, they have everything on their local machine, including a recent copy of the database, to do all their work even if the network goes down, which it used to do on occasion.  For SQL Server development, each has a copy of SQL Server 2008 R2 Developer Edition installed, and it was one of these...

SQL Source Control with Vault Support Officially Released

HOORAY!  It is officially here!  Today, Red-Gate officially released SQL Source Control version 2.1 with support for Vault. While we have been happily and successfully running the beta version (a.k.a. the Early Access release) of Red-Gate SQL Source Control with support for Vault for quite a while, it is good to have the official RTM (or GOLD, or PROD, or whatever you call your “no-longer-in-beta”) release of the product. As a courtesy to those who have not already read the series, allow me to provide you with these links to my previous posts about this fantastic...

Using SQL Source Control and Vault Professional Part 4

Two weeks ago I upgraded our installation of Fortress to the latest version, which is now named Vault Professional.  This is the version of Vault (i.e. Vault Standard 5.1 / Vault Professional 5.1) that will be officially supported with Red-Gate SQL Source Control 2.1.  While the folks at Red-Gate did a fantastic job of working with me to get SQL Source Control to work with the older Fortress version, we weren’t going to just sit on that.  There are a couple of things that Vault Professional cleaned up for us, such as improved integration with Visual Studio 2010, so it...

Fix for EF4 Profiler Issue Coming in next Cumulative Update

Hey!  What do you know?  Microsoft Connect really works! I was very happy this morning to open my email and find a notice from Umachandar on the SQL Programmability Team that they have created a fix for the Odd Profiler Results with EF4 issue that I wrote about last June.  Not only did I blog about it, but I logged an item to Connect with repro steps and sample code.  And now, they have announced that they have a fix for this problem and that it will be included in the next Cumulative Update for SQL Server 2008 R2....

Source Control and SQL Development – Part 3

In parts one and two of this series, I have been specifically focusing on the latest version of SQL Source Control by Red Gate Software.  But I have been doing source-controlled SQL development for years, long before this product was available, and well before Microsoft came out with Database Projects for Visual Studio.  “So, how does that work?” you may wonder.  Well, let me share some of the details of how we do it where I work… The key to this approach is that everything is done via Transact-SQL script files; either natively written T-SQL, or generated.  My preference...

Using SQL Source Control with Fortress or Vault – Part 2

In Part 1, I started talking about using Red-Gate’s newest version of SQL Source Control and how I really like it as a viable method to source control your database development.  It looks like this is going to turn into a little series where I will explain how we have done things in the past, and how life is different with SQL Source Control.  I will also explain some of my philosophy and methodology around deployment with these tools.  But for now, let’s talk about some of the good and the bad of the tool itself. More Kudos and...

Using SQL Source Control with Fortress or Vault – Part 1

I am fanatical when it comes to managing the source code for my company.  Everything that we build (in source form) gets put into our source control management system.  And I’m not just talking about the UI and middle-tier code written in C# and ASP.NET, but also the back-end database stuff, which at times has been a pain.  We even script out our Scheduled Jobs and keep a copy of those under source control. The UI and middle-tier stuff has long been easy to manage as we mostly use Visual Studio which has integration with source control systems built...

SQL Sentry Truth-Telling and Disk Configuration

Recently, SQL Sentry told me something about my SQL Server disk configurations that I just didn’t want to believe, but alas, it was true. Several days ago I posted my First Impressions of the SQL Sentry Power Suite.  Today’s post could fall into the category of, “Hey, as long as you have that fancy tool…”  Unfortunately, it also falls into the category of an overloaded worker taking someone else’s word for the truth, not verifying it with independent fact-checking, and then making decisions based on that.  Here’s my story… I’m not exactly an Accidental DBA (or Involuntary DBA...

More Maintenance Plan Weirdness

I’m not a big fan of the built-in Maintenance Plan functionality in SQL Server.  I like the interface in SQL 2005 better than 2000 (it looks more like building an SSIS package) but it’s still a bit of a black box.  You don’t really know what commands are being run based on the selections you have made, and you can easily make some unwise choices without realizing it, such as shrinking your database on a regular basis.  I really prefer to know exactly what commands and with which options are being run on my servers. Recently I had another...

SQL Sentry First Impressions

After struggling to defend my SQL Servers from a political attack recently, I realized that I needed better tools to back me up, and SQL Sentry is the leading candidate. A couple of weeks ago, seemingly from out of nowhere, complaints from the business users started coming in that one of the core internal applications was running dramatically slower than normal, and fingers were being pointed at the SQL Server.  Unfortunately, we don’t have a production DBA whose entire job is to monitor and maintain our SQL Servers.  The responsibility falls to me to do the best I can, investing only...

Keep it Professional – Multiple Environments

I have certainly been reading blogs a whole lot more than writing them the last several weeks, and it’s about time I got back to writing.  I have been collecting several topics and references for blog posts…some of which will probably just never get written as the timeliness of the topics fade over time.  Nonetheless, I’m back, and I think it is time to revive my Doing Business Right series, this time coming from the slant of managing a development team rather than the previous angle of being self-employed.  First up: separating Dev, Test, and Prod. A few months...

Twitter Revisited

Last night at the Exhibitor Reception at the Summit, Lance Harra said to me, “I thought you gave up on Twitter.”  That was in response to seeing my Twitter ID on my name badge.  Some of you may recall that last year, just before the PASS conference, I jumped on the Twitter bandwagon to play with it during the event.  Shortly thereafter, I jumped back off.  Well, this year, again shortly before the Summit, I started getting interested again.  So, here are a few things I thought I’d share to clear the air… I learned a few tricks to...

Clean Up After Yourself

I have been editing some ASP.NET pages lately and finding a LOT of code that is either all commented out, or worse, someone created a way to permanently hide it from the user (such as ASP Panel that is never made visible) yet left all the code active.  Another trick: commenting out all the lines inside of a routine, but leaving the declaration intact to prevent code from crashing.  If you are intending to retire the routine, then kill it completely, don't just cripple it.  (I know that to some of you, the thought of completely killing a routine is...

Why I Hate DISTINCT

You know... that "handy" keyword that eliminates duplicates from your result set.  Yeah, that DISTINCT.  I Hate it!  My team thought I was crazy (maybe I am, but this is not proof of it).  I am frequently railing against the use of DISTINCT by any of my developers.  "It's a sign of weakness!", I would say.  Or, "It just shows you don't know how to write SQL!" Okay, maybe hate is too strong of a word.  But I certainly dislike its use within my applications.  Actually, today I slowed down long enough to put into coherent terms what my...

How to Return Record #n

I got this question in email a while ago, and it seems to come up quite a bit, which frankly surprises me.  I guess it's because I almost never* write an application where I need to retrieve the 10th (or whatever number) record.  I am always trying to retrieve the record for Joe Smith, or Order Number 1256487, or the last record entered on 12/31/2007, or... well, you get the point.  I am frequently searching for records based on a data value.  But I rarely, if ever, have had to go looking for the nth record. I see this...

Even More SQLPrompt Goodness

If you want to read more...

SQLPrompt Revisited

I have now had the chance to communicate via email with a couple of representatives from Red-Gate and can tell you a little more about the upcoming release.

SQLPrompt - Intellisense in Query Analyzer - Initial Review

I just downloaded and installed the free tool SQLPrompt, now from Red-Gate Software, and I'm really liking it.  It provides Intellisense or statement-completion features in SQL Query Analyzer, SQL Server Management Studio, Visual Studio, and other development tools.  For example, it not only pops up a list of fields after you type a table name followed by period, but it will also lookup relationships and give you options for your JOIN ON statement (and of course a whole lot more).  So, even if you are working in a small database where you already know all the fields and relationships, it...

Top 10 Blog Design Mistakes

Jakob Nielsen, a renowned web design and usability expert, has come up with the Top 10 Design Mistakes for Weblog Usability.

How NOT to Select Records

Apparently in his version of SQL Server, WHERE clauses only work on Temp tables...

Separate Your Singular From Your Plurals

Here's the issue: Singular vs. Plural names of objects and variables...

What Language do you THINK in?

You know you're truly fluent when you can think in a foreign language instead of thinking in your native language and translating.

How Many Test/QA Instances do you Need?

So this got me thinking about just how many different test or QA instances do you need or would you want in an ideal world. Here are some thoughts about different types of testing...

Scripts and Source Control - 102

In the comments section of my SCRIPT IT post, Lisa asked a couple of questions for clarification. These were a good reminder to me that some of the things that I just take for granted these days are actually very new concepts to others who haven't had the same experience. So rather than just post a reply in comments, I thought it'd be worth a post unto its own.

MSDD: Securing a Login Form

At the lunch break, one of the people I was talking to said, “Instead of showing us how to hack an unsecure login page, why don't they just build a secure login page component we can use?”. Well guess what we saw during the closing session...

Source Control and the Build Process

Do the terms Build Master or Development DBA mean anything to you? Learn more about the deployment process...

Test your Backups!

You DO backup your databases, don't you? And you do test your backups occasionally to make sure they're good, don't you?

Separate Development, Testing, and Live Data

“You DO have a separate development environment, don't you?”

To ALTER or to DROP/CREATE... That is the Question

Do you ALTER your sproc/view or do you DROP it and then CREATE it with the new definition?

SCRIPT IT! SCRIPT IT!! SCRIPT IT!!!

If you use Enterprise Manager (EM) to create or edit table structures, data, stored procedures, or much of anything else, you are a rookie. Now, each one of us was a rookie at some point, and you do have to start somewhere, but if you want to be considered a professional, you HAVE TO learn the SQL syntax and get comfortable living in Query Analyzer (QA). Period.

How NOT to Implement Source Control

Damian recently spoke out on versioning code, and it reminded me of a nightmare I'm involved in with versioning documentation.

«November»
SunMonTueWedThuFriSat
2627282930311
2345678
9101112131415
16171819202122
23242526272829
30123456