Orphaned User After Migration To SQL 2005

After migrate SQL 2000 to SQL 2005 the SQL-Logins lost the relationship (Database <-> Server).

To fix the problem:

Find the SQL-Logins which orphaned: Exec sp_change_users_login ‘Report’

Run following statement to resolve the problem: Exec sp_change_users_login ‘UpdateOne’, ‘Name’, ‘Name’

Note: This will only work for SQL-Logins.

There is a parameter “Auto_Fix“ available. I don’t use it due to security reasons.

Recommendable SQL Server Posts

Two excellent articles by Rodibdably.

The first article cover maintenance of the TempDB.

The second describe the use of database trigger to audit DDL operations.

Backup transaction log when master db and database files are damaged

To backup the transaction log first you must have a working master database. Without the master database the sql server won’t start.

In this case use the KB 253817.

The Need Of A Full Backup

We all aware that a full database backup is the basis of our backup strategy.

So, what will happen when you create a new database on your production server because high level management need this a.s.a.p ?

You use the full recovery model to include this database in your backup strategy. Of course, the full backup will run as always later the day.

The requested department started to work with the new database. Hours later somebody reported a problem with the new database.

During investigation you find out that most of the data from the last hours are deleted and no longer available.

But, you are running the database in full recovery mode. You started with the recovery from the transaction log.

Normally this is not a big deal. This time it will not work.

Why ?

Although the database is using the full recovery model a full backup was never done.
Therefore the transaction log was reused by the SQL Server after reaching the end of the log file.

This truncation of the log file will also occur when you truncate the transaction log after the last full backup.

Above scenario may not be the best example but it show you the importance to do a full backup.

Split your I/O

There are two different types of physical disk I/O: sequential and random

For our work it is very helpful to know which type of I/O occurred on our system.

The sql server transaction log is a example for sequential I/O.
This files are written sequentially and there is only a small move of the disk drive head.

On the other side there are our database files.
Due to the need of search data there are many seeks and rotations on the disk needed.
This type of I/O is much slower.

To speed up your performance it is a good solution to split your I/O.

Depending of your budget use as many disks as possible.

Consider to use a own physical disk array for:

• SQL Server Transaction Logs (sequential)
• Backup Files (sequential)
• Program Files, OS Files (random)
• Database Files (random)
• TempDB (random)