Time Matters 11 Installation Checklist | 2011

What many an unprepared IT Consultant, Lawyer, or Paralegal has failed to realize before attempting to do a Time Matters Upgrade is that it is not really an upgrade. Rather, it is a re-import. You are not just clicking update, going to Starbucks and coming back, sorry doesn’t work like that.  If you have a very small database, say under 4gb you might get lucky but anything bigger than that and you want to take some extra steps to make the process as painless as possible.  The bird’s eye overview is that:

1. Installing a new instance of the Time Matters application and an empty Time Matters database.
2. Pointing this empty Time Matters instance at your existing Time Matters database (data-source).
3. Click Import. Hope for the best.

There are many thing that can go wrong in this equation, and while Lexis offers a broad set of guidelines in their Time Matters Installation Guide (Do read and/or keep this handy if you’re attempting this for the first time as it has lots of handy screen shots), it in my opinion does not cover all the bases. Personally I like to go a little more in depth before moving gigabytes of data between databases and / or servers.   This article is is intended as a complement for the Lexis Installation Guide.

Please note that steps outlined in this article are intended for advanced users and we make no guarantees as to the success that you will have in employing them, if you have any doubt about your ability to perform the steps in this article contact a professional.

What you will need

1. Microsoft SQL Server 2008 Management Studio. Microsoft SQL Server Management studio (SSMS) allows you to easily perform database maintenance on your server. Here is a Microsoft SQL Server 2008 Management Studio Installation Guide as this can be a little tricky to get installed (Go figure). It is also a great idea to have on your server in case you need help from tech support or someone like me as it cuts down the amount time it will take to get things underway.

2. Access to your SQL Instance. If you installed Time Matters standalone SQL your server name should be .\SQLEXPRESS your user is always SA and your password should be your poduct key (which if you don’t have you can find in the tmconfig file in your setup folder.  When open SSMS you will get this login screen and you can try the standard credentials or if you have installed SQL on its own your system administrator should have the needed credentials (hopefully).

SQL Login Screen

If you are unsure of the instance your Time Matters Database is installed on or if you have multiple SQL servers I have intance checker application that you can download, this will analyze all running SQL servers and list them in a console window for you.  Simply unzip the file and double click the executable file.  Note that this requires .NET framework 3.5 or higher.

Download SQL Instance Checker

*Note: All Scripts Below (in Blue) are meant to be run in SQL Server Managment Studio.

Step 1. Getting Started

The first thing were going to do is test the continuity of the existing Time Matters Database. Log-in to your SQL Server Management Studio with right click on your Time Matters 10 Database and click new query, paste in the following code, and click execute (or hit F5 on your keyboard).

use TimeMatters10
GO
DBCC CHECKDB
GO

If you have a large database this command can take several minutes to execute. Essentially what we are doing here is checking the database for continuity issues or outright corruption. If the output (which you will see as several pages of text in the output window, upon completion) has any red text you have a issue that you need to address, contact a professional or technical support. If you see no red text or error messages you are clear to precede to the next step.

Step 2. Create Backup as a Reference

The first thing you should do is create a backup, this is always a good idea and if at any point you find yourself with a non-functional install you can always revert to this backup, if you use imaging software on your server (which is another good idea), this would be a great time to catch an image.

You can Backup from within Time Matters but I prefer SQL Backup Script

BACKUP DATABASE [TimeMatters10] TO DISK = 'M:\SQLBACKUP\TMSQL_PREMOVE.bak'
WITH INIT, STATS = 10, NOFORMAT

 
Step 3. Run Time Matters Database Maintenance

Run the Time Matters Database Maintenance on your existing instance, this will run several stored procedures that should help cleanup any issues with bad relationship in your lntmuser.relatea and lntmuser.relates tables. Here is a screen shot of what I check off:

Step 4. Baseline

One of the things that continues to amaze me when I see people do critical data migrations or high priority maintenance, is the lack of any type of base-lining. People do a massive import, have someone look at it see if it looks right, find some wood to knock on, and then they act surprised months later when someone discovers missing data. Whenever you move data, files, or perform mission critical updates you should perform base-lining to make sure that you have 100% of the data in the new instance.

USE TimeMatters10
GO
SELECT OBJECT_NAME(object_id) AS [ObjectName],
SUM(Rows) AS [RowCount]
FROM sys.partitions
WHERE index_id < 2
and OBJECT_NAME(object_id) not like 'Audit%'
and OBJECT_NAME(object_id) not like 'tmpsrt%'
AND OBJECT_NAME(object_id) NOT LIKE 'sys%'
AND OBJECT_NAME(object_id) NOT LIKE 'queue_%'
AND OBJECT_NAME(object_id) NOT LIKE 'filestream_tombstone%'
GROUP BY object_id
ORDER BY SUM(Rows) DESC
GO

 

You don’t have to know exactly what this means just paste it into your SSMS and execute (or F5). This script will allow you to get a count of how many records are in each table in your Time Matters Database, here is an example:

Save this result set to excel and put it somewhere safe, this will be your point of reference after you imported into Time Matters 11. The reason we are doing this is that in our Time Matters 10 Database we have 54,099 records (or matters) when we do the import into Time Matters 11 we better have 54,099 matters. If this is not the case we have a problem, and we need to address it before we can put the database into a production environment.

Step 5. Rebuild Indexes

Without getting into a technical explanation of INDEX structure and page fragmentation I will tell you that Time Matters Key Setup (sysid) leads to significant index fragmentation. The following script will rebuild all the indexes in the Time Matters Database, this will rebuild the physical leaf level of the I/O locations on disk lowering the fragmentation as much as possible. The lower the fragmentation in the indexes in the databases the faster our import to Time Matters 11 Database will occur.

Here is the script: (this is an undocumented stored procedure in SQL server 2005 and higher to parse the tables in a database).

use TimeMatters10
GO
sp_MSforeachtable @command1="print '?' DBCC DBREINDEX ('?')"
GO

Step 6. Do a Final Check for Database Integrity

Repeat the DBCC CHECKDB (Check Database) Command to make sure that everything is good to go. Again if you see anything in Red you’ve got a problem that must be resolved before you attempt the import.

use TimeMatters10
GO
DBCC CHECKDB
GO

Step 7. Truncate the Log File

Because we have just done a ton of maintenance we have also generated a ton of corresponding log file which we don’t need for our import. This script will decrease the size of the Log File.

USE TimeMatters10
GO
ALTER DATABASE TimeMatters10
SET RECOVERY SIMPLE;
GO
DBCC SHRINKFILE (TimeMatters10_Log, 1);
GOALTER DATABASE TimeMatters10 

SET RECOVERY FULL;
GO

Step 8. One More Backup

Using the script above catch one more backup (note: don’t overwrite the original one).

Step 9. Install Time Matters 11

From here on out the Time Matters Installation Guide does a decent job of getting you up. You are going to install Time Matters 11 on the server create a new database open the program and point the importer at you Time Matters 9 / 10 instance.

This starts at page 42 in the PDF install guide. After you have your new instance up you can can install Time Matters on the users workstations and your good to go.

If you have questions about upgrading to Time Matters 11 or are looking for a qualified consultant to ensure your data makes it from point a to point b safe and sound contact us today.

David Wetherell
NYC-Software Solutions
consult@nyc-software.com