SQL 2005 Compatibiliy Level [sp_dbcmptlevel]

Hi,

I would like to find out what other people are doing when restoring a SQL2000 database to SQL2005.

What compatibility level are you setting the database to:

  1. SQL Server 2005 (90)
  2. SQL Server 2000 (80)

Ref: http://msdn2.microsoft.com/en-us/library/ms178653.aspx

Background: I am using Plan Guides (which I am not happy about), and would like to do some further research around the database options.

Thanks

If we are talking about NAV database the recommendation I have got is to make a NAV backup from old db on SQL2000 and restore in new db on 2005.

I am going to do that when upgrading to SQL2005 this summer.

Yes its NAV, what else…

My db is over 50gb, so NAV backup/restore takes way too long. I have had no issues with SQL backup/Restore and so won’t be changing that.

I have some of my development copies set as 2005 compatibility mode, and some restored keeping 2000 compatibility mode. Was hoping to find out what other people are doing.

Yes its NAV, what else…

My db is over 50gb, so NAV backup/restore takes way too long. I have had no issues with SQL backup/Restore and so won’t be changing that.

I have some of my development copies set as 2005 compatibility mode, and some restored keeping 2000 compatibility mode. Was hoping to find out what other people are doing.

Witch version of Navision are you using or plan to migrate?

I think SQL Server 2005 in SQL Server 2000 mode isn’t support by Microsoft, but it will run.

To work properly with SQL Server 2005 in SQL 2005 mode you need at least Navision 4.0 SP3.

3.7db, 4.02 clients (with some 4.03) & 4.03 stored procs installed, which I think is ok. I Am not going to upgrade the db to 4.03, going to jump straight to 5.0 later in the year.

I will set it to 2005 compatibility for now. Thanks.

But, I would still like to know if there are any experiences out there with any issues around this setting or if its just ok to use any setting. I guess there must be some.

Thanks

Why are you using planguides?

Doesn’t SQL use the correct index for the query?

Its what Microsoft advised me to do. Quite a long story and would be happy to discuss again. I have some other posts on this websitre from a while back relating to plan guides, also check http://www.mibuso.com/forum/viewtopic.php?t=16343&highlight=guide where it is also discussed. I am not a SQL guru, but have got some plan guides working and they seem to do the job. It seems they are necessary on large tables with SQL2005 and the way Navision queries it.

Can you mix clients like that OK? In any case, why not just put 5.00 executables now. If you are upgrading anyway, surely thats the best so you don’t need to do it again later. if you are not ready to risk 5.00 then at least put 4.00 SP3 everywhere, and not just on some work stations.

We have 8 Citrix servers. 6 of these with 4.02 client with one of the later hotfixes. 4.03 client on the remaining 2 citrix servers because they are newer. It works. No issues so far, I know its not purist but I so far I have no reason to doubt mixing 4.02 & 4.03. I reckon I will schedul in the 5.0 client rollout but not for a few months.

one thing to watch out is this issue with 5.0 exe upgrade.

http://www.mibuso.com/forum/viewtopic.php?t=17446&highlight=upgrade+executable

Moved to SQL forum.

I know about the story and all the workarounds and solutions but frankly I am a little headstrong on this subject.

On most of the issues I get SQL to do wat I want. [:P]

Love to hear more about your specific issue though…

Yup, I am trying and learning as I go. I was a “normal” Nav developer with regards to only working on native databases and have only been working on SQL version for the last 2 years. Its a lot of work, and a steap learning curve getting to grips with the SQL side. But at the same time rewarding. Getting SQL to do what I want is my ultimate goal [;)]

Nic have you done Hynek’s course yet? It can help a lot.

Yes, twice. He knows me well :slight_smile:

It helped a lot.

Well, if you are guided by Hynek it should be ok. [H]

Comfortable with that, can always learn more was my point. We had lots of locking and performance issues before and since attending Hyneks course its been much much better.

Anyway, my original reason for the post was: SQL 2005 Compatibiliy Level [sp_dbcmptlevel]…