...
The issue with the second option is that it can be difficult for inexperienced SkySync Administrators and DBAs to determine just how big those pre-sized files should be. So, the tendency is to over-allocate which can waste precious high performance disk space. Essentially, the best solution is a combination of the two. Pre-create and conservatively pre-size the SkySync and TempDB database data files to minimize extent operations. Then enable Instant File Initialization to ensure that if there must be extent operations, they happen quickly.
Steps to Enable Instant File Initialization
...
5) Restart the SQL Server service
...
Pre-Creating and Pre-Sizing SQL Data Files
SQL Server experts suggest that multiple database files can have a meaningful impact on database performance. Paul Randal is one of the leading authorities on all things SQL Server. His company, SQLSkills, maintains a website full of useful and reliable information regarding SQL Server. Among the many articles in his “In Recovery…” blog is this one which highlights the benefits of properly scaling out database data files. It also draws attention to potential performance pitfalls of not doing it correctly.
...
When a database is first created in SQL Server through the user interface, the database will be created with a single “mdf” data file by default. It is possible to create this database with the primary “mdf” data file and then multiple “ndf” data files as well. This gives the database an opportunity to store content across multiple data files.
...
Configuring SQL data files in this way allows for improved data access performance because it allows SQL Server to multi-thread disk I/O operations. This is particularly helpful when the administrator has the freedom to store each of the data files on a unique, high performance disk volume. This technique can be used to enhance the ability of the SkySync solution to scale out with many processing servers.
The general rule of thumb for the number of data files that should be created is ¼ to ½ the number of physical cores available to SQL Server. For an 8 core SQL Server, deploying (2) to (4) data files is ideal. For a 16 core SQL Server (4) to (8) files would be good. For fewer cores, trend towards the “1/2” number. For a machine with 16 cores or more, trending towards the “1/4” number is generally typical.
...
Given the highly transactional nature of any migration platform, database maintenance plans become very important. Without proper maintenance, indexes can quickly become fragmented resulting in reduced performance.
| Info |
|---|
It is beyond the scope of this document to provide specific scripts for database maintenance. However, it is important to run index defragmentation and reorganization scripts at least twice per week to ensure that SkySync tables are properly maintained. If maintenance scripts are not standardized in the organization, Ola Hallengren provides very useful scripts that can rebuild indexes across all tables in a database but only when necessary. |
...
Like any migration solution, the data in the database can generally be considered transient. In other words, even if the database is lost, organizational content is not lost. This means that while database backups are useful to minimize any lost migration or synchronization processing time, recent log backups are not necessary.
| Info |
|---|
For these reasons, it is recommended that the SkySync database be configured for Simple Recovery model. There is no need to waste storage space and processing resources on data logging. |
...
With the SkySync database operating in the Simple Recovery model, database backups are straightforward. For high throughput solutions, a nightly full backup is generally sufficient. However, if the Recovery Point Objective (RPO) for the organization indicates that a full day of migration/synchronization processing is too much time loss, then additional incremental backups can be scheduled at intervals throughout the day.
Ideally, database backups should be executed with compression enabled to minimize backup storage size and backup duration. However, this will come at a cost of increased CPU utilization on the SQL Server which is important to consider.
...
2)Click on the performance tab in the SkySync configuration window:
3)Increase or decrease parallel writes as necessary by (1) or (2) at a time.
...