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

Posted: January 26, 2012  |  Categories: Other

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 the 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 a 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.

Author: Sandro Pereira

Sandro Pereira lives in Portugal and works as a consultant at DevScope. In the past years, he has been working on implementing Integration scenarios both on-premises and cloud for various clients, each with different scenarios from a technical point of view, size, and criticality, using Microsoft Azure, Microsoft BizTalk Server and different technologies like AS2, EDI, RosettaNet, SAP, TIBCO etc. He is a regular blogger, international speaker, and technical reviewer of several BizTalk books all focused on Integration. He is also the author of the book “BizTalk Mapping Patterns & Best Practices”. He has been awarded MVP since 2011 for his contributions to the integration community.

Leave a Reply

Your email address will not be published. Required fields are marked *

BizTalk360
BizTalk Server

Over 500+ customers across
30+ countries depend on BizTalk360

Learn More
Serverless360
Azure

Manage and monitor serverless
components effortlessly

Learn More
Atomicscope
Business Users

Monitor your Business Activity in iPaaS
or Hybrid integration solutions

Learn More

Back to Top