The Microsoft Dynamics® GP archive utility, Company Data Archive, now has the ability to archive data onto a completely different server. The new feature, dubbed “linked server archiving”, is available in the Plus version of Company Data Archive (CDA Plus). Traditional archiving creates a new archive database on the same SQL server to which one’s historical GP data is archived. Now with linked-server archiving, there’s more flexibility.
Why is this cool?
First and foremost, archiving data onto a different server is going to free up space on the production server. This is the most important and apparent benefit to linked server archiving.This new option also provides more flexibility when running the process opposed to traditional archiving. Linked server archiving allows users to perform archives with less effect on production server performance. Because CDA is impacting production server performance less than before, it opens more possibilities, especially for companies with tight time windows due to other processes and work going on overnight, etc. The bulk of the load is on the archive server, but there is still some on the production server. Look at it this way, instead of one server having to do all the work, now you have two, like a freight train with engines at the front and the back.While linked server archiving isn’t going to reduce hardware costs per se, it will allow companies to utilize second tier hardware. Since this process actually uses more hardware than the traditional one server model, it’s feasible for companies who already have hardware and SQL license available, perhaps the old production server. The savings are then realized due to the disk space reduction and the fact you don’t have to buy more enterprise class storage. While your desktop 1T drive may cost you $100, that much enterprise storage would run you probably 30x as much.Disk space is the big plus. Traditional CDA users on the one-server model may have had the archive company on a USB drive as not to fill up their SAN/storage system, but archive performance would have been very slow. Now CDA users can have this on another server and get both space and time back.
Requirements
Linking SQL servers has a few requirements. The SQL servers must be on the same SQL version and the collations sequences must match (see Table 1 for CDA compatibility with GP and SQL).
How does it work?
On Production Server:
Users will need to install Company Data Archive and create an archive company, then take backups of the production Dynamics database and the archive company database.
One Archive Server:
Once the archive server is built, the product database backups will then be restored onto the Archive SQL Server. (Note: Install SQL Server and GP (same versions) and create at least one company in DynUtils)In SQL Management Studio on the Archive SQL Server, the user will setup a Linked server that points to the production SQL server:Expand Server Objects.
The user will then enter the server and SQL Instance name (Example: Q-CDALS-P\GP10) and set up security. (Additional Linked Server Archiving documentation is provided with the Company Data Archive code for this process.)
After the user has registered CDA Plus, the SQL servers are linked, and the user selects the Live company from the Originating Company Name lookup, the user is able to select an expansion button on the Archive Company Data window next to the Originating Company ID.
On this window you put in the Originating Server\Instance. This would be the Production SQL Server name and GP Instance. The Originating Database defaults from the main window.Click the Test Linked Server button. This will verify that connectivity exists between the servers, collation sequences are matched,and the GL account formats are the same.Click OK. After this initial setup, the next time you go to the archive window in this company it will default all this information.
The rest of the archive process remains the same.If you would like to learn more about Company Data Archive, please feel free to attend one of our upcoming webinars or contact us today!
