DMS - The DB Size problem and Planning Ahead

I am writing because I hope to help anyone new starting with DMS (or with only a year or two into it) to avoid some of the problems we've run into.

DMS has a size limitation - once the database (DB) exceeds 10 GB it becomes unstable and can easily corrupt. Last year our DB - with 7000-odd clients, 4 years of returns, and a size of around 40 GB - got corrupted. As our backups had also corrupted (since we had no idea the DB was going bad), it took us five days just to get the DB rebuilt and running. The advice from Lacerte to prevent this in the future was to break it up into smaller DBs of no more than 10 GB each.

The basic problem with multiple databases is that the only way you can make sure that the tax return you are printing to DMS from Lacerte goes to the right database is to have that database open while printing it. You CANNOT point to a specific DMS database in the Lacerte tax options. And you cannot create a desktop shortcut that both starts DMS and opens a particular DB - you have to choose the right DB from the dropdown menu in DMS. And, to make matters worse, the only place where you can tell which DB you have open is at the bottom of the DMS window where the DB path shows up in tiny little letters. (There are lots of older preparers who aren't very computer savvy and have eye problems in our office, so these are real issues for us). I have sent in Feedback asking Lacerte to address these issues to help those of us who have to go the multiple DB route - hopefully they will listen.

Breaking up the DB can go one of two ways - by Alphabet or by Year.

The biggest plus about Alphabet is that it allows you to keep all your client's info across tax years in one spot (especially the issues). And if you go this route, it's easier to break up an existing DB. But your preparers will have to stay on their toes making sure they have chosen to open the correct Alphabet DB when they print their tax returns to DMS (i.e, E-F, not S-Z). And the real negative is that, as Lacerte does not appears to be interested in dealing with the DB size problem, somewhere down the line you'll end up with a separate DB for each letter of the alphabet.

Yearly has a singular practical advantage in that because 85-odd% of your preparer's returns in any given year will be only for that year, they will rarely have to switch DBs in DMS. And once a tax year is finished, you'll know that year's DB size will not grow much larger in the ensuing years. The negative side is that your issues tab will be essentially obsolete, if you use it to pass on general client information (like "crazy taxi driver who will try to make you tell him how much income he should report") - unless you remember to copy the note to each of the DBs. And the absolute biggest negative is that to break up an existing DB into yearly ones is a nightmare on wheels.

I'm going to briefly describe what you have to do to break up your DB into either grouping so you don't have the same problems as me. Oh, and one more very important thing you need to know - deleting files from a database WILL NOT reduce the size of the database; you have to delete and then export the remaining files to a new DB to get a smaller size.

ALPHABET.

Create a new DB and give it an easy-to-remember name (DMS A-D). Open your old DB and make sure any issues that you want to show up in the new database are unchecked or they won't copy (yes, you have to go through the entire DB client by client to do this). Choose File - Export and select Export to an existing DMS database. For your destination, choose the new DB location. For the clients - and this is important - first sort the list that shows up by Client (click on the word "Client" at the top of the list - this is essential if you mix all Individual, Corporate, etc. clients in one DB). Then click on the first client of your alphabet group to highlight it, scroll down to the last person, hold down your shift key and click on that person to select that entire group. Now let it run. For your reference, it took me 8 hours to send 1400 clients (A - D) with five years of tax information over the the new DB. And then, unfortunately, I found out that I had a 13.8 GB database. So, do one letter at a time and check the size of your DB as you do it. :) Repeat until you have all the DBs you want.

YEARLY

This one gets UGLY if you have a large database. You might just want to do want we're considering (if the boss chooses this route) and just leave the DB as is and from this point on, create a new one each year with just that year. But if your DB isn't the nightmare ours is, here's what to do. Let's pretend we're starting by making a 2007 DB. Make a copy of your existing DB - VERY IMPORTANT - let's call it DMS-COPY. Go in that copy and client by client, delete any tax year folders that aren't TY07. Also delete any clients that have empty TY 07 folders. (It took us 7.5 hours to get through 1400 clients doing this - and you're going to have to do it again and again for each tax year DB you create). Now even though you think you have a 2007 DB, you don't. Thanks to DMS not shrinking in size when you delete files, you are now going to have to create a new DMS 2007 DB and export all those clients into it (don't forget to uncheck the issues items - if that matters). When you export, just select All Clients. After 8 or so hours, you will have a DMS 2007 DB that is much smaller than your original DB. Now go delete the DMS-COPY DB (which is useless now) and create another copy of the original DB. Repeat the preceding steps for each tax year you are going to create from the old DB.

As you can see, if you allow your DB to grow beyond the 10 GB size and then decide to break it up, you will have a lot of work on your hands. It is far better to start planning for growth in the first two years of tax returns and implementing those changes than to wait as long as we did. In our defense, no one warned us the DB would become so unstable.

We are still debating across our office which method to use. We'll probably go with Alphabet because we like having all the client info in one spot (which was the whole point of DMS in the first place) and it will save us on the heavy cost of coverting to Yearly. But there is no doubt that yearly would make the day-to-day operation go faster and that, in the long run, it would be easier to manage new years. So, what you do is up to you. Just, please, if this is a program you're planning on using for awhile be sure to give some serious thought as to how much you expect your DB to grow and how you will handle it - I wouldn't wish this situation on my worst enemy! LOL! :)

Good luck!