SQL 2005 Database Mirroring

bbrown

Is anyone running Database Mirroring in production? We have a client that wants to implement it and I am just trying to find if there are any known issues. I ask this to Microsoft and their basic response was "try it, let us know if it works".

Which modes did you test and which one did you decide to use?

What was the performance impact of high availability mode?

Are the servers similar or is the mirror a slower box?

Any issues with Navision operation?

Nobody

I have never implemented it at a customer, but it makes no structural changes to the database so it really does not matter that it is a Navision database it would work the same for any kind of database. The highest availability mode could add substantial overhead because the transaction is not completed and comitted until it confirms the write on the mirror so it could cause some blocking issues and timeout issues depending on the speed of the connection. The lowest level does not do this. I just do not think mirroring is a good fit for an ERP database as there are better solutions available that would not add overhead like log shipping and replication.

DenSter

As I understand it (and I have no actual knowledge about it, just reproducing what an NAV person from MSFT Germany told me) both replication and mirroring can have trouble in NAV due to the way it uses the timestamp field (which isn't really a timestamp field, but that's another discussion) to simulate the NAV versioning principle. He said they have not fully tested it, but for having a copy of the database, log shipping looked like the best option for NAV. Again, no personal experience, just reproducing what others have said.

kriki

DenSter wrote:

As I understand it (and I have no actual knowledge about it, just reproducing what an NAV person from MSFT Germany told me) both replication and mirroring can have trouble in NAV due to the way it uses the timestamp field (which isn't really a timestamp field, but that's another discussion) to simulate the NAV versioning principle. He said they have not fully tested it, but for having a copy of the database, log shipping looked like the best option for NAV. Again, no personal experience, just reproducing what others have said.

I heard exactly the same.

bbrown

Thanks for the replies..

From what I have seen Mirroring is much closer to Log Shipping then Replication. Mirroring is based on transaction log updates. I don't see where timestamps would apply. I understand the application of timestamps with replication, since it is based on individual table updates.

We have a few sites running Log Shipping, so that is our fallback position. Mirroring is appealing for its ability to provide unattended failover (server not client). Log Shipping requires user intervention to brig the backup online. This can be an issue if the system fails when no one is around.

The servers will be in separate buildings with a GB fiber link between them. The current production server (4x2.2 Ghz, 8 GB, 8x36 RAID 10 Data) will become the mirror. A new server (4x3.0 Ghz DC, 16 GB, 14x72 RAID 10 Data) will become the new production box. The new server is 64 bit, and the older is 32 bit. Both will be dedicated to Navision. The backup will also hold a test/development database that will see light use.

It looks like we have some testing and prototyping to do. Our preferred solutions would be:

1. Mirroring in High Availability mode
2. Mirroring in High Reliability Mode
3. Log Shipping

Further comment/suggestions are welcome.

DenSter

It has something to do with the mirroring and/or the replication process modifying the timestamp values during the process, which screws up NAV's versioning principle, and can lead to the app thinking it is corrupt due to inconsistent values.

From what I understand, log shipping is the only method that actually creates an identical data set. Like I said though, this is hearsay. From a reliable source though, but still...

bbrown

It depends on what you mean by 'identical data sets'. Log Shipping works by sending transaction log backups and restoring them. This is similar to mirroring except mirroring does create the backup.

DenSter

All I was told:

Mirroring: not good, MSFT has seen problems
Replication: not good, MSFT has seen problems

Only promising option left: log shipping

I've actually seen a replicated database have big NAV problems, because the replication process actually modified timestamp values in the NAV database.

This guy that I talked to used to work for Navision, then for a solution center, and now works for MSFT in their tech labs. He's been involved in major benchmark testing and seems to know his stuff.

If I had to take any advice about this, I would take his, but that's me. Do what you want I'm curious to see what happens either way.

bbrown

I can definitely see the issues with replication. I have several sites running with log shipping, so I know that is a valid option. In the past, log shipping or clustering $$$$ have been my preferred solution. My current problem is I have a customer that likes to keep his hands in the technology. He attended a Microsoft dog-and-pony show and saw mirroring. Now he want it. I will need to give him more specific reason not to use it. Microsoft already told him to use it.

DenSter

Of course the MSFT SQL Server people will say there's no problem, but they don't know s%$t about NAV. If Microsoft NAV tech support recommend mirroring, then I guess that's the official word. Personally I would get confirmation myself through our tech support. Seeing as I don't have any personal experience with it I guess I'm out of arguments.

