Some considerations that we should have when migrating databases to a new SQL Server

Posted: January 26, 2012 in Other
Tags: ,

This post is not really about BizTalk… but since I’ve been configuring several environments in my client I decided to take this notes. Basically we have some BizTalk processes that communicate with database in SQL Server that we are moving to SQL Server 2008 R2.

Consideration 1: Change Edit Top 200 Rows and Select Top 1000 Rows

SQL Server Management Studio is configured by default, for performance reasons, with the following options:

  • Select Top 1000 Rows
  • Edit Top 200 Rows

default-options-select-edit-sql

In my opinion I think these quantities a bit exaggerated and I suggest you to always reduce them. In my LAB environment I set the following values:

  • Select Top 100 Rows
  • Edit Top 100 Rows

my-options-select-edit-sql

However in production environment you should reduce more this numbers, maybe 20/10.

To modify these options you should:

  • Run the SQL Management Studio
  • Click in the menu Tools -> Options
  • Select the option “SQL Server Object Explorer” and edit the following properties:
    • “Value for Edit Top Rows Command”
    • “Value for Select Top Rows Command”

edit-options-select-edit-sql

Note: If we set these properties: ““Value for Edit Top Rows Command”, “Value for Select Top Rows Command” with the value 0 (zero) it will return all records from the table.

 

Consideration 2: Set a valid owner to database

As I said earlier, we did some database migration and when attempting to create a diagram (New Database Diagram) in one of these databases on SQL Server 2008 R2 I obtained the following error:

TITLE: Microsoft SQL Server Management Studio
——————————

Database diagram support objects cannot be installed because this database does not have a valid owner.  To continue, first use the Files page of the Database Properties dialog box or the ALTER AUTHORIZATION statement to set the database owner to a valid login, then add the database diagram support objects.

——————————

This error occurred in two situations:

  • After I performed a database restore from another server
  • Or after making a detached, move the files to a new directory and attached again

CAUSE

This error is being originated because the database does not have a valid owner.

The "no valid owner" issue usually comes up when databases are owned by SQL-authentication logins and are upgraded, detached/attached to another server, or restored from backup to another server.  The SID (a large number) doesn’t match any existing login on the new server, so the owner name remains whatever it was on the old server, but it is marked invalid.  Explicitly setting the owner to a valid principal on the server solves the problem.

SOLUTION

In order to solve this problem we have to:

  • Access database properties (select database, right-click and choose Properties)
  • Select “Files” tab
  • And give a valid user to database owner

valid-owner-sql-database

Note: A good best practice is to change the database owner immediately after creating, restoring or attaching a database. Unless you have a reason to do otherwise, I suggest you to specify “sa” or one SQL service user as the database owner.

Tags: SQL

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s