No FK constraints either. When using Reflector to peek inside the LINQ-To-SQL ORM, you can see that it uses an explicit transaction with Read Committed isolation level. Guess I'll...

Just to add, I can't find anywhere that we're using SERIALIZABLE isolation level (we have no TransactionScope usage either) but after googling around there appear to be one or two...

Thanks a lot for all this, it's been very helpful indeed 🙂

I am one of the developers 😉 (accidental DBA scenario). And yes it's a consequence of the data access tech, more specifically the ORM tool (LINQ-to-SQL). SERIALIZABLE isn't needed, so...

Don't forget, in Database Mirroring, the Mirror is constantly in Recovering state. If you kill the Principal without first failing over to the Mirror then it's state will just be...

Thanks for pointing me at that article, makes sense.

However what I don't get is why range locks are coming into play at all when it's an IDENTITY based table and...

Sorry my bad, I misread your question. In that case without automatic failover (a witness server) you'll need to manually fail over (see my initial response) from the principal to...

Thanks for the replies SQLKiwi. The table in question is clustered/pk'ed on an identity column and thus there is no "SELECT to see if exists and then INSERT" pattern going...

SQLKiwi:

The INSERT and SELECT are from 1 table with 1 index (the clustered one), so how does the UPDLOCK come into play here?

Answers to your questions:

1. READ COMMITTED.

2. That particular deadlock is rare, but it's potentially a large problem.

3. It's a LINQ to SQL application so the INSERTs all look like:

exec sp_executesql...

After you've got the mirror up and running it does say "(Principal, Synchronized)" next to the Principal and "(Mirror, Synchronized)" next to the Mirror? And you do have a Witness...

Just to add I figured out it was pretty easy to have the Publisher and Subscriber in the same database, you just can't do it via the SSMS wizard. Obviously...

Cheers Perry. You rock. The second option you provided works a treat.

Alex.

Thanks for your reply Perry.

It figures you should be able to add new Files to an existing FileGroup, but my question was about adding a new FileGroup entirely to an...

Firewall issue! We found the problem/solution via this support document:

http://support.microsoft.com/kb/941526

Specifically the line which says:

Enable User Datagram Protocol (UDP) traffic to the mirror partner server over port number 1434....