Avid Interplay SQL Sync Installation And Administration Guide 1.2 & Manual V1.2 D
User Manual: avid Interplay SQL Sync - 1.2 - Installation & Administration Manual Free User Guide for Avid Interplay Software, Manual
Open the PDF directly: View PDF 
.
Page Count: 38
| Download | |
| Open PDF In Browser | View PDF |
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. 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. Avid products or portions thereof are protected by one or more of the following United States Patents: 4,970,663; 5,267,351; 5,309,528; 5,355,450; 5,396,594; 5,440,348; 5,467,288; 5,513,375; 5,528,310; 5,557,423; 5,577,190; 5,584,006; 5,627,765; 5,640,601; 5,644,364; 5,654,737; 5,715,018; 5,719,570; 5,724,605; 5,726,717; 5,729,673; 5,745,637; 5,752,029; 5,754,851; 5,799,150; 5,812,216; 5,828,678; 5,842,014; 5,852,435; 5,999,406; 6,038,573; 6,061,758; 6,141,007; 6,211,869; 6,532,043; 6,546,190; 6,596,031; 6,636,869; 6,747,705; 6,763,523; 6,766,357; 6,813,622; 6,847,373; 7,081,900; RE40,107; D352,278; D372,478; D373,778; D392,267; D392,268; D392,269; D395,291; D396,853; D398,912. Other patents are pending. This document is protected under copyright law. An authorized licensee of Interplay Framework may reproduce this publication for the licensee’s own use in learning how to use the software. This document may not be reproduced or distributed, in whole or in part, for commercial purposes, such as selling copies of this document or providing support or educational services to others. This document is supplied as a guide for Interplay Framework. Reasonable care has been taken in preparing the information it contains. However, this document may contain omissions, technical inaccuracies, or typographical errors. Avid Technology, Inc. does not accept responsibility of any kind for customers’ losses due to the use of this document. Product specifications are subject to change without notice. Copyright © 2008 Avid Technology, Inc. and its licensors. All rights reserved. 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: This software is based in part on the work of the Independent JPEG Group. This Software may contain components licensed under the following conditions: Copyright (c) 1989 The Regents of the University of California. All rights reserved. Redistribution and use in source and binary forms are permitted provided that the above copyright notice and this paragraph are duplicated in all such forms and that any documentation, advertising materials, and other materials related to such distribution and use acknowledge that the software was developed by the University of California, Berkeley. The name of the University may not be used to endorse or promote products derived from this software without specific prior written permission. THIS SOFTWARE IS PROVIDED ``AS IS'' AND WITHOUT ANY EXPRESS OR IMPLIED WARRANTIES, INCLUDING, WITHOUT LIMITATION, THE IMPLIED WARRANTIES OF MERCHANTABILITY AND FITNESS FOR A PARTICULAR PURPOSE. Copyright (C) 1989, 1991 by Jef Poskanzer. 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 1995, Trinity College Computing Center. Written by David Chappell. 2 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. Permission to use, copy, modify, distribute, and sell this software for any purpose is hereby granted without fee, provided that the above copyright notice appear in all copies and that both that copyright notice and this permission notice appear in supporting documentation, and that the name of Daniel Dardailler not be used in advertising or publicity pertaining to distribution of the software without specific, written prior permission. Daniel Dardailler makes no representations about the suitability of this software for any purpose. It is provided "as is" without express or implied warranty. Modifications Copyright 1999 Matt Koss, under the same license as above. Copyright (c) 1991 by AT&T. Permission to use, copy, modify, and distribute this software for any purpose without fee is hereby granted, provided that this entire notice is included in all copies of any software which is or includes a copy or modification of this software and in all copies of the supporting documentation for such software. THIS SOFTWARE IS BEING PROVIDED "AS IS", WITHOUT ANY EXPRESS OR IMPLIED WARRANTY. IN PARTICULAR, NEITHER THE AUTHOR NOR AT&T MAKES ANY REPRESENTATION OR WARRANTY OF ANY KIND CONCERNING THE MERCHANTABILITY OF THIS SOFTWARE OR ITS FITNESS FOR ANY PARTICULAR PURPOSE. This product includes software developed by the University of California, Berkeley and its contributors. The following disclaimer is required by Nexidia Inc.: © 2006 Nexidia. All rights reserved. Manufactured under license from the Georgia Tech Research Corporation, U.S.A. Patent Pending. 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.” 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 3Prong.com Inc.: Certain waveform and vector monitoring capabilities are provided under a license from 3Prong.com Inc. The following disclaimer is required by Interplay Entertainment Corp.: The “Interplay” name is used with the permission of Interplay Entertainment Corp., which bears no responsibility for Avid products. This product includes portions of the Alloy Look & Feel software from Incors GmbH. 3 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. These components may only be used as part of and in connection with Avid Interplay. 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, AirSpeed, ALEX, Alexis, AniMatte, AudioSuite, AudioVision, AutoSync, Avid, Avid Advanced Response, Avid DNA, Avid DNxcel, Avid DNxHD, AVIDdrive, AVIDdrive Towers, Avid DS Assist Station, Avid ISIS, Avid Learning Excellerator, Avid Liquid, Avid Mojo, AvidNet, AvidNetwork, Avid Remote Response, AVIDstripe, Avid Unity, Avid Unity ISIS, Avid Xpress, AVoption, AVX, CamCutter, CaptureManager, ChromaCurve, ChromaWheel, Conectiv, CountDown, DAE, Dazzle, Deko, DekoCast, D-Fi, D-fx, DigiDelivery, Digidesign, Digidesign Audio Engine, Digidesign Intelligent Noise Reduction, DigiDrive, Digital Nonlinear Accelerator, DigiTranslator, DINR, DNxchange, do more, D-Verb, Equinox, ExpertRender, Face Robot, FACE ROBOT, FastBreak, Fast Track, FieldPak, Film Composer, FilmScribe, FluidMotion, FXDeko, HIIP, HyperSPACE, HyperSPACE HDCAM, IllusionFX, Image Independence, iNEWS, iNEWS ControlAir, Instinct, Interplay, Intraframe, iS9, iS18, iS23, iS36, ISIS, IsoSync, LaunchPad, Lightning, Lo-Fi, Magic Mask, make manage move | media, Marquee, Matador, Maxim, MCXpress, Media Browse, Media Composer, MediaDock, MediaDock Shuttle, Media Fusion, Media Illusion, MediaLog, Media Reader, Media Recorder, MEDIArray, MediaShare, MediaStream, Meridien, MetaFuze, MetaSync, MicroTrack, MissionControl, MovieBox, NaturalMatch, Nearchive, NetReview, NewsCutter, Nitris, OMF, OMF Interchange, OMM, Open Media Framework, Open Media Management, PCTV, Pinnacle MediaSuite, Pinnacle Studio, Pinnacle Systems, ProEncode, Pro Tools, QuietDrive, Recti-Fi, RetroLoop, rS9, rS18, SalesView, Sci-Fi, ScriptSync, SecureProductionEnvironment, Show Center, Softimage, Sound Designer II, SPACE, SPACEShift, SpectraGraph, SpectraMatte, Sputnik, SteadyGlide, SubCap, Symphony, SynchroScience, TARGA, Thunder, Thunder Station, TimeLiner, Torq, Trilligent, UnityRAID, Vari-Fi, Velvet, Video RAID, Video Slave Driver, VideoSPACE, Xdeck, and X-Session are either registered trademarks or trademarks of Avid Technology, Inc. in the United States and/or other countries. Adobe and Photoshop are either registered trademarks or trademarks of Adobe Systems Incorporated in the United States and/or other countries. Apple and Macintosh are trademarks of Apple Computer, Inc., registered in the U.S. and other countries. Windows is either a registered trademark or trademark of Microsoft Corporation in the United States and/or other countries. All other trademarks contained herein are the property of their respective owners. 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 Interplay SQL Sync Installation and Administration Guide • 0130-07644-03 Rev D• August 2008 • This document is distributed by Avid in online (electronic) form only, and is not available for purchase in printed form 4 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 . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 9 How to Order Documentation . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 9 Avid Training Services . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 10 Chapter 1 Overview of the SQL Syncer Tool . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 11 Chapter 2 Installing the SQL Syncer Tool . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 13 SQL Syncer Setup Requirements . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 13 Installing SQL Syncer . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 14 Running WorkgroupSQLSetup.exe on the Interplay Server (Optional). . . . . . . . . . . 18 First Export to SQL . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 19 Chapter 3 Avid Interplay Database Schema, Syncing and Querying . . . . . . . . . . . 21 Avid Interplay Database Schema . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 21 Progress and Success Log . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 24 Scheduled Synchronization. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 24 Efficient Update Ability . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 24 SQL Queries . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 25 Tutorial on View Creation . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 25 Reports . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 26 Chapter 4 SQL Syncer Configuration and Troubleshooting . . . . . . . . . . . . . . . . . . 27 SQL Syncer Configuration . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 27 SQL Syncer Troubleshooting . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 30 Appendix A Schema Table Descriptions . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 31 wg_anonymous_properties . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 31 wg_dependencies . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 32 wg_dependency_types . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 32 wg_dependency_usages . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 33 5 wg_exportinfo . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 33 wg_mimetypes . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 34 wg_objects . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 34 wg_properties. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 35 wg_propertyinfo . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 36 wg_propertytypes . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 36 Index . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 37 6 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. 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. Follow the guidelines in this document or on the unit itself when handling electrical equipment. This symbol indicates menu commands (and 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. (Windows), (Windows only), (Macintosh), or (Macintosh only) This text indicates that the information applies only to the specified operating system, either Windows or Macintosh OS X. 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. If You Need Help If you are having trouble using your Avid product: 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 the latest information that might have become available after the documentation was published: - If the latest information for your Avid product is provided as printed release notes, they ship with your application and are also available online. If the latest information for your Avid product is provided as a ReadMe file, it is supplied on your Avid installation CD or DVD as a PDF document (README_product.pdf) and is also available online. You should always check online for the most up-to-date release notes or ReadMe because the online version is updated whenever new information becomes available. To view these online versions, select ReadMe from the Help menu, or visit the Knowledge Base at www.avid.com/readme. 3. Check the documentation that came with your Avid application or your hardware for maintenance or hardware-related issues. 4. Visit the online Knowledge Base at www.avid.com/onlinesupport. Online services are available 24 hours per day, 7 days per week. Search this online Knowledge Base to find answers, to view error messages, to access troubleshooting tips, to download updates, and to read or join online message-board discussions. 8 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. For example, the following line opens the portal Web page on a system named DocWG: http://DocWG • Click the “Avid Interplay User Documentation” link to access the User Information Center page. On this page, select the Avid Interplay Framework User’s Guide from the list of user’s guides. Accessing the Online Library The Avid Interplay Online Library DVD contains all the Avid Interplay product documentation in PDF format.The Online Library includes a Master Glossary of all specialized terminology used in the documentation for Avid products. Most Avid online libraries also include multimedia content such as feature presentations. This multimedia content is an excellent first resource for learning how to use your application or for helping you understand a particular feature or workflow. n You need Adobe® Reader® to view the documentation online. You can download the latest version from the Adobe web site. To access the online library from the Online Library DVD: 1. Insert the Online Library DVD into the drive. 2. Double-click the Mainmenu file. 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. 9 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. 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). 10 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. 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. 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 Syncer tool 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 to which the SQL Syncer is connected. The view of the database can then be altered as needed. This topic 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 Syncer 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. After successfully establishing a connection, the SQL Syncer waits for a scheduled synchronization with an Interplay Engine backup and then updates the new events in the corresponding SQL database. 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. In case of network failures, database shutdowns or other problems in the involved components that prevent changes to the Avid Interplay Engine from being propagated to the external database, the implementation does not perform an automatic resynchronization. 12 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. Software Requirements The following versions are supported: • n 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. Machine Setup Options The three elements (Interplay Engine, SQL Syncer, and SQL database) of the SQL synchronization can be distributed successfully in the following ways: • Two machines: the Interplay Engine on one machine, and the SQL Syncer and SQL database on the other. • Three machines: Interplay Engine on one machine, the SQL Syncer on another machine, and the SQL database on a third machine. 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 Contact your Avid representative to obtain a copy of the SQL Syncer installation program. To install the SQL Syncer: 1. Make sure the Avid Interplay Engine is installed. See the Avid Interplay Software Installation and Configuration Guide for instructions on how to install the Avid Interplay Engine. 2. Make sure the SQL Server is installed. The SQL Server must be installed on a machine other than the Avid Interplay Engine. 3. Run the WorkgroupSQLSyncer.exe installation program. The Welcome dialog box opens. 4. Click Next. The License Agreement dialog box opens. 14 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. 15 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). For more information, see the Avid Interplay Software Installation and Configuration Guide. 12. Click Next. The Specify SQL Server Name dialog box opens. 16 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. 17 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.exe on the Interplay Server (Optional) The EnableSQLUpdate feature can be turned on (it is off by default) on the Interplay Engine by running WorkgroupSQLSetup.exe. See “Efficient Update Ability” on page 24 for details before installing this feature, because it has a slight impact on server performance. To turn on the EnableSQLUpdate feature: 1. Start the WorkgroupSQLSetup.exe. The Welcome dialog box opens. 2. Click Next. 18 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. First Export to SQL At the next scheduled update time, the SQL Syncer (DbToSql.exe) runs, creates the new SQL database, and exports the Interplay data it finds in the Interplay Backup directory. This first update might take a long time because the entire backup database must be exported. 19 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. 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. 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 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. Schema 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. n Do not alter the schema; an alteration leads to an exception at export time. Instead, additional columns can be added onto the schema using views (see “Tutorial on View Creation” on page 25.) The following diagram shows the export schema for the export of the current object snapshot from the Interplay Database. 22 Avid Interplay Database Schema See “Schema Table Descriptions” on page 31 for complete descriptions of the tables. 23 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. Efficient Update Ability If the EnableSQLUpdate feature is turned on, the Avid Interplay Engine keeps a record of (timestamp,object id) pairs, so that on subsequent runs the SQL Syncer 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 to 3 percent in a dedicated test constantly updating Interplay objects). See “Running WorkgroupSQLSetup.exe on the Interplay Server (Optional)” on page 18 for information about turning the feature on. 24 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. select * from wg_propertyinfo where name LIKE 'NxNServer_AuthOwner' Output: propertyhandle name flags type 238 'NxNServer_AuthOwner' 6145 11 25 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' ... 3AuthOwner '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. Run the view. select * from vobjects_wauthowner Output: bdh parent_bdh name 4 'NxNServer_PropStorageType' ... 3 AuthOwner 'Administrator' ... Reports If you have Crystal Reports you can load a sample report to use as a template from C:\Program Files\Avid\Avid Interplay SQL Syncer\CrystalReports. 26 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.xml The XML file should contain, in an xml configuration format, the following information: • Type of SQL database you are connecting to • SQL database location • Username/password used to connect to the database • For each Interplay Engine database you want to back up: - UNC path to its backup folder - Frequency information and time for the export to occur By default, the SQL Syncer is scheduled to run daily at 5:00 am. You can change that to any time or frequency. One possibility is to estimate or measure how long a nightly backup takes, and then schedule a sync on that new backup some hours later. You can sync with either a full backup or a partial backup. 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 config file. It must contain a database node and an exportlist node. database config Describes the SQL server you are exporting to. exportlist config Contains a list of Interplay databases you want to export to SQL 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. 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. retriesBeforeScan “3” You can customize the number of times DbToSql retries an Efficient Update before reverting to a full export. Workgroup Node Attribute Descriptions 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 chooses the most recent completed backup subdirectory from this location. 29 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. Finally, the format "+HH:MM,HH:MM,…" is supported, where each HH:MM entry in the comma-separated 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. databasename The default database name is the By default, DbToSql creates or name of the Interplay database exports to a SQL database with being exported. the same name as that of the Interplay database being exported. You can override this behavior by specifying the SQL database name here. SQL Syncer Troubleshooting Running the Export Immediately By running dbtosql.exe kickstart from a command prompt, you initiate an immediate export from the Interplay backup directory to the SQL server, for each project in dbtosql.xml. Any errors or messages go to the log file. This is a useful diagnostic or test mode because it is less cumbersome than setting a time to export in the dbtosql.xml file, and you get immediate feedback on success or failure of the process. 30 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. 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. 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. (may be NULL). wg_dependency_types 32 Column Name Type Description type bigint The type ID name ntext The dependency type name. 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. 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. 33 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. 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 is NULL. flags bigint Low-level object flags (undefined). 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. Exactly one of stringvalue, integervalue or utcvalue is 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. stringvalue ntext The string value (may be NULL) integervalue bigint The integer value (may be NULL) utcvalue datetime The datetime value (may be NULL) 35 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. name ntext The property type name. 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.exe first export 19 E EnableSQLUpdate described 24 enabling 18 Exporting Interplay data to SQL 19 H Hardware requirements SQL Syncer 13 I Installation EnableSQLUpdate 18 SQL Syncer 13 Interplay database schema described 21 R S Scheduling SQL Syncer 24 Schema table descriptions SQL Syncer 31 Software requirements SQL Syncer 13 SQL Server license 14 SQL Syncer architectural overview 11 configuration 27 configuring 24 Crystal Reports report 26 efficient updates 24 first export to SQL 19 installing 13, 14 Interplay database schema 21 log file 24 running immediately 30 scheduling 24 schema descriptions 31 setup options 13 software and hardware requirements 13 troubleshooting 30 view creation 25 viewing exported contents 25 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.exe running 14 38
Source Exif Data:
File Type : PDF File Type Extension : pdf MIME Type : application/pdf PDF Version : 1.6 Linearized : Yes Encryption : Standard V2.3 (128-bit) User Access : Print, Copy, Extract, Print high-res Tagged PDF : Yes Page Mode : UseOutlines XMP Toolkit : Adobe XMP Core 4.0-c316 44.253921, Sun Oct 01 2006 17:14:39 Format : application/pdf Creator : Avid Technology, Inc. Title : Avid Interplay SQL Sync Installation and Administration Guide Create Date : 2008:05:20 11:38:17Z Creator Tool : FrameMaker 7.2 Modify Date : 2008:08:27 13:20:28-04:00 Metadata Date : 2008:08:27 13:20:28-04:00 Producer : Acrobat Distiller 8.1.0 (Windows) Document ID : uuid:b743ae7b-bc96-4e10-8004-d33e0f9adb9b Instance ID : uuid:0115b2e1-5b4b-4d4b-bb8e-903bf8c21cf2 Page Count : 38 Author : Avid Technology, Inc.EXIF Metadata provided by EXIF.tools