Avid® Interplay™ SQL Sync Installation and Administration Guide m a k e m a n a g e m ove | m e d i a ™ Avid ®
Legal Notices Product specifications are subject to change without notice and do not represent a commitment on the part of Avid Technology, Inc. The software described in this document is furnished under a license agreement. You can obtain a copy of that license by visiting Avid's Web site at www.avid.com. The terms of that license are also available in the product in the same directory as the software.
Permission to use, copy, modify, and distribute this software and its documentation for any purpose and without fee is hereby granted, provided that the above copyright notice appear in all copies and that both that copyright notice and this permission notice appear in supporting documentation. This software is provided "as is" without express or implied warranty. Copyright 1996 Daniel Dardailler.
This product includes software developed by the Apache Software Foundation (http://www.apache.org/). © DevelopMentor This product may include the JCifs library, for which the following notice applies: JCifs © Copyright 2004, The JCIFS Project, is licensed under LGPL (http://jcifs.samba.org/). See the LGPL.txt file in the Third Party Software directory on the installation CD. Avid Interplay contains components licensed from LavanTech.
Contents Using This Guide . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 7 Symbols and Conventions . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 7 If You Need Help. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 8 Viewing User Documentation on the Interplay Portal . . . . . . . . . . . . . . . . . . . . . . . . . 9 Accessing the Online Library . . . . . . . . . . .
wg_exportinfo . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 33 wg_mimetypes . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 34 wg_objects . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 34 wg_properties. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .
Using This Guide Congratulations on the purchase of your Avid® Interplay™, a powerful system for managing media in a shared storage environment. This guide is intended for all Avid Interplay administrators who want to export the contents of their Avid Interplay databases to Microsoft® SQL Server™ databases. n The documentation describes the features and hardware of all models. Therefore, your system might not contain certain features and hardware that are covered in the documentation.
Symbol or Convention Meaning or Action Bold font Bold font is primarily used in task instructions to identify user interface items and keyboard sequences. Italic font Italic font is used to emphasize certain words and to indicate variables. Courier Bold font Courier Bold font identifies text that you type. Ctrl+key or mouse action Press and hold the first key while you press the last key or perform the mouse action. For example, Command+Option+C or Ctrl+drag.
Viewing User Documentation on the Interplay Portal Viewing User Documentation on the Interplay Portal You can quickly access the Interplay user documentation from any system in the Interplay environment. Type the following line in your Web browser: http://Interplay_Engine_name where Interplay_Engine_name is the name of the computer running the Interplay Engine software.
Avid Training Services Avid makes lifelong learning, career advancement, and personal development easy and convenient. Avid understands that the knowledge you need to differentiate yourself is always changing, and Avid continually updates course content and offers new training delivery methods that accommodate your pressured and competitive work environment. To learn about Avid's new online learning environment, Avid Learning Excellerator™ (ALEX), visit http://learn.avid.com.
1 Overview of the SQL Syncer Tool The Avid Interplay Engine provides extremely fast storage and access to object hierarchies and meta-information attached to the current version of each object. A fully-integrated fast searching implementation provides efficient access to subsets of the stored data. What the Avid Interplay Engine does not provide is a standard searching interface that can be used by standard tools like Crystal Reports.
1 Overview of the SQL Syncer Tool Limitations The SQL synchronization architecture does not guarantee or imply that the Avid Interplay Engine and the external database are 100 percent in-sync all the time, as the SQL database is updated from the Interplay Engine backup. The SQL Syncer is an optional component which can be used to facilitate complex search queries on the data stored in an Avid Interplay Engine.
2 Installing the SQL Syncer Tool This chapter contain the following topics: • SQL Syncer Setup Requirements • Installing SQL Syncer • Running WorkgroupSQLSetup.exe on the Interplay Server (Optional) • First Export to SQL SQL Syncer Setup Requirements The following sections describe the hardware and software requirements for a successful installation of the SQL Syncer.
2 Installing the SQL Syncer Tool Machine Requirements/Limitations Due to capacity requirements, Avid strongly recommends purchasing and using an unrestricted SQL Server license rather than use a restricted-size free version. If a restricted-size version suits your needs, you can download the free SQL Server 2005 Express Edition from here: http://www.microsoft.com/sql/editions/express/default.mspx.
Installing SQL Syncer 5. Read the text carefully and select “I accept the agreement.” 6. Click Next. The Specify Destination Location dialog box opens. 7. Enter the folder in which you want to install the SQL Syncer. 8. Click Next. The Specify Interplay Backup Path dialog box opens.
2 Installing the SQL Syncer Tool 9. Type the path to the backup of the Avid Interplay database you want to sync to the SQL database. 10. Click Next. The Server Execution User Dialog Box opens. 11. Select Custom user account and click Next. You are then be prompted to enter the custom User Name and Password, and to confirm the password. c Make sure to select Custom user account. This account must be the same account that runs the Avid Interplay Engine (the Server Execution User).
Installing SQL Syncer 13. Type the name of the SQL server host name that should be used by the SQL Syncer. Do one of the following: t For a two-machine setup: leave the default (the current machine name) t For a three-machine setup: change the SQL server name to the host name or IP address of the SQL server computer. The Specify SQL Server Settings dialog box opens.
2 Installing the SQL Syncer Tool 14. Specify the SQL Server instance name (if applicable), the Administrator user name and the Administrator password. 15. Click Next. The Ready to Install dialog box opens. 16. Click Finish. 17. A reboot may be required if the database needs to be installed. After a successful installation, the Installation Completed dialog box opens. 18. Click Finish. Running WorkgroupSQLSetup.
First Export to SQL If the functionality is already configured to be on, the Efficient SQL Updates Already dialog box opens. It allows you to reconfigure this setting. a. b. Do one of the following: - Select “Keep Avid Interplay Engine configured for efficient SQL updates” (default) - Select “Remove efficient SQL update tables from the Avid Interplay Engine” Click Next. The Ready to Install dialog box opens. 3. Click Finish. The Setup Completed dialog informs you of the successful installation.
2 Installing the SQL Syncer Tool 20
3 Avid Interplay Database Schema, Syncing and Querying This chapter contain the following explanations: • Avid Interplay Database Schema • Progress and Success Log • Scheduled Synchronization • Efficient Update Ability • SQL Queries • Tutorial on View Creation • Reports Avid Interplay Database Schema The Avid Interplay database schema provides a flexible, efficient, and self-contained implementation that mirrors essential data from the Avid Interplay database.
3 Avid Interplay Database Schema, Syncing and Querying minimal number of tables and rows in the database are affected. Self-containment means that all information for finding out where the requested data is stored is kept inside the same database. Overview There is one table which stores basic information about all objects in the Avid Interplay database. The basic information includes the object's handles and information about the position of the object in the hierarchical object tree.
Avid Interplay Database Schema See “Schema Table Descriptions” on page 31 for complete descriptions of the tables.
3 Avid Interplay Database Schema, Syncing and Querying Progress and Success Log The progress and success of the SQL Syncer is written to the DbToSQL.log file located in C:\Program Files\Avid\Avid Interplay SQL Syncer\Bin. Scheduled Synchronization The installer schedules the SQL Syncer to run daily at 5:00 am. This can be changed in the configuration file found in C:\Program Files\Avid\Avid Interplay SQL Syncer\Bin\DbtoSQL.xml. See “SQL Syncer Configuration” on page 27 for details.
SQL Queries SQL Queries After exporting the Interplay Database to the SQL database, you can view the exported contents and run queries. Tutorial on View Creation The following tutorial shows how you can modify the schema by working with views defined on top of the tables provided. 1. Add the property value NxNServer_AuthOwner as a column on wg_objects. First determine the propertyhandle for NxNServer_AuthOwner.
3 Avid Interplay Database Schema, Syncing and Querying 2. Test a SQL query that does this: select vo.*,vp.stringvalue AuthOwner from wg_objects vo, wg_properties vp where vo.bdh=vp.bdh and vp.propertyhandle=238 Output: bdh parent_bdh name 4 ‘NxNServer_PropStorageType' ... 3 AuthOwner 'Administrator' ... 3. Create that as a view. create view vobjects_wauthowner as select vo.*,vp.stringvalue AuthOwner from wg_objects vo, wg_properties vp where vo.bdh=vp.bdh and vp.propertyhandle=238 4.
4 SQL Syncer Configuration and Troubleshooting This section contains the following topics: • SQL Syncer Configuration • SQL Syncer Troubleshooting SQL Syncer Configuration The SQL Syncer is configured through an XML configuration file, DbToSQL.xml, which can be found in the installation directory, for example: C:\Program Files\Avid\Avid Interplay SQL Syncer\Bin\DbToSQL.
4 SQL Syncer Configuration and Troubleshooting The following is an example of a configuration file: The following tables describe the configuration file nodes and their attributes: Node Descriptions 28 Node Name Parent Node Description config NONE Top level node of the c
SQL Syncer Configuration Database Node Attribute Descriptions Attribute Name Default Value Description server None, must be set Specify the SQL Server that you are exporting to here. For SQL Server, if you have a named instance, the syntax is \ type None, must be set Type must be "MSSQLServer". username None, must be set The username to use in connecting to the SQL database. password None, must be set The password to use in connecting to the SQL database.
4 SQL Syncer Configuration and Troubleshooting Workgroup Node Attribute Descriptions (Continued) Attribute Name Default Value Description schedule None, must be set This is in the format "-HH:MM" where HH:MM is a time in 24-hour time format when the backup should happen. It can also be in the format "*HH:MM" where HH:MM is a time interval describing how often an export should happen. For example, "*01:00" means an export should be performed every hour.
A Schema Table Descriptions This chapter contains the following Avid Interplay database schema tables: • wg_anonymous_properties • wg_dependencies • wg_dependency_types • wg_dependency_usages • wg_exportinfo • wg_mimetypes • wg_objects • wg_properties • wg_propertyinfo • wg_propertytypes wg_anonymous_properties One row for each anonymous property assigned to an object. Column Name Type Description bdh Bigint The BDH for the object with the anonymous property.
wg_dependencies One row for each dependency relationship between two objects (designated "from" and "to" objects). Column Name Type Description from_bdh bigint The BDH for the "from" object. type bigint The type of the dependency, viewable with a lookup in wg_dependency_types. usageid bigint Usageid maps to a string associated with the dependency. to_bdh bigint The BDH for the "to" object. object_bdh If an object is associated with the dependency link, it's BDH handle is specified here.
wg_dependency_usages wg_dependency_usages Column Name Type Description usageid bigint The dependency usageid. value ntext The associated string. wg_exportinfo There is one row for each attempt to export the Interplay database to SQL. was_successful indicates success. Column Name Type Description exporttime datetime The time of the last export attempt. updatetime datetime The last time the Interplay Engine database was modified. was_successful tinyint 1 if the attempt was successful.
wg_mimetypes Column Name Type Description bdh bigint The BDH for the Mime Type object. name ntext The name of the mime type. path ntext The path for the mime type in a tree of mime types organized in broad categories. ext ntext The file extention string for files associated with this mime type. wg_objects Each row is a Workgroup Object. 34 Column Name Type Description bdh bigint The canonical object identifier.
wg_properties Column Name Type Description isfolder tinyint True if the object is a folder. ischeckedout tinyint True if the object is checked out. issystem issystem True if the object is a Workgroup System object. smot datetime Server File Modification Time simt datetime Server File Import Time scit datetime Server Check In Time scrt datetime Server File Creation Time wg_properties Each row represents a property on an object.
wg_propertyinfo For each unique propertyhandle, there is a row giving more details about the property type. Column Name Type Description propertyhandle bigint The unique handle for each property. name ntext The property name. propertytype bigint References the property type (see table wg_propertytypes). wg_propertytypes A row is given for each unique property type in the system. 36 Column Name Type Description propertyname bigint The ID for the property type.
ABCDEFGHIJKLMNOPQRSTUVWXYZ Index C L Configuring SQL Syncer 24 Crystal Reports from SQL Syncer 26 Logging SQL Syncer 24 D Reporting from SQL Syncer 26 DbToSql.
Index ABCDEFGHIJKLMNOPQRSTUVWXYZ SQL synchronization from Interplay backup 11 sqlconfig.xml configuration 27, 27 Synchronization from Interplay backup 11 T Troubleshooting SQL Syncer 30 V View creation SQL Syncer 25 W WorkgroupSQLSetup.exe running 18 WorkgroupSQLSyncer.
