Directions Diary - DEV Session - Migrating from C/SIDE to SQL

Mark Brummel is presenting this session. As you might know, we're quite good friends so I was kind of obligated to go to his session. I've been reviewing his presentation, gave him some input and I'm curious if he payed attention and changed his slides .

First of all: why should people move to SQL? Well, for all the technology that SQL Server offers and C/SIDE doesn't. Things like:

• Higher availability (clustering, TL backups, ...)
• Analysis/Reporting/Integration services ...
• ...

Before moving to 6.0, you should be as close as possible. So, upgrading to 5.0, then moving to SQL is a good thing to do, and you can do it now.

Furthermore, there are 10 steps to migrate to SQL Server:

• Upgrade or Migration. In some cases, when you're in an earlier version, you should first upgrade to a higher version. e.g.
  • 3.01 - 3.60: a full upgrade to the latest version is recommended
  • 3.70: a technical upgrade to built 19868 (3.70.B) is recommended because of the SQL bug in earlier version.
  • 4.0 - 5.0: use 4.0SP3 update 6 or 5.0 update 1 (and see what you need for index hinting)
  • 6.0: a full upgrade is inevitable

  During this session, Mark will talk about migration, not upgrade!

• Hardware

  See that the memory is OK and that you use 64 bit hardware, OS and SQL Server. This is a major difference.

• Check SQL Datatypes

  Biggest issue of SQL is that it doesn't support dates from before 1753. NAV supports year 0000. the migrate.fob on the NAV CD is used for this. It is a very time-consuming job on big databases !!

• Test conversion

  During this conversion, measure the time, because you'll need that info to tell the customer how long he will be down. To fasten the conversion, here are some tips to speed it up:

  • Turn off always rowlock
  • Restore per company, data common... First
  • Turn off SIFT levels
  • Turn off indexes
  • Make sure Transaction log files are big enouh... Expanding those takes a lot of time.
• Testing

  Both manual testing as benchmark testing is recommended. Manual testing is putting the users to the machine and see if everything works like it should. Benchmak testing is the next step .

• Benchmark testing

  This is the ONLY way to test performance without go-live. The intention is to simulate user input, and simulate the user load (multiple users). Tools Mark uses is:

  • Benchmark tookit
  • Client monitor
  • SQLPerform tools
  • SQL profiler
  • Performance monitor

  Demo time! Het showed how the Benchmark toolkit worked, and how it could be set up. He started a couple of NAV clients and ran som random profiles. He showed the Windows Performance Monitor. The counters he uses were (Average read/write queue length, lockrequest/sec, Checkpoints, Page life expectancy, ...).

  He also showed the SQL Profiler. It logs all queries that have a duration of whatever you set it up (e.g. 50 ms).

  Next, he showed some SQLPerform queries.

• Possible performance tuning solutions

  You can do index & sift tuning, you can implement the specific FIND instructions, ... . Mark also showed the advantages of using Temp tables ... And the form property "SourceTableTemporary" property.

• Conversion

  Perform the migration company by company.

• After Go-Live

  Check the profiler data to check if everything is running like expected

• 3 months after go-live

  After 3 months, you have statistical info that you can use to do a deep performance tuning. Warn the customers you want to come back after 3 months to tune the database to their use.

If you might wonder ... Yes, he included some of my suggestions into his presentation .