This post is not really about BizTalk… but since I’ve been configuring several environments in 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.
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 more these numbers, maybe 20/10.
To modify these options you should:
- Run the SQL Management Studio
- Click on 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”
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 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.
