How To Move a Time Matters and PCLaw Database to a New Server

This is an updated post to my previous entry regarding moving LNTPA Practice Advantage 9 to a new server.  This time I had to move Time Matters 11 from SBS 2003 server running SQL 2005 SP2 (Server A) to Server 2012 R2 running SQL 2008 R2 Express (Server B).  Time Matters specifically has to be installed as a blank database first to let the built in SQL security accounts be created, then you run a SQL query to reset the permissions after attaching the copied MDF / LDF files.  Here is how to do it.

Time Matters 11

1. Install SQL 2008 R2 Express with Tools and SQL Studio Manager on SERVER B (I used the same ‘sa’ password as before but different instance name).  SERVER B instance name noted:  SERVERSQL2008R2.  Run SQL Server Configuration Manager and make sure SQL Server and Server Browser are running and set to Automatic.  Also, drill down to SQL Server Network Configuration > Protocols for SERVERINSTANCE (SQL2008R2 in this case 😉 and set “Named Pipes” and TCP/IP to “Enabled”.  Now is a good time to Enable SQL Firewall Ports in Windows Server.  (TCP 445, TCP 1433, UDP 1434)
2.Stop SQL Service on Server A and copy MDF and LDF files to new server temp location.   (If you can’t do that, STOP NOW and contact me or a CIC.  I copied these to the desktop on SERVER B as an intermediate step, do not copy directly to MSSQLDATA yet!)
2b. Copy shared files directory to Server B and set share with full permissions to a security group.  Map share as the same drive letter as before and change Group Policy Object for Default Domain to update all client drive mappings.
3. Copy Time Matters setup file out of shared folder onto desktop (important that it is not in the same directory as the old adminctl.ini
4. Run ‘setupe.exe’ from desktop as a new Time Matters 11 installation.  This will create the default TM11 database as well as all security accounts and permissions needed.
5. After setupe.exe has completed, say no to setting up new accounts.  Also, cancel out of product activation when it asks if this is the primary database or remote database.
6.  Run SQL Management Studio 2008 R2  on SERVER B.   Drill down SERVERSQL2008R2DatabasesTimeMatters11 > Right Click > Tasks > Detach
7. Copy TimeMatters11.mdf and TimeMatters11_log.ldf from desktop to MSSQLDATA directory in Program Files overwriting the default database files: C:Program FilesMicrosoft SQL ServerMSSQL10_50.SQL2008R2MSSQLDATATimeMatters11.mdf  and TimeMatters11_log.ldf
8.  Back to SQL Management Studio 2008 R2 on SERVER B.  Right click “Databases” and “Attach”.  “Add” your newly copied MDF file (LDF should automatically select with it)
9. Here is the trick Lexis Nexis makes you pay for AMP to see.  SQL Management Studio > New Query:

use [TimeMatters11]
declare @DBname as varchar(500) set @DBname = ‘TimeMatters11’
declare @DBowner as varchar (500) set @DBowner = ‘lntmu11’
exec (‘sp_defaultdb [‘+@DBowner+’], [‘+@DBname+’]’)
exec (‘sp_change_users_login @action=update_one, @usernamepattern=[‘+@DBowner+’], @loginname=[‘+@DBowner+’]’)

MAKE SURE TO MAKE ALL OF THE SINGLE QUOTES AS REGULAR SINGLE QUOTE TICKS.   NOT LEFT OR RIGHT QUOTES. Something about WordPress keeps formatting this funny.  Anyway… Then hit “!Execute”.  That should restore permissions to the lntmu11 security account in SQL.
10. A little bit of clean up.  Go to your TM share folder \SERVERSHARETMSETUP and edit adminctl.ini to reflect your new server SQL and DB_Name.
11.  Same folder – edit tmconfig.ini to reflect new server shared “FILES_DIR”
12.  On client workstations: run “Time Matters 11.1 File Locations” as administrator (Start > search “File Locations” and right click > run as admin).  Change “Main (Default)” to reflect new Shared Files Directory (SQL Server Name and Database should autofill after that).   Click OK and yes to prompt warning it will affect all users.  Tell it to “Cancel” coping shared files from old server location (because you already did this in step 2b).
13.  Optional: depending on UAC settings, you may need to run Time Matters 11 as administrator.  Just right click > Properties > Compatibility > Run as Administrator
14. Grab a beer.
 
Thank you to Eric for the following information regarding TM14:

use [TimeMatters] declare @DBname as varchar(500) set @DBname = ‘TimeMatters’
declare @DBowner as varchar (500) set @DBowner = ‘lntmuid’
exec (‘sp_defaultdb [‘+@DBowner+’], [‘+@DBname+’]’)
exec (‘sp_change_users_login @action=update_one, @usernamepattern=[‘+@DBowner+’], @loginname=[‘+@DBowner+’]’)

PCLaw 12

1.  So, SQL 2008 R2 Express and the SQL2008R2 instance is already installed.  Copy our MDF and LDF files from the old server to the new server.  (This time it is ok to copy directly into your MSSQLDATA folder)
2.  Attach the MDF file in SQL Studio.
3.  SQL Studio > SERVERINSTANCE > Security > Logins (right click ) > New Login
4.  Login Name:  PCLAWUSER_12345 (your Serial#)
5. Check “SQL Server Authentication”  and set password to your “ADMIN” PCLaw user password, uncheck “Enforce Password Policy” uncheck “Enforce expiration” uncheck “must change password”.   Change ‘Default database”  to PCLAWDB_12345.
6.  User Mapping > users mapped to this login > check PCLAWDB_12345 and add roles “db_owner” and “public”.  Click OK
8. Install PCLaw on server from shared folder and attach to existing dataset.  You should be able to open PCLaw from the server at this point.  NOTE: You may need to edit C:Program Files (x86)LexisNexisPCLawLOCALDATAPCLaw.CFG to point at the updated “BOOKPATHDATA”.  This is only necessary on machines using UNC naming and not mapped drive letters.  Copy PCLaw.CFG to same location on client workstations as needed.
9.  ALLOW Windows Firewall Port:  TCP 51***   (Domain only) for PCLaw login from client workstations.  I had to view the firewall monitor log to find which port was being used.  It appears to be randomly selected upon installation, but it keeps using the same port.
10.  Update GENINFO.32 in the DYNDATA.  Unfortunately, this is impossible without an AMP agreement with Lexis Nexis as it can only be edited from the Tech Module area of PCLaw and requires a challenge password from PCLaw engineering support.
11.  Now time to update to version 14…