This post is not really about BizTalk… but since I’ve been configuring several environments for my client, I decided to take these notes. Basically, we have some BizTalk processes that communicate with the database in SQL Server that we are moving to SQL Server 2008 R2.
📝 One-Minute Brief
Migrating databases to a new SQL Server requires more than just moving data. This post covers two critical but often overlooked considerations: optimizing SQL Server Management Studio (SSMS) performance by adjusting “Edit/Select Top Rows” limits and resolving the “Database diagram support objects” error by correctly setting valid database owners after a restore or attach.
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
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
However, in a production environment, you should reduce these numbers further, maybe to 20/10.
To modify these options, you should:
- Run the SQL Management Studio.
- Click on the Tools -> Options menu.
- Select the option SQL Server Object Explorer and edit the following properties:
- Value for Edit Top Rows Command
- Value for Select Top Rows Command
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 R,2 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 attach again
Cause
This error is occurring 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 the database, right-click, and choose Properties)
- Select the Files tab.
- And give a valid user to the database owner
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.