I would tell the customer though that there have been known issues with mirroring NAV databases, and that your time will be fully billed if/when the problems start.

With that in mind, go for it you might learn some cool things.

bbrown

Thanks for all the input. We are going to setup mirroring on a test system and make a decision from there. I will let everyone know what happens.

DenSter

Do yourself a favor and get in touch with NAV tech support, and ask them if there are any known issues with mirroring and if there are, what kind of solutions there are. If you have access to partnersource, I think they've opened up internal KB.

bbrown

That was the first thing I did. They will not commit one way or the other. I asked them to escalate the call, but have not heard back yet. They just keep repeating the Microsoft disclaimer:

Microsoft support policies do not apply to the database mirroring feature in SQL Server 2005. Database mirroring is currently disabled by default, but may be enabled for evaluation purposes only by using trace flag 1400 as a startup parameter. (For more information about trace flags, see Trace Flags (Transact-SQL).) Database mirroring should not be used in production environments, and Microsoft support services will not support databases or applications that use database mirroring. Database mirroring documentation is included in SQL Server 2005 for evaluation purposes only, and the Documentation Policy for SQL Server 2005 Support and Upgrade does not apply to the database mirroring documentation.

This disclaimer is from the Pre-SP1 release of SQL 2005. Database Mirroring was released for production use as part of the SP1 release.

Microsoft SQL Server 2005 Service Pack 1 (SP1) adds Database Mirroring, SQL Server Management Studio Express, additional options for ISVs and normal feature fixes

There is nothing in PartnerSource on this subject.

SQLGuru

there's issues with synchronous mode - a transaction commits when both, principal and mirror have committed.

i would choose asynchronous mode (high performance mode) or logshipping. but make sure you also migrate the logins to the secondary machine, if applicable including their sids, passwords and default databases!!! they are stored in master which cannot be mirrored or shipped, and this information needs to be identical to that stored in the navision database(s).

bbrown

Synchronous mode will be impacted by the lag between the 2 servers. Exactly what that impact is, no one seems to know. Both the servers are dedicated to Navision and neither one is being pushed. They are in seperate buildings and connected by a 1 GB fiber link.

We are considering asynchronous mode or log shipping as a fallback. Our customer would prefer the features of synchronous mode.

On the issue of users there are 2 solutions.

1. Use Windows Authentication. The Navision sync process would handle the user creation on the mirror.

2. If using Database Authentication you would need to manually create the login on the mirror before adding to Navision and syncing.

SQLGuru

bbrown wrote:

2. If using Database Authentication you would need to manually create the login on the mirror before adding to Navision and syncing.

I am afraid you cannot do this, neither with logshipping nor with mirroring as both rely on transactional consitsency in order to redo transactions originating from the primary. so sids have to be identical.

bbrown

We would need to create the login on the primary, then transfer it to the mirror to keep the same SID.

SQLGuru

bbrown wrote:

We would need to create the login on the primary, then transfer it to the mirror to keep the same SID.

plus: same password, of which the hash can be transferred
plus: same default database, which is no longer just an id in sql2005 but the name itself. however, the logins' default databases have to exit on the mirror prior to the transfer of the logins.

bbrown

plus: same default database, which is no longer just an id in sql2005 but the name itself. however, the logins' default databases have to exit on the mirror prior to the transfer of the logins

Leaving the login's default database to MASTER resolves this issue.

Administrator

[Topic moved from Navision to SQL General forum]

bbrown

An update:

We have the mirroring up and running, and so far no issues. There seems to be no noticable performance impact.

muratkarabek

Hello bbrown,

I have few questions below :

1. Have you faced any problem until now in running Navision with mirroring?

2. Which version of sql 2005 are you using? Standard or Enterprise?

3. Are your servers at different locations and what is the connection speed between them?

Thanks for your answers

Murat

bbrown

muratkarabek wrote:

Hello bbrown,

I have few questions below :

1. Have you faced any problem until now in running Navision with mirroring?

2. Which version of sql 2005 are you using? Standard or Enterprise?

3. Are your servers at different locations and what is the connection speed between them?

Thanks for your answers

Murat

1. We have not experienced any issues with the operation of Navision.

2. The primary server is 64 Bit Enterprise. The secondary server is 32 bit enterprise and I don'trecall what we are using on the Monitor server. You cannot mirror between different versions.

