Enterprise versions of Time Matters from version 3 through version 9 used a user assigned login and password. For large sites, this was assigned and controlled by a system administrator or even a DBA. This login and password was considered secure and was not generally shared with anyone without a “need to know”. Time Matters Version 10 has deviated from this legacy methodology.

In an effort to simplify the migration of Professional end-users to the use of SQL Server the latest version of Time Matters restricts access to the login and password information used to create the new Time Matters database.

This has caused somewhat of a ripple effect for those of us supporting and developing add-ons for Time Matters Enterprise. We at Data Equity have just completed upgrading our third party products to support this change. As a result we have put together the following information if you are facing similar tasks. You may also find it useful when providing information to your client’s IT staff.

There are two ways to access Time Matters data without knowing the Time Matters database Owner login.  One method would be to have the end user add another SQL Server login with access to the Time Matters database tables. This defeats the point originally intended (simplicity) and creates even greater confusion in the long run. Data Equity has decided instead to use Windows “Trusted” Authentication. This technique allows us to maintain a level of simplicity for our customers.

Understanding Windows Authentication

Windows Authentication connects to SQL Server through a Microsoft Windows user account. SQL Server will validate the account name and password using information in the Windows operating system. This method allows the user’s identity to be confirmed by Windows. The server does not ask for the password, and does not perform the login/identity validation. The credentials are managed and provided by the underlying operating system.

This methodology provides a great deal of flexibility that can be managed from the Windows Security Individual or Group level. If the desired Windows user does not have access simply add them to the SQL Server Security section by group or Windows user. In SQL Server Express, Windows users are granted connect permission because the BUILTIN\Users group is already provided as a login.

The Impact On Your SQL Scripts

If you write your SQL scripts explicitly using the table owner, you are accustomed to changing the table names when the owner of the table is different from the logged in user.

                SELECT * FROM tm9user.contact

Historically, the best way to keep your SQL scripts abstract is to implicitly use the current login as the table owner.

                SELECT * FROM contact

Notice I don’t need to use the table owner in my query. Unfortunately, this method requires you to login as the table or object owner (the way TM V3 – V9 worked). We no longer have that option in V10. You will need to modify all SQL statements (HotDocs, Crystal Reports, etc) to use the newly “hard-coded” table owner lntmuser.

                SELECT * FROM lntmuser.contact  

The technology press has been covering the move to cloud technology for quite some time. Most technology pundits are complicit to the fact that we will eventually find a significant percentage of our technology funding being applied to cloud based technologies. We have already seen a major move to hosted backups and online services such as hosted Exchange. In fact the newest product offering of Business Assurance for Time Matters (ba4tm.com) provides fixed priced services for hosted Exchange synchronization setup and configuration.

Microsoft's "next big thing" SQL Azure, has been slowly making it's way to wide release. SQL Azure is currently in Community Technology Preview (CTL) and scheduled to be commercially available in November 2009. In general, SQL Azure is hosted version SQL Server. In theory, we should be able to point Time Matters to our database on SQL Azure instead of purchasing and deploying within the client organization. Pricing will be extremely enticing but will Time Matters be compatible? Data Equity will be testing Time Matters specifically for SQL Azure compatibility. Bandwidth usage and changes to data access methodology in V10 are of potential concern.

Advantages of a cloud server are significant; high availability, scalability, centralized backup and management, zero maintenance and remote access. Client workstations no longer need to perform Time Matters based synchronization and distant departments no longer require SQL replication. Add to that the ability to connect to the database from a client's site or the coffee shop and we have an early cloud alternative. Time Matters clients migrating from Professional to the SQL requirement for version 10 will also benefit from simplified deployment management of a hosted solution.  

Watch the blog for more results from our testing.

This tip applies to virtually every list in Time Matters and can be implemented with ease but only applies to Time Matters installations running on SQL Server. If your users tend to click on column headings a lot or use a custom sort order for their quick tabs, this tip can help immensely.

With SQL Server we have the ability to add custom indexes without disturbing Time Matters in an adverse way. In fact, we can significantly improve the performance of the primary lists. Take for example a matter QuickTab that is designed to sort by a custom field. Every time this QuickTab is selected, SQL Server must build an on-demand custom sort index based on the specific field. By default, SQL does this much faster than professional but the performance can be significantly improved if we add a custom index for it.

Time Matters makes this very easy to do, no SQL required! From the program level settings for lists, select Matter, then the Sort tab. 

Upon completion of this screen, the following index will be created in SQL Server.

As you see, Time Matters has automatically added the archive column to the top of the index. All lists in Time Matters have a hidden filter for the archive flag, thus creating indexes with archive first allows for an optimal SQL query.

You do not have to rely on Time Matters to create these indexes. You can in fact use the interface in SQL Server to create additional indexes. SQL Server is optimized to automatically select the best index based on the current filter and sort order. 

It's important to note that adding too many indexes will increase the overhead required to update and insert new records into the database.