Blog

Have We Devalued SQL Server Consulting?

Have we given too much away?

SQL Server has an amazing community. It freely provides information and training of SQL Server technologies via the internet and even in person events like SQL Saturday’s.

Has the value of SQL Server Consulting Diminished?

Has the value of SQL Server Consulting Diminished?

This is fantastic for anyone who works in SQL Server. It has even been noted that having SQL Server skills can increase your base salary rate with a new job by near 10% (http://sqlmag.com/sql-server/look-sql-server-and-data-related-salaries-2015). We have so many resources readily available to give us the information we need to be able to do our jobs to the best extent.

However, when it comes to paying someone from the outside for that knowledge and expertise the increases aren’t there. There is so much high quality information available for free that companies can quickly start to question the need for outside consultants when Google has “all the answers”. Did we, as a community, devalue the role of the SQL Server consultant?

Generally consultants can’t hike their prices to keep up with the pay scale increases. In some cases prices may start to creep down in order to get companies to spend some of their money to solve their SQL Server problems more quickly and efficiently. Even then, a lot of companies would rather hire a full time or contract employee at a perceived lower rate rather than bring in a more expensive consultant. Perhaps the company feels it can’t afford a consultant. Maybe there were issues in the past with consultants that milk the system for every penny possible. It doesn’t matter than the lower rate employee may take 3 to 4 times longer to achieve the same results due to lack of experience. The up-front focus of most projects is on the bottom line. The perceived lower per hour cost outweighs the ultimately cheaper cost of getting things done right the first time at a slightly higher rate.

Companies will continue to squeeze the pennies and consultants will always be out there trying to get as much as they can from the companies. The solution to the dilemma comes when the two forces work together to resolve issues in an expedient and cost effective way for all parties involved.

Are you a SQL Server consultant? Do you work with SQL Server, yet feel no need for a consultant? Do you manage a project that uses SQL Server, but decided to hire rather than use a consultant? Feel differently than the views expressed above? Leave a comment with your thoughts on the value of the SQL Server Consultant.

 

Chris Bell, SQL Server MVP, MCSE & MCITP, is a 20 year veteran of using Microsoft products & SQL Server to create solutions for businesses, organizations and individuals. Chris speaks, blogs, writes articles and makes media of all kinds regarding SQL Server at WaterOxConsulting.com.

Click here to contact Chris or to follow him on twitter.

Chris is also the founder of WaterOx Consulting, Inc. which features SQL Server consulting services along with the destination location week-long training series: SQL Summer Camp. New in 2015 WaterOx Consulting is introducing the SQLPunch series of half day training events designed to help others learn to efficiently and effectively use SQL Server.

He is the founding president of PASSDC and organizes the annual SQLSaturday for the DC area. Chris frequently speaks at and attends SQL Server events, sharing his passion for all things SQL Server.

In 2012 Chris was a finalist in the worldwide Exceptional DBA competition and in 2014 he received the Microsoft MVP award in recognition of his open sharing of his knowledge with the technical community. His blog is currently syndicated to SQLServerCentral.com and ToadWorld.com

12 Things To Check With SQL Server To Prepare For Winter

Winter is coming! Is your SQL Server ready?

Winter is coming! Is your SQL Server ready?

There you sit, watching TV, when you hear it: “Analysts are predicting massive cold fronts this winter! Learn 12 tips to prepare for winter and the storm of the century tonight at 11!”

“Oh my gosh!” you think, “I’d better pay attention and record that for future reference”

Yeah. You are probably like me and just get back to trying to find something not over propagandized to watch. Is there anything like that on TV anymore?

Reality is, winter is coming, at least for those of us in the northern hemisphere. That doesn’t seem to mean much to us when dealing with SQL Server databases, right?

Well, actually, it does.

For a lot of companies, the end of year is an exceptionally heavy workload month. There is a culmination of month end, quarter end and year end analysis and reports to be done. If the company happens to be part of the retail industry, there are sales and promotions that start to push websites, applications, and databases to an extreme they usually don’t experience any other time of the year. If a company’s fiscal year matches is based on the calendar year, there is a lot of other reporting and analysis to be done for that. It all adds up very quickly.

Let’s go back to the initial opening statement I had, about the news coverage of the upcoming winter. There will most likely be some story about how to prepare your car, your home, or even yourself to make it through the rough winter ahead.

My question is what are you doing to make sure your database is ready for the onslaught of winter.

Here are 12 things to check to make sure your system is prepared for this winter. There are lots of other things that can be checked as well. I invite you to share in the comments anything else you do to prepare SQL Server for this winter’s onslaught of transactions and analysis.

Read more →

Versioning In Your SQL Database

What Is The Point of Versioning?

How do you know change occurs in your database?

How do you know change occurs in your database?

Now, I’m not talking about version control, I am talking about stamping your database with a version number so you can keep track of how many modifications may be made in an environment not completely in your control.

I have used this concept in the past in a development environment where I had a full data team working on revisions in our version of the database. We did weekly releases to the development team into their development database.

My team was able to use the version number we put in the database to see if anyone on the developer team was making changes to the database structure without following the required processes and weekly code reviews. The version number also gave us a check before we were to release a new ‘build’ that would change the database’s structure. We could do one simple check and know if the database had been modified since our last ‘release’.

The script below build a DDL trigger that fires when DDL changes are made and increments the version number. The trigger was built this way as I didn’t care who made the change, and I didn’t want to point fingers. I just wanted to know if there was a change made.

Read more →

When Things Align Unexpectedly

Things are happening

Things are happening

or: Learning to Not Look a Gift Horse in the Mouth

The stars seem to have aligned. I am sitting in my hotel room in Seattle waiting for the Microsoft MVP Summit to start as a precursor to the PASS Summit later this week.

Just a few months ago I never thought I’d be in this position.

The back half of this year so far has been a tough one. Clients have been quiet, and funds getting tight after dealing with Digby! and his 2 knee surgeries. I had written off attending the PASS Summit and had no idea that the MVP Summit would even be a remote thought.

This year, after a year spent working to get my name out there more as an independent SQL consultant, I was nominated and received the 2014 MVP award for SQL Server. When I got the news I was floored! I had been nominated before, but never got a hint of getting the recognition. I was not expecting it at all and it was a nice pick-me-up for the back half of the year.

Now normally attending the PASS Summit wouldn’t have been a problem as I’d just have to get the hotel and flight sorted out. (There are perks to running a user group and one of those is PASS covering the registration cost to the PASS Summit to represent your group). Earlier this year I started checking out hotels in Seattle nearby and flights from the D.C. area, everything was just too much and would kill any concept of a budget I had left for the year. Once I heard officially that I received the MVP award this year, I started looking into their Summit as I had heard a lot of rumblings that it was going to be at the same time, and general location of the PASS Summit. Even better, Microsoft would cover some of the hotel during the MVP Summit.

The first star started to line up. At the Pittsburgh SQL Saturday this year I talked with Brian Davis from UpSearch (and who is presenting at SQL Summer Camp as well! Very excited!) and we arranged to split the room cost to allow both of us to attend Summits we would not be able to otherwise. The first star (or whatever) aligned when booking the room through Microsoft MVP Summit and they mentioend to me that that they would be completely comping my room during the MVP Summit. That was 1/2 the trip! This meant that Brian and I would be splitting the last part of the week. Booyah! We were all good!

Then started looking for flights. Earlier in the year, even just a just a couple months ago everything was outrageously priced, or a 13+ hr day to get cross country. A couple of weeks ago when I was looking for flights after that first star aligned, I found Alaskan Airlines was now offering a new direct BWI – SEA flight, and had just started it in Sept this year and came in at less than $400! Ok, there goes Star #2 getting aligned. This was starting to looking interesting.

As I sit here thinking more about things, the third star is that today, Nov 1st is the anniversary of when I decided to branch out on my own. It has been a few years now and things have been pretty good as an independent, regardless of a recession. I am still working out kinks with this business (not my first, or second, or umm… yeah, we’ll leave it at that) but it has survived and I have even managed to pick up a couple of pretty big clients to have, even to my own surprise. As noted things are quiet right now, but I am looking at things picking up some in the new year. (I also still have time next year to help out if you need any!)

Deeper thoughts

For my part I know nothing with any certainty, but the sight of the stars makes me dream.

Vincent Van Gogh

I am still in shock that I am able to attend these summits, especially the MVP one. The quiet times with clients leave me with too much time to work on blogs, videos, photos, and ideas. So many ideas! The problem with lots of ideas, is a lot of thoughts come along. In fact the whole MVP award thing is still mind boggling. Obviously I’ve done something to have an impact that is beingnoticed. It is actually somewhat of a new concept for me too. I am going to the MVP Summit feeling so out of place this year, and I’m not fully sure why. Maybe it is the guilt of spending money that I could be using for other things, but it is important to also get out to network and meet more people. Maybe it since things have been a little quiet with clients that I don’t feel I’m as up to date as I should be with things. I really don’t know. I’m sure I’ll figure it all out over this week at the Summits.

So, as a head’s up you may see me sitting a couple of times this week off somewhere quiet for a while. Rest assured I’m doing fine, probably just thinking and processing everything, and don’t hesitate to to come and say hi. I will probably be burying myself into more thoughts of ‘do I really deserve all this’ , ‘Is this really all happening?’ and ‘will it all work out?’. Come give me the slight jarring out of that mindset and the reminder that I did it, and I am there to enjoy the time and everything else going on.

So, see you around Seattle this week, SQLLive360 in a couple of weeks and at SQL Saturday in Washington DC in just over a month! Off to get a little rest before starting things off early today.

 

Chris Bell, SQL Server MVP, MCSE & MCITP, is a 20 year veteran of using Microsoft products & SQL Server to create solutions for businesses, organizations and individuals. Chris speaks, blogs, writes articles and makes media of all kinds regarding SQL Server at WaterOxConsulting.com.

Click here to contact Chris or to follow him on twitter.

Chris is also the founder of WaterOx Consulting, Inc. which features SQL Server consulting services along with the destination location week-long training series: SQL Summer Camp. New in 2015 WaterOx Consulting is introducing the SQLPunch series of half day training events designed to help others learn to efficiently and effectively use SQL Server.

He is the founding president of PASSDC and organizes the annual SQLSaturday for the DC area. Chris frequently speaks at and attends SQL Server events, sharing his passion for all things SQL Server.

In 2012 Chris was a finalist in the worldwide Exceptional DBA competition and in 2014 he received the Microsoft MVP award in recognition of his open sharing of his knowledge with the technical community. His blog is currently syndicated to SQLServerCentral.com and ToadWorld.com

Changing Your SQL Server Name

Is the name of your SQL Server correct?

Is the name of your SQL Server correct?

Why Worry About The Server Name In SQL?

When the name of the actual host machine’s server name does not match the server name stored in SQL Server a lot of things can start to go wrong.

Reporting services can break, Connections denied, maintenance plans fail. Generally chaos can happen, but not always right away.

How does these names get out of sync? Usually is is from some sort of move or rebuild of a server. I experienced it with one client that physically moved data centers, and in the process decided they wanted to change the IP address and name for every server. We are talking thousands of servers moved, and every one of them renamed. That included over 750 SQL Servers.

I came onto the project after the move had been done and it took a while to realize that a lot of maintenance plans were failing. Turns out that the detailed checklist for the data center move, including renaming the servers, had left out the critical step of resetting the server name on each SQL Server. Production and development system were affected and needless to say it made for a fun week to get everything fixed as fast as possible.

Exceptions

There are a few exceptions to remain aware of when you are looking to rename a SQL Server:

  • Fail-over Clusters – When as instance is part of a SQL Server fail-over cluster, the renaming process outlined here will not work.
  • Replication – renaming computers involved in replication, except for log shipping with replication, as not supported. You need to remove replication for the change to be able to be done. Be sure to script out all your settings first so you can more easily rebuild it after the server name change. If you are using log shipping with replication and you completely lose the primary, you can rename the secondary. You can get more info on this scenario in the MSDN library here: Log Shipping and Replication (SQL Server)
  • Reporting Services – After a rename, reporting services may not be available. To fix this you need to edit the RSReportServer.config file in a couple of ways. Details can be found in MSDN here: Rename a Report Server Computer.
  • Database Mirroring – Mirroring needs to be turned off prior to changing the server name. Then once renamed you will need to re-establish the mirror using the new server name. Metadata for mirroring foes not update automatically.
  • Windows groups – If using a hard-coded reference to the server name. This occurs if the Windows group specifies the old computer name. You would need to update the Windows group after the name change to specify the new server name.

Now that that is all out of the way, let’s get to actually changing our server name.

Changing the Server Name

First you are going to want to check if there are any remote logins. If you have any remote logins, that reference the old server name, you will receive an error when you try to change the server name.

This script will check your server for any remote logins so you can remove them.

Be sure to record the remote logins so you can recreate them afterwards.

To drop any remote logins you may be found, you can use the following syntax:

Once the remote logins are out, you can go ahead and change the name of the server with this script:

To add the remote logins back after you have changed the server name you can use the following syntax:

After all the changes have been made you will need to restart the SQL Server service to be able to connect with the new server name.

Download it!

 

Chris Bell, SQL Server MVP, MCSE & MCITP, is a 20 year veteran of using Microsoft products & SQL Server to create solutions for businesses, organizations and individuals. Chris speaks, blogs, writes articles and makes media of all kinds regarding SQL Server at WaterOxConsulting.com.

Click here to contact Chris or to follow him on twitter.

Chris is also the founder of WaterOx Consulting, Inc. which features SQL Server consulting services along with the destination location week-long training series: SQL Summer Camp. New in 2015 WaterOx Consulting is introducing the SQLPunch series of half day training events designed to help others learn to efficiently and effectively use SQL Server.

He is the founding president of PASSDC and organizes the annual SQLSaturday for the DC area. Chris frequently speaks at and attends SQL Server events, sharing his passion for all things SQL Server.

In 2012 Chris was a finalist in the worldwide Exceptional DBA competition and in 2014 he received the Microsoft MVP award in recognition of his open sharing of his knowledge with the technical community. His blog is currently syndicated to SQLServerCentral.com and ToadWorld.com

 

Find all triggers in a SQL Server Database

What Are Database Triggers?

Do you know what triggers lurk in your database?

Do you know what triggers lurk in your database?

Triggers can be implemented to enforce business rules or referential data integrity in database applications.

There are even triggers that allow data modifications to multiple base tables of a view. I have actually used this in the past when working with 3rd party encryption tools prior to SQL 2005’s native encryption options.

Triggers are quite capable of being very powerful tools in your SQL Server database and application. There is a lot of debate on the use of triggers, but I’m not going to go digging into that in this post. In general they are preferred as a last resort if no other technique can be used to accomplish what you are trying to do (Foreign Keys, Stored Procedures, etc).

In general, I feel that triggers can be like hidden code in your SQL Server database. They are able to modify the way SQL Server behaves at a tabular level by overriding your original SQL code that inserts, updates or deletes data. Unless you know what each and every trigger in your database does, you run the risk of spending a lot of time trying to figure something out when things get wonky in your database or application. The key to being able to know what the triggers are doing, is to know what triggers are in your database, their type, as well as if they are active or not.

OMG! How Do I Find The Triggers In My Database?

You have to dig in the GUI to find triggers on tables and views

You have to dig in the GUI to find triggers on tables and views

Well, that’s where things get interesting. You can use the GUI in SSMS, but you have to dig through the tables & views to find the triggers. They aren’t in the same locations as stored procedures or functions. This isn’t much of an issue if you are working in a smaller database with a handful of tables. The issue arises when you have hundreds, or even thousands, of tables and views. Some may contain triggers, some may not. The only way you’ll know is to check each object for triggers manually by opening and closing the sub folders in the GUI, is in this picture.

To make things easier though, we can use the SYS.OBJECTS table to list out all the triggers within our database, include the table they are on as well as their type and status.

The following script will identify all the triggers in your database tables and indicate their type as well as if they are enabled or not.

Once you have identified the triggers, you can look more deeply into what they actually do and determine if they are truly required, or just “forgotten” code in your database.

Regardless, knowing what code is in your database, be it triggers or not, and what it does is important. Especially when it comes to troubleshooting odd activities.