3. The primary and monitor are in 1 building. The secondary is in a second building about a quarter mile away. They are connected by a 1 GB fiber connection.


Remember that the process mirrors only the Navision database. Any thing else must be manually updated on both servers. An example is the users. If you are using Database Auth. then they must be added to each server manually before adding to database.

Also any maintenance operations that require single-user mode will require the mirror to be shutdown.

esatkaraoz

Hello Bbrown,

You say that, so far the system is up and running. My question is:

1) Is it because, there was no failover of the primary server in this meantime (and therefore no problem has been encountered due to "commit" delay that might have been expected.)

2) Is it because, the system managed the availability and continuity of the database, even though there were some failovers of the primary server.

Your detailed comments will be highly appreciated.

Kind regards.

Esat Karaoz

bbrown

esatkaraoz wrote:

Hello Bbrown,

You say that, so far the system is up and running. My question is:

1) Is it because, there was no failover of the primary server in this meantime (and therefore no problem has been encountered due to "commit" delay that might have been expected.)

2) Is it because, the system managed the availability and continuity of the database, even though there were some failovers of the primary server.

Your detailed comments will be highly appreciated.

Kind regards.

Esat Karaoz

We have had a number of failures. Most of these were scheduled events to test the system. We have also had a couple of unscheduled failures. These were usually network communication failures. No committed data was lost.

We are running syncronous mirroring with full safety. The means that updates are committed to both servers or neither. In this setup the databases are never out of sync.

During a failover any active transactions are aborted and rolled back. Users must manually connect to the backup server.

David_Singleton

Is it just me, or does it seem that NAV customers want one of two backup strategies:

a/ Joe makes a backup most nights before he goes home, except on Tuesday's when he takes the kids to Soccer, and the third Wed of each month, and also on the few times he forgets, but generally we have a backup thats not more than 3 days old oh and he tested the backup in 2003 so we know it restores OK.

or

b/ We had a team of ex NASA engineers that designed a server farm that cost more than 3 years of company revenue, and is designed to withstand a 15Megatonne nuclear blast up to 1,000 meters away. And will have the server back on line in under 7 milliseconds.

](*,)

David_Singleton

bbrown wrote:

... The current production server (4x2.2 Ghz, 8 GB, 8x36 RAID 10 Data) will become the mirror.
...A new server (4x3.0 Ghz DC, 16 GB, 14x72 RAID 10 Data) will become the new production box....

Am I missing something here? In a mirrored environment the system will be as fast as the slowest machine right? So why would have one machine faster than the other?

Sorry I have never configured mirroring on NAV (in SQL at least), so I am just asking.

bbrown

I think you hit it on the nose. Option B usually last until they see the price tag. :shock:

bbrown

David Singleton wrote:

bbrown wrote:

... The current production server (4x2.2 Ghz, 8 GB, 8x36 RAID 10 Data) will become the mirror.
...A new server (4x3.0 Ghz DC, 16 GB, 14x72 RAID 10 Data) will become the new production box....

Am I missing something here? In a mirrored environment the system will be as fast as the slowest machine right? So why would have one machine faster than the other?

Sorry I have never configured mirroring on NAV (in SQL at least), so I am just asking.

That's sort of right. The critical component is the write performance on the transaction log. This is the part that must occur on both servers to consider the transaction committed. The data write happens as a background process. As long as the backup system can handle the transaction volume (including peaks) you don't need to have exact systems.

For example, the primary server may average 15% utilization (with peaks to 40%) but the backup may average 30% utilization (with peaks to 70%).

David_Singleton

bbrown wrote:

David Singleton wrote:

bbrown wrote:

... The current production server (4x2.2 Ghz, 8 GB, 8x36 RAID 10 Data) will become the mirror.
...A new server (4x3.0 Ghz DC, 16 GB, 14x72 RAID 10 Data) will become the new production box....

Am I missing something here? In a mirrored environment the system will be as fast as the slowest machine right? So why would have one machine faster than the other?

Sorry I have never configured mirroring on NAV (in SQL at least), so I am just asking.

That's sort of right. The critical component is the write performance on the transaction log. This is the part that must occur on both servers to consider the transaction committed. The data write happens as a background process. As long as the backup system can handle the transaction volume (including peaks) you don't need to have exact systems.

For example, the primary server may average 15% utilization (with peaks to 40%) but the backup may average 30% utilization (with peaks to 70%).

Thanks that helps me clarify a few things I was not 100% sure of.