Information on SQL mirroring, Replication, and Log Shipping

Nobody

Just and FYI and as I saw some discussions earlier on this topic, and did some testing on my own.

1. Replication – Replication works fine the only issue I had was for some reason the $ndo$dbproperty did not get published as an article so I exported the table over and everything worked after that. I was able to log into the replicated database and run reports. There is one significant caveat with replication once the articles have been published and replication started you cannot alter the schema of the NAV database at all you actually cannot compile any changes or it will error out. You need to delete the publication and make the changes and then republish. So there is some thought and planning that needs to be done before using replication.

2. Database mirroring – This only works on SQL Enterprise edition and is extremely touchy and takes a long time to get the setup correct, but it does work. While mirroring is running the mirrored DB must stay in “Restoring” mode so there is no access to the mirrored DB. I failed over to the mirror and was able to log into NAV and it worked fine no errors. I am not sure this solution makes much sense to the vast majority of Dynamics – NAV customers and it would be very expensive as you need two SQL Server 2005 Enterprise edition licenses. NOTE: configuring the sql mirroring endpoints is a killer it took me a day and a half to get them right.

3. Log Shipping – Log shipping works very well you just need to be aware that it puts the logged shipped DB in read-only mode which allows you to log in and run reports, but nothing else.

My opinion is log shipping is the best of the three

ara3n

Thank you for this info. I havent tried to setup replication, and try all the different option, just because of the enormous time that it requires.
So this info will help a lot of people when they making the desision.

davmac1

From talking to Microsoft, their people say you only need to buy one license if the mirrored database is only being used as a mirror - as it is in your scenario.

Since standard supports mirroring - what option do you need that is only available in Enterprise to make database mirroring work with Navision?

we are currently using Log Shipping in SQL 2000 - works well, except sometimes the restore fails and we have to rebuild the replicated database.

Nobody

Hmm interesting I was told that Mirroring was only supported on Ent. Edition so I did not even try to test it on standard. Looked it up and it looks like to use all features of Mirroring you need Ent, but the basic mirroring will work on Std. Learn something new everday. :-)

kriki

[Topic moved from Navision forum to Navision Tips & Tricks forum]

Nice One! =D> =D> =D>

DenSter

I talked to one of the guys in the technology lab at MSFT Germany, who have tested those methods. He said the same. They had big problems and issues with replication and mirroring, and the only one that looked really promising was log shipping.

kriki

If I remember correctly. Logshipping uses the transactionlog to do it. But in the transactionlog, the BLOB's are not logged. So this is something to remember!
Please, correct me on this if I am wrong!

Nobody

A little update on replication. I discussed the error when you try to modify an object with some others and they were able to fix it. You just need to run (Sp_configure ‘max text repl size’, 2147483647) and you are able to make any changes you want and they will replicate over.

As far as BLOB fields I did not test it but can see no reason why transactions against image columns in SQL Server (BLOB's are converted to datatype image) would not be logged? I avoid BLOBs like the plague because they are perfomance killers as SQL needs to go out to the large object store to retrieve the image data imformation which is much slower than pulling data from the actual row.

ara3n

The (Sp_configure ‘max text repl size’, 2147483647) works fine if you are just adding or removing fields.

If you are changing the PK of a table for example, you'll see get the error. I ran into this on a sql sever that was being replicated. They moved to log shipping.

bbrown

SQL 2005 Standard does support mirroring but with limitations. Single REDO thread and the safety setting is always on.

On replication, are you referring to transaction or snapshot?

Nobody

Transactional Replication.

kriki

And now that we have SQL-forums ...
[Topic moved from Navision Tips & Tricks forum to SQL General forum]

sporri

kriki wrote:

If I remember correctly. Logshipping uses the transactionlog to do it. But in the transactionlog, the BLOB's are not logged. So this is something to remember!
Please, correct me on this if I am wrong!

The blobs are logged, but it's the same as in bulked logged mode, a change in a blob will result in all the blob being written to the transaction log.

bbrown

davmac1 wrote:

From talking to Microsoft, their people say you only need to buy one license if the mirrored database is only being used as a mirror - as it is in your scenario.

Sounds like it came from marketing. Since you license the "SQL Server" and not the "database", and most customers will probably use the backup server to host other databases, the free license won't apply

Also if a client needs the short recovery times provided by these methods, then they should have no problem justifying the additional license cost.

I have used each of these methods in different systems. The keys is to understand the requirements and select the appropriate method.