Time Matters Installation Checklist
This is tutorial / Time Matters 11 Installation Checklist on how to do your Time Matters upgrade right. 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, it 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 things 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 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 of 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 product key (which if you don’t have you can find in the tmconfig file in your setup folder. When opening 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).
If you are unsure of the instance your Time Matters Database is installed on or if you have multiple SQL servers I have instance 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.
*Note: All Scripts Below (in Blue) are meant to be run in SQL Server Management Studio.
Step 1. Getting Started
The first thing we’re 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 proceed 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’re 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 clean up 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 to 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 your 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 you’re 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.