4/14/2014

Teamcenter In-Depth Tips: How to Set Up a SQL Server Maintenance Plan (Part 2)

Without a basic database maintenance plan, your Teamcenter database can quietly grow into a bit of a monster – unwieldy, fragmented and slow.  This article is aimed at the typical IT person who wears many hats and doesn’t know a lot about Teamcenter, as well as for the CAD administrator/engineer who is trying to manage the company’s Teamcenter installation on-the-side while doing his “real job.”

This article shows steps you can use to automate the maintenance of your Teamcenter database on SQL Server so it stays the reliable workhorse we want it to be.

This article is the second of a two-part series.  The first part outlines step-by-step direction on setting up a minimal maintenance plan.

By the way, this post contains a LOT of information so grab a cup of coffee, put your feet up and get to reading.  Or, print it out for review at your leisure!

MORE MAINTENANCE TASKS? WHY???
Well, you could definitely just do the minimal tasks I outlined in Part 1.  However, that leaves out some very important steps that can make your job easier, and your database more manageable over time.   The maintenance plan tasks I outline below are modeled after Siemens and Microsoft’s “Best Practices Guide for Teamcenter on SQL Server”.

EDITING THE MAINTENANCE PLAN

As before, the first steps are to start SQL Server Management Studio, then log in to SQL Server as a database administrator.  (This is what gives you access to the SQL Server databases).

As shown in the image below, the next step is to expand the Management Group and the Maintenance Plans subgroup.  Right-click the existing maintenance plan and select Modify.

6959041_orig.png#asset:529

ADD STATISTIC AND INDEX TASKS

With the basic maintenance plan defined in part 1 , we can add the other tasks needed to keep your database healthy.

First on the list is to define an “Update Statistics Task”.  Siemens does recommend that Teamcenter databases be configured with “Auto Update Statistics” enabled, and I agree with this method.  However, I also like to set it so that the index statistics are updatedbefore doing maintenance on the indexes, which will come after this task.

Just like in Part 1, drag-and-drop the task onto the maintenance plan window and define its properties.  First things first, select the database.  In my example, I select “tctr”.

9679685_orig.png#asset:530

Then, select “Index Statistics Only”, and “Full Scan”.  These selections are shown in the image below, ready to click “OK”.

8808809_orig.png#asset:531

Now with the “Update Statistics Task” defined, select the previous task and drag its green arrow to the NEW task so they will execute sequentially.

Next, add a “Rebuild Index Task”.  This is not required, however it is an important task for the long-term health of the database.  In my example, I again select the “tctr” database.  The “Object” will be “Tables and Views”, and I use “Reorganize pages with the default amount of free space”.

6451088_orig.png#asset:532

Again, select the previous task and drag its green arrow to the new task.  Five of eight steps are now defined in the Teamcenter database maintenance plan, as shown below.  It’s over halfway done!

2067753_orig.png#asset:533

In a similar fashion, create a “Reorganize Index” task for the database on “Tables and Views”.  Be sure to select “Compact Large Objects” as it can significantly reduce the size of your Teamcenter database.  Then connect the tasks sequentially, resulting in the tasks below.

3925264_orig.png#asset:534

CLEAN UP AFTER YOURSELF

First, add two “Maintenance Cleanup” tasks to clear out old backup files.  The time frame chosen will depend on your company standards and what enterprise backup plan you have in place.  For my example, I rely on the enterprise backup plan to copy my backup files or off-site storage.  This means that I don’t need to retain those files for a long period of time.

In the example below, I have defined a Cleanup Task to delete my database backup files that are  more than 3 weeks old.  Having previously selected the option of placing the backups in a subfolder, I just select that subfolder in the Maintenance Cleanup Task form.  Please notice that I have selected the folder specifically, I did not enable the option to “include first-level subfolders“.  Doing it this way means that this cleanup task will not affect other backups or maintenance plans that may be defined on this server.

9813698_orig.png#asset:535

Then, create a similar Maintenance Cleanup Task to delete your TR log backup files that are more than 3 weeks old.  See below.

1478117_orig.png#asset:536

We’re so close!  The second-to-last step:  With the Cleanup tasks defined, connect those tasks sequentially to the previous ones to complete the Teamcenter maintenance plan, as shown below.

9249136_orig.png#asset:537

THE GRAND FINALE

We’re finally at the last step.  Save your changes and sit back and wait for the maintenance plan to run.  Or, run it manually by right-clicking on your chosen maintenance plan and selecting “Execute”.  I will typically run a new or modified maintenance plan once manually just to be sure all is working as intended.

9738460_orig.png#asset:538

CONCLUSION

And with that, you are done!  With the above steps you can automate the maintenance of your Teamcenter database on SQL server to perform the database maintenance tasks recommended in the Best Practices guide.  In my experience, this really does help to keep your installation healthy with very minimal effort.

Thanks for reading!

-Pat

Oh, and one more thing:  Make sure that you check your SQL Server and Maintenance Plan logs on a regular basis to make sure they have run successfully.  To do this, right-click your Maintenance Plan and select “History”.  There you will find logs of the maintenance execution tasks.  You can expand the logs to see the actual results of each task.

Patrick Kennedy

Pat brings years of real-world experience to his position at Acuity — and we would know. He departed our team to spend four years on the road, holding positions that furthered his skills in planning, implementing and administering Siemens engineering systems as well as competing products.

Teamcenter
Article