Introduction
The EZ Data Archiving Tool offers a method of trimming off older data objects. The trimmed objects are moved to another 'archived database' for storage purposes. These objects can later be restored to the live system if needed.
Objects that can be archived:
- NVR [name-value records]
- PSR [part step records]
- BG [blogs]
- FSR [flow step records]
- EVR [event records]
- EM [email]
The Data Archiving Tool assumes that the user(s) have a solid understanding of MS SQL Server, SQL Queries, SQL Management Studio, and basic understanding of databases. It is not considered to be a low level type of application.
Installation and Source Code
Source Code is downloadable via our Repository. A username and password is required to access this. It is available by request only.
ClickOnce Installation is used to install the executable onto your computer.
Configuration
It should be noted that this application should be installed on the same server as the SQL Server hosting your live database. If it is not, then you should take care that you have file access/permissions so that your connection strings will work properly. The application can also read the connection strings easily if it has access to the web application folder where EZ-MES is installed.
Setting Up Your Connection Strings
As mentioned earlier, you can use the tool that will automatically populate your connection strings or manually type them in. To do this automatically we will select the website folder using the icon.
After you select the website folder, the Operational DB Connection String will be populated along with the Application Website Folder.
To test your connection string you can use the Connection String Tester Icon.
Create Archived Database Structure.
Open the Scripts Folder by going to the File Menu. This will open the file browser window that contains a folder with the SQL Script that is used to create the Archived Database Structure used by the Data Archiving Tool.
Use SQL Management Studio (SSMS) to first create an empty database named 'EZ_Archive'. In a query window, open the SQL Script from the folder labeled 'Create Archive DB Structure' to create the structure. For the 'USE' statement match the database name to be correct with the same database you just created. This example we used a name of 'EZ_Archive'. Execute the script and it should complete successfully.
In the database you will now see two tables
- tbl_EZ_Obj
- tbl_EZ_Obj_Data
Add Connection String to the New Database
Copy the Operational Database Connection string to the Archive Database connection string. You will then change the database name in the connection string to that of the new archive database, 'EZ_Archive'. You must also ensure in SSMS that you have the user login privledges for this archive database.
Add SOAP Communication Access
Populate the Soap User, Soap Key and Web-Service URL.
- SOAP User: username
- SOAP Key: If you do not have a SOAP Key then Generate a New PassKey.
- Web Service URL: This is the address used to access the EZ-MES Web Applicaiton + EazyWebservice.asmx
i.e. http://projectname.eazyworks.com/eazywebservice.asmx
Generate FR and PR Reports
It is good practice to generate the FR and PR reports so that data is archived correctly. If it is not done then you potentially risk some archived data not being done correctly. To do this go to Tools, Check PR and FR Reports. This test can take some time and is dependant on your database size and server performance. This is designed to be run on live systems that are operating.
Selecting Objects to Archive
On the Data Archive tab you can select the objects to archive. All objects should be archived but it is not necessary.
Set the Days to Archive to the value desired. If this value is set to 365 then you will archive all selected objects older than 365 days.
You can run this now by hitting the Archive Button.
When this archive is completed, you can then confirm this by checking your EZ_Archive database by querying the table tbl_EZ_Obj_Data.
Selecting Objects to Retrieve
From the Data Restore tab, enter an array of job numbers into the textbox. If you have the items in a query window then you should also be able to simply copy and paste the job numbers into this textbox.
Now click the 'Move Array of Objects' button to transfer these objects back to the live system.
