PCLaw's standard edition stores your books in c-tree data files on the server. PCLaw Enterprise stores them in Microsoft SQL Server. Moving from one to the other is the single biggest change most PCLaw firms ever make to their system, and it is worth doing carefully. This guide covers when to do it and how.
Signs it is time to move
- Slow posting and slow reports that get worse as the day goes on or as more people log in.
- Repeated data problems: integrity errors, index rebuilds, or files that need repair after a crash.
- A growing firm. More users and more years of history both put pressure on a file-based database.
- Backups that need everyone out. SQL Server can be backed up while people work, and restored to a point in time.
- An IT or insurance requirement for auditing, encryption or monitored backups.
If none of these apply and the system runs well, there is no need to move for its own sake.
What changes and what does not
Your staff will see the same PCLaw screens and the same data. What changes is underneath:
- The database runs as a service on the server instead of as shared files, so a workstation that crashes mid-entry is far less likely to damage anything.
- Backups run while the firm works.
- The server needs more memory and proper SQL Server maintenance.
- You need SQL Server licensing and the Enterprise edition of PCLaw. Confirm both with the vendor before planning dates.
Phase 1: Assess
- Record your exact PCLaw version. The c-tree and SQL editions are released as a pair, and you may need to upgrade first to reach a version that can be converted.
- Measure the data: size on disk, number of matters, years of history.
- List everything that touches PCLaw: bill and cheque templates, report layouts, links to other software, scheduled tasks.
- Check the vendor's current system requirements for supported Windows Server and SQL Server versions.
Phase 2: Clean the data first
A conversion copies whatever is there, including damage. Before migrating:
- Run Verify Data Integrity and resolve every error.
- Reconcile all bank and trust accounts to the most recent month-end.
- Close any fiscal years that should already be closed.
- Take a full, tested backup. See our PCLaw backup guide.
Failed migrations we are called in to rescue have usually skipped this phase.
Phase 3: Prepare SQL Server
- Size the server for the database, with memory to spare. SQL Server Express is limited to 10 GB per database and uses little more than 1 GB of memory for data, which rules it out for many firms.
- Put data files, log files and backups on separate drives where you can.
- Set up nightly full backups, transaction log backups through the day, and a weekly integrity check from the first day.
- Agree the security model: who administers SQL Server, and which accounts PCLaw uses.
Phase 4: Test migration
Never convert the live data first.
- Copy the production data to a test environment.
- Run the conversion there.
- Record how long it takes. That becomes your cutover window.
- Compare the results against the original using the checklist below.
- Have two or three staff work in the test system for a day: enter time, produce a bill, print a cheque, run their usual reports.
- Fix whatever they find and repeat until a run is clean.
Phase 5: Cutover
- Choose a weekend, and not month-end week.
- Freeze PCLaw: everyone out, and stay out.
- Take a final backup of the c-tree data and keep it untouched.
- Run the conversion.
- Validate before anyone is allowed in.
- Point each workstation at the new database and test printing from each one.
Phase 6: Validate
Run the same reports on the old and new systems, as of the same date, and compare:
- Trial balance
- Client trust listing, total and by matter for a sample
- Aged accounts receivable
- Work in progress listing
- Bank balances and reconciliation status
- Counts of clients, matters and vendors
- A sample of twenty matters checked line by line
Every figure must match exactly. A difference of a cent is a difference, and it needs to be explained before go-live.
Rollback
Decide the go or no-go point in advance. If validation is not clean by Sunday afternoon, the firm goes back to the untouched c-tree data on Monday and the conversion is rescheduled. Having that option is the reason you do not touch the original.
How long it takes
For most firms the cutover itself is a matter of hours, and fits in a weekend. The planning, clean-up and test migrations take two to six weeks, depending on the size of the database and how much repair the data needs.
After go-live
- Check each morning for the first week that backups and log backups completed.
- Keep the old c-tree data, read-only, until at least one month-end has been closed on the new system.
- Review performance after a month. Most tuning is done once real usage patterns are visible.
Common mistakes
- Converting data that has not been verified and reconciled.
- Skipping the test migration to save time.
- Under-sized server memory.
- No SQL backups configured on day one, because "we will sort that out next week".
- Forgetting templates and integrations until Monday morning.
Getting it done
Our PCLaw SQL Server service and PCLaw migration service cover the whole process, from assessment to post-migration support, with the work scheduled outside your business hours. Contact us for an assessment of your database.