Synchronization Server Database in SQL Server

Once a user has established a Synchronization Server connection, a SQL database will automatically be created on the SQL Server you have specified during the synchronization server creation. This Synchronization Server database contains all the “internal” connection information, meta data and fact data, along with subsequent changes.

Once you view the Data folder of the MS SQL directory you will see that it now includes two new additional files:

[1]  SQL Server Database Primary Data File or '.mdf'; and

[2]  SQL Server Database Transaction Log File or '.ldf'

If you launch the SQL Server Management Studio and expand the Databases, you will see that the new Synchronization Server database (Avon Trading Company Sync) you have configured has been added.

By expanding the Sync Server database, you will see the five dbo tables that will be automatically generated once you have configured a synchronization server connection. These five tables are as follows:

• dbo.Factdata
   - This table stores all the changes made to the fact data.
• dbo.Login
    - This table contains the user connection information or, in other words, it indicates the users who are connected to the Synchronization Server database at a given time.
• dbo.Metadata
   - This table stores all the changes made to the Metadata.
• dbo.Metalock
   - This table maintains meta lock information. Meta locks indicate that a particular user is currently editing meta data. For instance, when a user opens a dimension for editing a lock will be created so that other users will not be able to edit those dimensions. Typically, locks are released when a user commits changes or is disconnected from the Sync Server Database.
• dbo.POVersion
   - This indicates the PowerOLAP build of the client or the MDB Server that was used to create the corresponding Synchronization Server connection.