Blog

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.

Untrusted Foreign Keys

  What is a Foreign Key?

Do you trust your foreign keys?

Do you trust your foreign keys?

A foreign key is a link between 2 tables that is used to enforce referential integrity in the database.

For example, if you have an order and a customer table, there is probably a logical relationship between the 2 tables. An order can’t exist without being linked to a customer record.

When trusted, these keys help ensure that the data in your database stays ‘clean’ and logical.

By establishing trusted foreign keys in SQL Server between your tables, the optimizer is able to make some assumptions about the data and therefore make more efficient execution plans for your queries.

Foreign keys are trusted by default when created since checks are done to ensure the data all lines up. If the data does not match as expected, errors are relayed and the key is not created. Once established they keys will prevent ‘bad’ data from being entered into your database so long as they remain trusted.

 

What is an Untrusted Foreign Key?

An untrusted foreign key is one that has had the referential integrity of the relationship removed. SQL Server is unable to ‘trust’ that the data is clean in both tables and therefore isn’t exactly sure of the best way to proceed. This can start to show itself by queries getting slower as the optimizer starts to perform extra checks to make sure the data it is getting is good.

 

  How Do They Become Untrusted?

We have established that foreign keys are important in relational databases since they check & help enforce the referential integrity of the data. Sometimes though, when doing large bulk loads and similar operations, SQL Server can slow down due to the large number of checks being done.

One of the ways to improve the performance of these bulk loads is to disable the foreign keys on the tables being loaded. This is usually a safer & faster process than just dropping and recreating the keys.

Foreign keys then become untrusted when they are not re-enabled correctly after the bulk load or similar operation is completed.

Usually the foreign key is re-enabled with the CHECK CONSTRAINT option in the ALTER TABLE statement. This is fine to re-enable the foreign key, but it does not tell SQL Server to re-verify the integrity of the data.

Because of this, SQL will not know if the relationship can still be trusted, that the data is ‘clean’ between the 2 tables. This results in the SQL Server optimizer ignoring the foreign key restraint and checking the data integrity itself with extra processes added to your query execution plan.

 

  How Do I Trust a Foreign Key Again?

Before you are able to trust foreign keys again, you have to identify the ones that are no longer trusted first.

This first snippet of code goes through your database and identify the foreign keys & constraints that are enabled yet not trusted. (We don’t have to worry about disabled keys)

This will identify the schema, object and name of the keys that are not trusted in your database.

Once you have that result set you can use the following statement to re-establish the trust for each result in the list:

ALTER TABLE <s.name from results>.<o.name from results> WITH CHECK CHECK CONSTRAINT <i.name from results>

The CHECK CHECK in the syntax seems odd, but it is just due to the alignment of 2 different options with the alter table statement.

The first is the WITH CHECK statement. This tell the ALTER statement to validate the table contents against the foreign key. By default when re-enabling an existing foreign key or constraint this is set to WITH NOCHECK. You have to explicitly define it when re-enabling a foreign key or constraint.

The second is the CHECK CONSTRAINT statement. This is used to configure if the constraint is enabled or disabled. CHECK CONSTRAINT is enabled, while NOCHECK CONSTRAINT disables the foreign key.

If you have a lot of results from the previous script, you can use the following script which will output the ALTER TABLE statements so you can copy & paste the ones you like.

This script could be easily modified to also apply the alter, but I personally prefer to look at the results and changes to be made prior to having it run, just in case.

The script can also be modified to enable all foreign keys & constraints by using ALL rather than the [i.name] but be sure that you want all keys and constraints enabled or disabled first.

Download it!

Any way you do it, you want to make sure your foreign keys and constraints are trusted so the SQL Server optimizer can create efficient plans for your queries.

 

P.S. – Don’t forget to sign up for our new SQL newsletter and your chance to win a $25 Amazon gift card at the end of November, 2014!

You can sign up here!

 

 

 

I Must Be Doing Something Right – MVP Award

I am extremely excited to announce that I have just received the Microsoft MVP award for SQL Server for 2014.

MVP_Horizontal_FullColor

For those that know what this whole MVP thing is there isn’t much more to say other than the headline:

I must be doing something right!

and

Yay!

 

For others that may not know what this is all about, here is a little more info of why I am excited and honored to receive this recognition:

Read more →

Video: Walk & Talk #003 – RPO

What is the RPO?

And why does it matter?

Join me as I walk through Jefferson Patterson Park’s Indian village and discuss the Recovery Point Objective and what it means is respect to SQL Server.

Location: Jefferson Patterson Park

More info: http://www.jefpat.org/

Video: Walk & Talk #002 – RTO

Recovery Time Objective (RTO)

What is it and why does it matter?

I am actually walking this time! Join me to walk & talk through what the RTO (Recovery Time Objective) is with regards to SQL Server.

Location: American Chestnut Land Trust – South Side Trail: Gravatt Lane (0.72 miles long)
more info @ http://www.acltweb.org/