The article is divided into two parts, this first part gives you step-by-step instruction on setting up the absolute minimum maintenance.
By the way, this post contains a LOT of information so grab a cup of coffee, put your feet up and get to reading.
Teamcenter is a database application in the sense that information about the processes and the files it manages is stored in a database. Quite often smaller organizations use Microsoft SQL Server because it tends to be more familiar to smaller IT teams. It also integrates well with the system backup solutions found in many small-to-medium size organizations, and is relatively “low maintenance”. However, even on SQL Server, someoneneeds to perform basic database maintenance on the Teamcenter database. Thankfully, SQL Server provides tools to automate those tasks.
USING SQL SERVER MANAGEMENT STUDIO
There are different ways to setup automated maintenance plans in SQL Server. For people not trained in the T-SQL language, I think it’s easiest to use SQL Server Management Studio to create your maintenance plan. SQL Server Management Studio does have a Maintenance Plan Wizard to help get you started, but I usually don’t go that route. Instead, this article walks you through the steps individually.
Also, I set my maintenance plans to run automatically on a regular basis. To support this, make sure the Windows Service called “SQL Server Agent (MSSQLSERVER)” is set to Automatic startup and is running.
START A NEW MAINTENANCE PLAN
So the first steps are to start SQL Server Management Studio, and then login to SQL Server as a database administrator. This gives you access to your databases in SQL Server.
As shown in the image below, the next step is to expand the Management group, right-click on Maintenance Plans, and pick New Maintenance Plan…
DEFINE THE SCHEDULE
Since we want this maintenance plan to run automatically, a CRITICAL step is to schedule it. Click the calendar icon to open the schedule window. For an organization that has enterprise backup software taking nightly snapshots, I suggest having the Teamcenter database maintenance plan run weekly, on the weekend. In my example I’ve set up the default “Subplan_1″ to run every Sunday at 1:00 AM, as shown below.
With that done, now we need to define the tasks this maintenance plan will perform each weekend. SQL Server Management Studio makes it easy to define maintenance plan tasks using the Toolbox in the lower left corner of the interface. I just point at a needed task and drag it to the lower area of the maintenance plan screen. That adds an empty task to the plan. Then I double-click the new task to open up its properties. This is where we provide the details for that particular task.
The first task I always define in my Teamcenter database maintenance plan is backing up the database. Please note that I do this even when the organization has configured their enterprise backup system to perform a “live” or “hot” backup of the Teamcenter database. That’s because of the Transaction (TR) Log.
IMPORTANCE OF BACKING UP THE TR (TRANSACTION) LOG
A complete backup is actually a two-step process: Backup the database, then backup the TR Log.
In a default Teamcenter installation on SQL Server, the TR Log contains ALL database transactions that have occurred since the last backup of both the database and the TR log. When you don’t set up the database backups to fully back up both the database and the TR log, the TR log does not purge itself of old transactions. Think about that: new transactions keep going in but old ones are never purged so it doesn’t take too long for the TR log to grow much larger than the database itself!
DEFINE THE BACKUP TASKS
To set up a database backup, drag-and-drop a “Back Up Database Task” to the lower area of the maintenance plan screen, then double-click the task (or right-click->”Properties) to configure it. I typically choose to have it perform a Full backup and select the Teamcenter database (“tctr”) as shown below:
This time, the “Backup type” is set to “Transaction Log”, selecting the “tctr” database again.
Now we have defined two backup tasks, but we need them to run sequentially. That’s where those green arrows come into play. Click the first task for its arrow to appear, then drag that arrow to the second task. That defines the order in which they will be run. The image below shows the result of this action.
With the backup now defined, we can add other tasks to keep our Teamcenter database healthy. As described in the introduction, I’m focused on the bare minimum today, so my final maintenance task will be the “Check Database Integrity Task”.
As before, simply drag-and-drop the task onto the maintenance plan window and define its properties. In this case, select the database to check and click “ok”.
So, what’s left to do? Save it, of course! It will then appear under the Management/Maintenance Plans folder in SQL Server Management Studio. Now I can sit back and wait for it to run, or run it manually by right-clicking on the maintenance plan and selecting “Execute”. (I typically recommend running it once manually to test that all works correctly).
That’s it! With the above steps you can automate SQL Server to perform basic maintenance of your Teamcenter Database.
COMING UP IN PART II…
In Part II, I will expand upon this example to create a complete Teamcenter database maintenance plan in SQL Server Management Studio. I will also cover other tasks recommended by Microsoft and Siemens to keep your Teamcenter database at its best.