Avid® Interplay™SQL Sync
Installation & Administration Guide
AVID CONFIDENTIAL DRAFT
make manage move | media
™
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 software may not be reverse assembled and may be used or copied only in accordance with the terms of the
license agreement. It is against the law to copy the software on any medium except as specifically allowed in the license
agreement.
No part of this document may be reproduced or transmitted in any form or by any means, electronic or mechanical, including
photocopying and recording, for any purpose without the express written permission of Avid Technology, Inc.
Copyright © 2004 Avid Technology, Inc. and its licensors. All rights reserved. Printed in USA.
The following disclaimer is required by Apple Computer, Inc.
APPLE COMPUTER, INC. MAKES NO WARRANTIES WHATSOEVER, EITHER EXPRESS OR IMPLIED, REGARDING
THIS PRODUCT, INCLUDING WARRANTIES WITH RESPECT TO ITS MERCHANTABILITY OR ITS FITNESS FOR ANY
PARTICULAR PURPOSE. THE EXCLUSION OF IMPLIED WARRANTIES IS NOT PERMITTED BY SOME STATES. THE
ABOVE EXCLUSION MAY NOT APPLY TO YOU. THIS WARRANTY PROVIDES YOU WITH SPECIFIC LEGAL RIGHTS.
THERE MAY BE OTHER RIGHTS THAT YOU MAY HAVE WHICH VARY FROM STATE TO STATE.
The following disclaimer is required by Sam Leffler and Silicon Graphics, Inc. for the use of their TIFF library:
Copyright © 1988–1997 Sam Leffler
Copyright © 1991–1997 Silicon Graphics, Inc.
Permission to use, copy, modify, distribute, and sell this software [i.e., the TIFF library] and its documentation for any purpose
is hereby granted without fee, provided that (i) the above copyright notices and this permission notice appear in all copies of
the software and related documentation, and (ii) the names of Sam Leffler and Silicon Graphics may not be used in any
advertising or publicity relating to the software without the specific, prior written permission of Sam Leffler and Silicon
Graphics.
THE SOFTWARE IS PROVIDED “AS-IS” AND WITHOUT WARRANTY OF ANY KIND, EXPRESS, IMPLIED OR
OTHERWISE, INCLUDING WITHOUT LIMITATION, ANY WARRANTY OF MERCHANTABILITY OR FITNESS FOR A
PARTICULAR PURPOSE.
IN NO EVENT SHALL SAM LEFFLER OR SILICON GRAPHICS BE LIABLE FOR ANY SPECIAL, INCIDENTAL, INDIRECT
OR CONSEQUENTIAL DAMAGES OF ANY KIND, OR ANY DAMAGES WHATSOEVER RESULTING FROM LOSS OF USE,
DATA OR PROFITS, WHETHER OR NOT ADVISED OF THE POSSIBILITY OF DAMAGE, AND ON ANY THEORY OF
LIABILITY, ARISING OUT OF OR IN CONNECTION WITH THE USE OR PERFORMANCE OF THIS SOFTWARE.
The following disclaimer is required by the Independent JPEG Group:
Portions of this software are based on work of the Independent JPEG Group.
The following disclaimer is required by Paradigm Matrix:
Portions of this software licensed from Paradigm Matrix.
The following disclaimer is required by Ray Sauers Associates, Inc.:
“Install-It” is licensed from Ray Sauers Associates, Inc. End-User is prohibited from taking any action to derive a source code
equivalent of “Install-It,” including by reverse assembly or reverse compilation, Ray Sauers Associates, Inc. shall in no event be
liable for any damages resulting from reseller’s failure to perform reseller’s obligation; or any damages arising from use or
operation of reseller’s products or the software; or any other damages, including but not limited to, incidental, direct, indirect,
special or consequential Damages including lost profits, or damages resulting from loss of use or inability to use reseller’s
products or the software for any reason including copyright or patent infringement, or lost data, even if Ray Sauers Associates
has been advised, knew or should have known of the possibility of such damages.
The following disclaimer is required by Videomedia, Inc.:
“Videomedia, Inc. makes no warranties whatsoever, either express or implied, regarding this product, including warranties with
respect to its merchantability or its fitness for any particular purpose.”
“This software contains V-LAN ver. 3.0 Command Protocols which communicate with V-LAN ver. 3.0 products developed by
Videomedia, Inc. and V-LAN ver. 3.0 compatible products developed by third parties under license from Videomedia, Inc. Use
of this software will allow “frame accurate” editing control of applicable videotape recorder decks, videodisc recorders/players
and the like.”
2 AVID CONFIDENTIAL DRAFT VERSION 5/24/06
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.:
Certain waveform and vector monitoring capabilities are provided under a license from 3Prong.com Inc.
Attn. Government User(s). Restricted Rights Legend
U.S. GOVERNMENT RESTRICTED RIGHTS. This Software and its documentation are “commercial computer software” or
“commercial computer software documentation.” In the event that such Software or documentation is acquired by or on behalf
of a unit or agency of the U.S. Government, all rights with respect to this Software and documentation are subject to the terms
of the License Agreement, pursuant to FAR §12.212(a) and/or DFARS §227.7202-1(a), as applicable.
Trademarks
888 I/O, Adrenaline, AirPlay, AirSPACE, AirSPACE HD, AniMatte, AudioSuite, AudioVision, AutoSync, Avid, Avid DNA,
Avid DNxHD, AVIDdrive, AVIDdrive Towers, Avid Mojo, AvidNet, AvidNetwork, AVIDstripe, Avid Unity, Avid Xpress, AVoption,
AVX, CamCutter, ChromaCurve, ChromaWheel, DAE, D-Fi, D-fx, Digidesign, Digidesign Audio Engine, Digidesign Intelligent
Noise Reduction, DigiDrive, Digital Nonlinear Accelerator, DigiTranslator, DINR, D-Verb, Equinox, ExpertRender, FieldPak,
Film Composer, FilmScribe, FluidMotion, HIIP, HyperSPACE, HyperSPACE HDCAM, IllusionFX, Image Independence,
Intraframe, iS9, iS18, iS23, iS36, Lo-Fi, Magic Mask, make manage move | media, Marquee, Matador, Maxim, MCXpress,
Media Composer, MediaDock, MediaDock Shuttle, Media Fusion, Media Illusion, MediaLog, Media Reader, Media Recorder,
MEDIArray, MediaShare, Meridien, MetaSync, NaturalMatch, Nearchive, NetReview, NewsCutter, Nitris, OMF,
OMF Interchange, OMM, Open Media Framework, Open Media Management, ProEncode, Pro Tools, QuietDrive, Recti-Fi,
RetroLoop, rS9, rS18, Sci-Fi, Softimage, Sound Designer II, SPACE, SPACEShift, Symphony, the Avid|DS logo, Trilligent,
UnityRAID, Vari-Fi, Video Slave Driver, VideoSPACE, and Xdeck are either registered trademarks or trademarks of Avid
Technology, Inc. in the United States and/or other countries.
iNEWS, iNEWS ControlAir, and Media Browse are trademarks of iNews, LLC.
All other trademarks contained herein are the property of their respective owners.
Footage
Arri — Courtesy of Arri/Fauer — John Fauer, Inc.
Bell South “Anticipation” — Courtesy of Two Headed Monster — Tucker/Wayne Atlanta/GMS.
Canyonlands — Courtesy of the National Park Service/Department of the Interior.
Eco Challenge British Columbia — Courtesy of Eco Challenge Lifestyles, Inc., All Rights Reserved.
Eco Challenge Morocco — Courtesy of Discovery Communications, Inc.
It’s Shuttletime — Courtesy of BCP & Canadian Airlines.
Nestlé Coffee Crisp — Courtesy of MacLaren McCann Canada.
Saturn “Calvin Egg” — Courtesy of Cossette Communications.
“Tigers: Tracking a Legend” — Courtesy of www.wildlifeworlds.com, Carol Amore, Executive Producer.
Windhorse — Courtesy of Paul Wagner Productions.
Arizona Images — KNTV Production — Courtesy of Granite Broadcasting, Inc.,
Editor/Producer Bryan Foote.
Canyonlands — Courtesy of the National Park Service/Department of the Interior.
Tornados + Belle Isle footage — Courtesy of KWTV News 9.
WCAU Fire Story — Courtesy of NBC-10, Philadelphia, PA.
Women in Sports – Paragliding — Courtesy of Legendary Entertainment, Inc.
GOT FOOTAGE?
Editors — Filmmakers — Special Effects Artists — Game Developers — Animators — Educators — Broadcasters — Content
creators of every genre — Just finished an incredible project and want to share it with the world?
Send us your reels and we may use your footage in our show reel or demo!*
For a copy of our release and Avid’s mailing address, go to www.avid.com/footage.
*Note: Avid cannot guarantee the use of materials submitted.
AVID CONFIDENTIAL DRAFT VERSION 5/24/06 3
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. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 5
Accessing the [Tutorial and] Online Library . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 6
How to Order Documentation . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 6
Avid Training Services . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 6
Chapter 1 SQL Sync Introduction . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 9
Introduction . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 9
Architecture Overview . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 9
Limitations. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 10
Chapter 2 SQL Sync Installation . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 11
Setup Requirements . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 11
Software Requirements . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 11
Machine Setup Options . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 11
Machine Requirements/Limitations . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 12
Installation. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 12
Step 1: Install the Avid Interplay Engine. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 12
Step 2: Install the SQL Server . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 12
Step 3: Install the SQL Syncer . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 12
Step 4: Run the WorkgroupSQLSetup.exe on the Interplay Server (Optional) . 16
The First Export. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 17
Chapter 3 The Schema, Syncing and Querying . . . . . . . . . . . . . . . . . . . . . . . . . . . . 19
The Schema . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 19
Design Objectives . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 19
Overview . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 20
The Schema Itself . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 20
Progress and Success Log . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 21
Scheduled Sychronization. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 22
AVID CONFIDENTIAL DRAFT VERSION 5/24/06 1
Efficient Update Ability . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 22
The SQL Queries . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 22
Tutorial on View Creation . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 23
Reports. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 24
Chapter 4 Configuration and Troubleshooting. . . . . . . . . . . . . . . . . . . . . . . . . . . . . 25
Configuration . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 25
Introduction . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 25
The XML File . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 25
Node Descriptions . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 26
Database Attributes . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 27
Workgroup Attributes . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 28
Troubleshooting . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 29
Running the Export Immediately . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 29
Table Descriptions . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 31
wg_anonymous_properties . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 31
wg_dependencies . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 31
wg_dependency_types. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 32
wg_dependency_usages . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 32
wg_exportinfo. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 32
wg_mimetypes . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 33
wg_objects . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 34
wg_properties. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 35
wg_propertyinfo . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 35
wg_propertytypes. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 35
2 AVID CONFIDENTIAL DRAFT VERSION 5/24/06
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.
AVID CONFIDENTIAL DRAFT VERSION 5/24/06
Using This Guide
Symbols and Conventions
Avid documentation uses the following symbols and conventions:
Symbol or Convention Meaning or Action
n
c
w
> This symbol indicates menu commands (and
t
k
Margin tips
(Windows), (Windows only),
(Macintosh), or (Macintosh
only)
A note provides important related information,
reminders, recommendations, and strong suggestions.
A caution means that a specific action you take could
cause harm to your computer or cause you to lose data.
A warning describes an action that could cause you
physical harm. Follow the guidelines in this document
or on the unit itself when handling electrical
equipment.
subcommands) in the order you select them. For
example, File > Import means to open the File menu
and then select the Import command.
This symbol indicates a single-step procedure.
Multiple arrows in a list indicate that you perform one
of the actions listed.
This symbol represents the Apple or Command key.
Press and hold the Command key and another key to
perform a keyboard shortcut.
In the margin, you will find tips that help you perform
tasks more easily and efficiently.
This text indicates that the information applies only to
the specified operating system, either Windows XP or
Macintosh OS X.
Italic font Italic font is used to emphasize certain words and to
indicate variables.
Courier Bold font
Ctrl+key or mouse action
k+key or mouse action
4 AVID CONFIDENTIAL DRAFT VERSION 5/24/06
Courier Bold font identifies text that you type.
Press and hold the first key while you press the last
key or perform the mouse action. For example,
k+Option+C or Ctrl+drag.
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:
- If release notes are available, they ship with your application.
- If ReadMe files are available, they are supplied in your Avid application folder.
ReadMe files are also available from Help.
If You Need Help
n
Release notes and ReadMe files are also available on the Avid Knowledge Center.
3. Check the documentation that came with your Avid application or your hardware for
maintenance or hardware-related issues.
4. Visit the online Knowledge Center at www.avid.com/support. Online services are
available 24 hours per day, 7 days per week. Search this online Knowledge Center to
find answers, to view error messages, to access troubleshooting tips, to download
updates, and to read/join online message-board discussions.
5. For Technical Support, please call 800-800-AVID (800-800-2843).
For Broadcast On-Air Sites and Call Letter Stations, call
800-NEWSDNG (800-639-7364).
Related Information
The following documents provide more information about:
The Online Library includes a Master Glossary of all specialized terminology used in the
documentation for Avid products.
n
For the latest product information, see the Avid Knowledge Center:
www.avid.com/support
AVID CONFIDENTIAL DRAFT VERSION 5/24/06 5
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. The
Acrobat folder on the CD-ROM contains an installer for Acrobat Reader. The [tutorial and
the] effects reference guide require[s] Apple’s QuickTime
QuickTime movies. You can download the latest version of QuickTime from the Apple
site.
To access the [tutorial and] online library from the [Tutorial and] Online Library CDROM:
1. Insert the [Tutorial and] Online Library CD-ROM into the drive.
2. Double-click the Mainmenu file.
To access [the tutorial and] the online library from the Help:
1. Insert the [Tutorial and] Online Library CD-ROM into the drive.
2. In your Avid application, select Help > Online Library.
How to Order Documentation
To order additional copies of this documentation from within the United States, call Avid
Sales at 800-949-AVID (800-949-2843). If you are placing an order from outside the United
States, contact your local Avid representative.
®
application to view the
®
Web
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.
6 AVID CONFIDENTIAL DRAFT VERSION 5/24/06
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-949-
2843).
AVID CONFIDENTIAL DRAFT VERSION 5/24/06 7
Using This Guide
8 AVID CONFIDENTIAL DRAFT VERSION 5/24/06
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.
What the Avid Interplay Engine does not provide is a standard searching interface, which
can be used by standard tools like Crystal Reports. Although the Avid Interplay searching
implementation is flexible enough to fulfill a lot of requirements, it is not as powerful and
flexible as SQL searches on a relational database system, for example.
Introduction
In order to provide the missing flexibility without implementing a complete SQL query
engine inside the Avid Interplay Engine, the data stored in backups of the Avid Interplay
Engine can be replicated into another SQL database. The optional SQL Sync (DbtoSQL) can
be attached to the Avid Interplay Engine, from which it creates an initial information export
and then on a set schedule, exports the entire Avid Interplay database regularly (an efficient
update of only the database backup changes can configured instead). These changes are
exported into the SQL database the SQL Sync tool is connected to. The view of the database
can then be altered as needed.
This document provides an overview over the SQL synchronization architecture used in the
replication of an Avid Interplay Engine database backup into an external database system.
Architecture Overview
The SQL Syncer sits between the Avid Interplay Engine and the SQL database server. The
setup program installs the SQL Sync as a service running in the background and
automatically connecting to the configured Avid Interplay Engine and SQL database server
on startup as specified during installation or in the configuration file.
AVID CONFIDENTIAL DRAFT VERSION 5/24/06 9
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. 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.
In case of network failures, database shutdowns or other problems in the involved
components which cause changes to the Avid Interplay Engine not being propagated to the
external database, the implementation does not perform an automatic resynchronization.
10 AVID CONFIDENTIAL DRAFT VERSION 5/24/06
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:
• Oracle SQL Server version... ????
Setup Requirements
• Microsoft SQL Server 2000
• Microsoft SQL Server 2005
n
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.
Machine Setup Options
The three elements (Interplay Engine, SQL Syncer and SQL database) of the SQL Sync can
be distributed successfully in the following ways:
• 2 machines: Interplay Engine on one machine, and SQL Syncer and SQL database on
the other.
• 3 machines: Interplay Engine on one machine, SQL Syncer on another machine, and the
SQL database on a third machine.
AVID CONFIDENTIAL DRAFT VERSION 5/24/06 11
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
Step 1: Install the Avid Interplay Engine
To install the Avid Interplay Engine:
t See the Avid Interplay Engine and Avid Interplay Access Installation Guide for
instructions on how to install the Avid Interplay Engine. There are no extra steps that
need to be performed.
Step 2: Install the SQL Server
Install the SQL server, if you have not already done so. This should be on a machine other
than the Avid Interplay Server.
Step 3: Install the SQL Syncer
This installation creates a new SQL Sync service running in the background, and
automatically connecting to the configured Avid Interplay Engine and SQL database server.
To install the SQL Syncer:
1. Run the WorkgroupSQLSyncer.exe installation program from the CD.
2. Click Next > In the Welcome dialog box.
12 AVID CONFIDENTIAL DRAFT VERSION 5/24/06
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.
AVID CONFIDENTIAL DRAFT VERSION 5/24/06 13
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:
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. You will then be prompted to enter the
custom User Name and Password, and to confirm the password.
14 AVID CONFIDENTIAL DRAFT VERSION 5/24/06
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.
AVID CONFIDENTIAL DRAFT VERSION 5/24/06 15
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. See “Efficient Update Ability” on page 22 for details
before installing this feature, as it will have a slight impact on server performance.
1. Start the WorkgroupSQLSetup.exe
2. In the Welcome dialog box, select Next.
16 AVID CONFIDENTIAL DRAFT VERSION 5/24/06
3. In the Ready to Install dialog box, select Finish.
The First Export
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.
AVID CONFIDENTIAL DRAFT VERSION 5/24/06 17
Chapter 2 SQL Sync Installation
18 AVID CONFIDENTIAL DRAFT VERSION 5/24/06
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 Schema
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. The creation of a new property in the Avid Interplay
Engine is implicitly performed as soon as an application sets a property the first time.
The Avid Interplay Engine is designed to provide such a flexible structure. In contrast, a
relational database system with its table-based data structures is more designed to deal with
data models which are mainly fixed and don't change over time.
The goal behind the design of the data model for the Avid Interplay Engine synchronization
was to provide a flexible, efficient and self-contained implementation. Flexible because the
dynamic creation of properties must be properly mapped to corresponding data structures in
the database. Efficient means that for updating, adding or getting meta-information, a
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.
AVID CONFIDENTIAL DRAFT VERSION 5/24/06 19
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. The most important properties live
in the WG_OBJECTS table as a column. For objects in the server, a BIH is stored for each
unique version object, and a BDH to refer to the set of versions. For the export, only the
current version of the objects is considered, which reduces the output table size and
complexity of the schema. Columns can be added to WG_OBJECTS using views (see
“Tutorial on View Creation” on page 23.
The following diagram shows the export schema for the export of the current object snapshot
from the Interplay Database.
20 AVID CONFIDENTIAL DRAFT VERSION 5/24/06
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.
AVID CONFIDENTIAL DRAFT VERSION 5/24/06 21
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.
Efficient Update Ability
If the feature is turned on, the Avid Interplay Engine will keep a record of (timestamp,object
id) pairs, so that on subsequent runs DbToSql only needs to look at objects changed since the
last update time instead of exporting the entire database backup. This can drastically reduce
the amount of time required to export to SQL. It does have a slight performance impact on
the server (2%-3% in a dedicated test constantly updating Interplay objects).
See “Step 4: Run the WorkgroupSQLSetup.exe on the Interplay Server (Optional)” on
page 16 for information about turning the feature on.
The SQL Queries
After exporting the Interplay Database to the SQL Database, it is easy to view the exported
contents and run queries.
22 AVID CONFIDENTIAL DRAFT VERSION 5/24/06
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.stringvalue AuthOwner from wg_objects vo, wg_properties
vp where vo.bdh=vp.bdh and vp.propertyhandle=238
Output:
bdh parent_bdh name <etc> AuthOwner
4 3 'NxNServer_PropStorageType' ... 'Administrator'
...
AVID CONFIDENTIAL DRAFT VERSION 5/24/06 23
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 <etc> 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
24 AVID CONFIDENTIAL DRAFT VERSION 5/24/06
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.
Configuration
The XML File
The XML file should contain, in an xml configuration format:
• Information about type of SQL database you are connecting to
• Location of that database
• Username/password to connect with
• For each Interplay Engine database you want to back up, you need
1. the UNC path to its backup folder
2. the frequency information and time we want export to occur
AVID CONFIDENTIAL DRAFT VERSION 5/24/06 25
Chapter 4 Configuration and Troubleshooting
Here is a sample configuration file:
<config>
<database server="kabelbinder"
type="MSSQLServer"
username="sa"
password="sa"
retriesBeforeScan="3"/>
<exportlist>
<workgroup backuppath="\\MACHINEA\WG_Database$\AvidWG\_Backup"
schedule="-05:00"/>
</exportlist>
</config>
Node Descriptions
Node Descriptions
Node Name Parent Node Description
config NONE Top level node of the
database config Describes the SQL server
exportlist config exportlist contains a list
26 AVID CONFIDENTIAL DRAFT VERSION 5/24/06
config file. It must
contain a database node
and an exportlist node.
you are exporting to.
of Interplay databases
you want to export to
SQL
Node Descriptions
Database Attributes
The database node has attributes, which are described below.
Database Attribute Descriptions
Configuration
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.
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
<server>\<named
instance>
type None, must be set Type must be one of
"MSSQLServer",
"Oracle", or
"PostgreSQL".
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.
retriesBeforeScan “3” You can customize the
number of times DbToSql
will retry an Efficient
Update before reverting
to a full export.
AVID CONFIDENTIAL DRAFT VERSION 5/24/06 27
Chapter 4 Configuration and Troubleshooting
Workgroup Attributes
The workgroup node also has attributes, which are described below.
Workgroup Attribute Descriptions
Attribute Name Default Value Description
backuppath None, must be set The path to the backup
schedule None, must be set This is in the format "-
directory for the Interplay
database you wish to
export. DbToSql will
choose the most recent
completed backup
subdirectory from this
location.
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.
Finally, the format
"+HH:MM,HH:MM,…"
is supported, where each
HH:MM entry in the
comma-seperated list is a
time in 24-hour time
format when the export
should be performed. For
example, "+01:00,13:00"
means that an export
should occur at 1 am and
1 pm daily.
28 AVID CONFIDENTIAL DRAFT VERSION 5/24/06
Troubleshooting
Workgroup Attribute Descriptions
Attribute Name Default Value Description
databasename The default database
Troubleshooting
Running the Export Immediately
By running dbtosql.exe kickstart from a command prompt, you will initiate an immediate
export from the Interplay backup directory to the SQL server, for each project in
dbtosql.xml.
Any errors or messages will go to the log file. This is a useful diagnostic or test mode-- less
cumbersome than setting a time to export in the dbtosql.xml file because you get immediate
feedback on success or failure of the process.
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.
AVID CONFIDENTIAL DRAFT VERSION 5/24/06 29
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
propertytype Bigint Propertytype maps to
propertyid Bigint A numeric ID used to
with the anonymous
property.
information about the
property.
separate anonymous
properties.
value ntext The string value of the
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"
AVID CONFIDENTIAL DRAFT VERSION 5/24/06
anonymous property.
object.
Appendix A Table Descriptions
Column Name Type Description
type bigint The type of the
usageid bigint Usageid maps to a string
to_bdh bigint The BDH for the "to"
object_bdh If an object is associated
wg_dependency_types
dependency, viewable
with a lookup in
wg_dependency_types.
associated with the
dependency.
object.
with the dependency link,
it's BDH handle will be
specified here. (may be
NULL).
Column Name Type Description
type bigint The type ID
name ntext The dependency type
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.
32 AVID CONFIDENTIAL DRAFT VERSION 5/24/06
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.
wg_mimetypes
The Workgroup mimetypes.
Column Name Type Description
bdh bigint The BDH for the Mime
name ntext The name of the mime
path ntext The path for the mime
ext ntext The file extention string
AVID CONFIDENTIAL DRAFT VERSION 5/24/06 33
Type object.
type.
type in a tree of mime
types organized in broad
categories.
for files associated with
this mime type.
Appendix A Table Descriptions
wg_objects
Each row is a Workgroup Object.
Column Name Type Description
bdh bigint The canonical object
bih bigint The low level ID of the
parent_bdh bigint The BDH of the parent
name ntext The object name.
mimetype_bdh bigint The BDH of the mime
identifier.
object (often called the
BIH)
object.
type for this object. If
there is no mime type
object associated this
field will be NULL.
flags bigint Low-level object flags
isfolder tinyint True if the object is a
ischeckedout tinyint True if the object is
issystem issystem True if the object is a
smot datetime Server File Modification
simt datetime Server File Import Time
scit datetime Server Check In Time
scrt datetime Server File Creation Time
34 AVID CONFIDENTIAL DRAFT VERSION 5/24/06
(undefined).
folder.
checked out.
Workgroup System
object.
Time
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.
wg_properties
Column Name Type Description
bdh bigint The BDH for the
containing object.
propertyhandle bigint The property handle.
stringvalue ntext The string value (may be
NULL)
integervalue bigint The integer value (may
be NULL)
utcvalue datetime The datetime value (may
be NULL)
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
name ntext The property name.
propertytype bigint References the property
wg_propertytypes
A row is given for each unique property type in the system.
AVID CONFIDENTIAL DRAFT VERSION 5/24/06 35
each property.
type (see table
wg_propertytypes).
Appendix A Table Descriptions
Column Name Type Description
propertyname bigint The ID for the property
name ntext The property type name.
type.
36 AVID CONFIDENTIAL DRAFT VERSION 5/24/06
Loading...