Avid® Interplay SQL Sync ™ Installation & Administration Guide m a k e m a n a g e m ove | m e d i a ™ AVID CONFIDENTIAL DRAFT Avid ®
Copyright and Disclaimer 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.
The following disclaimer is required by Altura Software, Inc. for the use of its Mac2Win software and Sample Source Code: ©1993–1998 Altura Software, Inc. The following disclaimer is required by Ultimatte Corporation: Certain real-time compositing capabilities are provided under a license of such technology from Ultimatte Corporation and are subject to copyright protection. The following disclaimer is required by 3Prong.com Inc.
Book Title Here • 0130-XXXXX-01 • July 2004 4 AVID CONFIDENTIAL DRAFT VERSION 5/24/06
Contents Using This Guide . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 3 Symbols and Conventions . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 4 If You Need Help. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 5 Related Information. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .
Efficient Update Ability . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 22 The SQL Queries . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 22 Tutorial on View Creation . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 23 Reports. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .
Using This Guide Congratulations on your purchase of 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 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.
Using This Guide Symbols and Conventions Avid documentation uses the following symbols and conventions: Symbol or Convention Meaning or Action n A note provides important related information, reminders, recommendations, and strong suggestions. c A caution means that a specific action you take could cause harm to your computer or cause you to lose data. w A warning describes an action that could cause you physical harm.
If You Need Help If You Need Help If you are having trouble using Avid Interplay: 1. Retry the action, carefully following the instructions given for that task in this guide. It is especially important to check each step of your workflow. 2. Check for the latest information that might have become available after the documentation was published in one of two locations: n - If release notes are available, they ship with your application.
Using This Guide Accessing the [Tutorial and] Online Library The [Product Name] [Tutorial and] Online Library CD-ROM contains [a multimedia tutorial and] all the product documentation in PDF format. [Avid recommends the multimedia tutorial as your first resource for learning how to use your application.] You can access [the tutorial and] the library from the [Tutorial and] Online Library CD-ROM or from the Help menu. n You will need Adobe® Acrobat® Reader® installed to view the documentation online.
Avid Training Services For information on courses/schedules, training centers, certifications, courseware, and books, please visit www.avid.com/training or call Avid Sales at 800-949-AVID (800-9492843).
Using This Guide 8 AVID CONFIDENTIAL DRAFT VERSION 5/24/06
Introduction Chapter 1 SQL Sync Introduction This chapter contains: • Introduction • Architecture Overview • Limitations Introduction 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.
Chapter 1 SQL Sync Introduction After successfully establishing a connection, SQL Sync waits for new events found in the Avid Interplay Engine’s backup to update them in the corresponding SQL database. Limitations The SQL synchronization architecture does not guarantee or imply that the Avid Interplay Engine and the external database are 100% in-sync all the time, as the SQL database is updated from the Interplay Engine backup.
Setup Requirements Chapter 2 SQL Sync Installation This chapter contain the following explanations: • Setup Requirements • Installation • The First Export Setup Requirements Software Requirements The following versions are supported: n • Oracle SQL Server version... ???? • Microsoft SQL Server 2000 • Microsoft SQL Server 2005 You must have the same version of the Interplay Engine installer as the SQL Syncer installer or there may be issues with the database2.dll file.
Chapter 2 SQL Sync Installation Machine Requirements/Limitations • Due to capacity requirements, it is strongly recommended to purchase and use 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.
Installation 3. In the License Agreement dialog box, read the text carefully and click I accept the agreement. Then click Next >. 4. In the Specify Destination Location dialog box, enter the folder in which you want to install the Avid Interplay SQL Syncer. Click Next >. 5. In the Specify Interplay Backup Path dialog box, type the path to the backup of the Avid Interplay database you want to sync to the SQL database.
Chapter 2 SQL Sync Installation 6. In the Server Execution User Dialog Box, select the user you want to use to run the Avid Interplay SQL Syncer. n This user needs to be the same user that runs the Avid Interplay Engine (the Server Execution User). Do one of the following: 14 t Select Default server user account (AVID_WORKGROUP_USER that was created during the Avid Interplay Engine installation, if you so specified). Then select Next. t Select Custom user account and select Next.
Installation 7. In the Specify SQL Server Name dialog box, 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 2 machine setup: leave the default (the current machine name) t For a 3 machine setup: change the SQL server name to the host name or IP address of the SQL server computer.
Chapter 2 SQL Sync Installation 8. When all the settings have been made, click Next > in the Ready to Install dialog box. A reboot may be required if the database needs to be installed. A successful installation will show the image to follow. Step 4: Run the WorkgroupSQLSetup.exe on the Interplay Server (Optional) The EnableSQLUpdate feature can be turned on (off by default) on the Interplay Server by running the WorkgroupSQLSetup.exe.
The First Export 3. In the Ready to Install dialog box, select Finish. The First Export At the next scheduled update time, DbToSql will run, create the new SQL database, and export the Interplay data it finds in the Interplay Backup Directory. This first update may take a long time since the entire backup database must be exported.
Chapter 2 SQL Sync Installation 18 AVID CONFIDENTIAL DRAFT VERSION 5/24/06
The Schema Chapter 3 The Schema, Syncing and Querying This chapter contain the following explanations: • The Schema • Progress and Success Log • Scheduled Sychronization • Efficient Update Ability • The SQL Queries • Reports The Schema Design Objectives The goal of the data model was to provide a flexible approach that can deal with the highly dynamic character of the Avid Interplay database. In Avid Interplay, an application is not restricted regarding the property space.
Chapter 3 The Schema, Syncing and Querying 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. The Schema Itself The schema exported to SQL is simple and fixed. Objects and their properties, dependencies between objects, and a few lookup tables are supported.
Progress and Success Log See Appendix A for complete descriptions of the tables. 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 Workgroup SQL Syncer\Bin.
Chapter 3 The Schema, Syncing and Querying Scheduled Sychronization The installer schedules the sync tool to run daily at 5:00am. This can easily be changed in the configuration file found in [InstallationDirectory]\Bin\DbtoSQL.xml . See “The XML File” on page 25 for details.
The SQL 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. 4. Add the property value NxNServer_AuthOwner as a column on VOBJECTS. First determine the propertyhandle for NxNServer_AuthOwner: select * from wg_propertyinfo where name LIKE 'NxNServer_AuthOwner' Output: propertyhandle name flags type 238 'NxNServer_AuthOwner' 6145 11 5. Test out a SQL query that does this: select vo.*,vp.
Chapter 3 The Schema, Syncing and Querying 6. 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 5) Run the view: select * from vobjects_wauthowner Output: bdh parent_bdh name AuthOwner 4 3 'NxNServer_PropStorageType' ... 'Administrator' ... Reports If you have Crystal Reports you can load a sample report to use as a template from \Avid Workgroup SQL Syncer\CrystalReports
Configuration Chapter 4 Configuration and Troubleshooting This section contains information on: • Configuration • Troubleshooting Configuration Introduction The SQL Sync is configured through an XML configuration file which can be found in the Installation Directory: \SQL\SQLSync\sqlconfig.xml. A description of the configuration possibilities follows.
Chapter 4 Configuration and Troubleshooting Here is a sample configuration file: Node Descriptions Node Descriptions 26 Node Name Parent Node Description config NONE Top level node of the config file. It must contain a database node and an exportlist node.
Configuration Node Descriptions Node Name Parent Node Description workgroup exportlist There is one workgroup node for each Interplay Database you want to export. It describes the path to the backup directory and specifies the times backup should be performed. Database Attributes The database node has attributes, which are described below. Database Attribute Descriptions Attribute Name Default Value Description server None, must be set Specify the SQL Server that you are exporting to here.
Chapter 4 Configuration and Troubleshooting Workgroup Attributes The workgroup node also has attributes, which are described below. Workgroup Attribute Descriptions 28 Attribute Name Default Value Description backuppath None, must be set The path to the backup directory for the Interplay database you wish to export. DbToSql will choose the most recent completed backup subdirectory from this location.
Troubleshooting Workgroup Attribute Descriptions Attribute Name Default Value Description databasename The default database name is the name of the Interplay database being exported. By default, DbToSql will create or export to a SQL database with the same name as that of the Interplay database being exported. You can override this behavior by specifying the SQL database name here. Troubleshooting Running the Export Immediately By running dbtosql.
Chapter 4 Configuration and Troubleshooting 30 AVID CONFIDENTIAL DRAFT VERSION 5/24/06
Appendix A Table Descriptions 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. propertytype Bigint Propertytype maps to information about the property. propertyid Bigint A numeric ID used to separate anonymous properties. value ntext The string value of the anonymous property.
Appendix A Table Descriptions Column Name Type Description 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 will be specified here. (may be NULL). wg_dependency_types Column Name Type Description type bigint The type ID name ntext The dependency type name.
wg_mimetypes 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. deltaupdate tinyint 1 if an efficient update could be performed (as opposed to a full export). durationseconds int The time required to complete the export. message ntext In case of an error, this contains text for a diagnosis.
Appendix A Table Descriptions wg_objects Each row is a Workgroup Object. 34 Column Name Type Description bdh bigint The canonical object identifier. bih bigint The low level ID of the object (often called the BIH) parent_bdh bigint The BDH of the parent object. name ntext The object name. mimetype_bdh bigint The BDH of the mime type for this object. If there is no mime type object associated this field will be NULL. flags bigint Low-level object flags (undefined).
wg_properties wg_properties Each row represents a property on an object. Exactly one of stringvalue, integervalue or utcvalue will be non-NULL for each row. The propertyhandle determines the type. Column Name Type Description bdh bigint The BDH for the containing object. propertyhandle bigint The property handle.
Appendix A Table Descriptions 36 Column Name Type Description propertyname bigint The ID for the property type. name ntext The property type name.
