This article explains how to architect a SureSync MFT database configuration using multiple SQL Express history databases.
SureSync MFT uses a SQL database extensively. This database includes records for each file processed, log record, job configuration and more. When planning a SureSync MFT deployment, consider the number of files in the environment and the impact that will have on the resulting SQL database size.
Assume approximately 3GB of database storage will be consumed per 1 million files in the data set.
This guidance assumes that you are using the defaults for history retention. The longer you store history, the larger the resulting database. The frequency of file changes can also impact the size of the database since more frequent changes to files will result in more history records. This guidance is to provide you with an idea of SQL storage requirements. The amount of storage consumed in your specific environment may differ depending on the configuration.
Current versions of SQL Express have a 10GB file size limit. If you intend to use SQL Express in an environment with more than 3 million files, you have a couple of options.
- Use SQL Server Standard or better. If your environment has a Microsoft SQL Server, it is recommended to use it. These editions of SQL do not have the database size limit and do not have the various performance throttles Microsoft places on SQL Express.
- Use SQL Express and use MFT's capability to use multiple SQL Express databases to store history to create databases under the 10GB file size limit.
Planning for History Database
If you will deploy SureSync MFT using SQL Express, some planning related to history databases will ensure a smooth deployment. While database history can be migrated at any time, it can be a time-consuming process once large amounts of history are present.
Multiple history database with SQL Express will get around the database file limit for most, but not all, environments. The amount of storage space needed for all of the folders and files within a specific job must fit in a single 10GB database. For example, if you have a single Job synchronizing 30 million files, the estimated amount of file history will be 30GB. Since 30GB is larger than the 10GB file size limit for a SQL Express database, you would need a SQL Standard server or higher.
If you consider the total number of files in your environment, you can determine if history databases are likely to be needed during initial setup. For example, if you have more than 3 million files, you will need history databases. If you create a history database, create a Job and immediately move the new Job's history to a history database the copy process will complete almost instantaneously.
If you wait until all the Jobs have significant file history in the main database, it is still possible to move that history to a new history database. The process just takes a great deal longer because all of that existing history must be copied.
Creating a History Database
The process for creating a history database is very similar to creating the main Hub database.
To begin, stop the Software Pursuits SureSync MFT Communications Agent service on the Hub machine. Launch the MFT Desktop. Click on Home | Database and then the "Create a History Database" button.
The following dialog will be displayed:
You have two options available to you:
- Create a new MFT History database: This option will have MFT create the database on the SQL server for you. The account you have launched the MFT Desktop as must have permissions on the SQL server to be able to create the database.
- Initialize an empty MFT History database: In environments where the user you're running the MFT Desktop as does not have permissions to create a new database on the SQL server, you can create one using SQL Management Studio and then use this option to open the empty database and initialize it.
For this example, we will click on the "Create a new MFT History database" button.
The following fields should be configured as appropriate:
- Name of the SQL Server and any Instance Name: Enter the name of the SQL server and an instance name if used. For example dev20\sqlexpress.
- Name of any Failover Partner (mirrored) SQL Server: This is an optional field which allows you to use a SQL Failover Partner. This functionality has been depreciated from SQL but is currently still available. Should be defined as machinename\instancename.
- Name of SQL Database: Enter the name of the SQL database that should be created.
Full path and file name of the new SQL database: Enter the path on the SQL server where the database files should be created. This folder must already exist. For example, you might enter C:\Databases\SureSyncMFT.mdf.
To complete the database creation panel, you must click on the 'Set SQL Server Database Credentials' button as shown below.
The following panel will load where you can provide the credential. The SureSync MFT Hub service must have a valid SQL authentication account to use for logging into the database. The SQL server must be configured in SQL only or mixed mode authentication to work with SureSync MFT.
The fields that need to be filled out are:
- SQL Server account name: Enter the account name such as SQLAdmin.Password/Repeat
- Password: Enter the password for the account twice.
Click the 'Save' button to save the credential.
You will be returned to the main panel. Click the 'Continue' button to create and initialize the new SQL database.
Moving a Job's History to a History Database
You can now move Job's in your main MFT Hub database to the history database. Please note, if you have Jobs that contain overlapping root paths, all Jobs that include that overlapped root path must be in the same history database.
To begin, stop the Software Pursuits SureSync MFT Communications Agent service on the Hub machine. Launch the MFT Desktop. Click on Home | Database and then the "Manage Database History" button.
The following dialog will load, clicking on the + next to your main Hub database name will expand the view to show all the Jobs in the database.
Check the "Select" box next to each job you wish to move and then click the "Move History" button.
You will then be prompted to select which history database the selected history should be copied to. Select the appropriate database from the drop-down menu and click on the "Start Move" button.
Progress of the history copy will be displayed. A success message will be displayed when the copy has been completed.
