Applies to
ApexSQL Build, ApexSQL Data Diff, ApexSQL Diff, and ApexSQL Script
Summary
This article explains all about ApexSQL database installer packages.
ApexSQL products offer two ways of database packaging: An executable installer and a C# solution. The difference between these two is that C# solution is source code that can be edited and built in Visual Studio, while the Executable installer is the compiled end product that cannot be edited.
A database installer is useful vs. just creating a script:
- For tighter control of end user execution and ensuring update integrity
- Essentially encrypting the SQL script in a compiled binary
- Compression for a more compact deliverable to end user
- Allows user to run it without installing any other software (e.g. SSMS, osql.exe, etc.)
- Enable easier integration into a larger installer or application (embed installer code into a larger application)
Description
Depending on the product, there are usually several options for packaging that can be set in the Package tab:
An option that can come in handy when creating an installer with a large amount of data for the synchronization is the Use compression option. In an example I performed in writing this KB, an installer built for synchronization of two databases, without this option checked, was 243 MB and with this option checked it was only 6 MB. So, using the Use compression option the installer package size was reduced 4000%.
The Error handling option can be set to Abort, Ignore, or Ask for confirmation during the package execution to affect package behavior when dealing with script errors:
-
Abort – the package will roll back all changes before the error occurred and stop executing aka abort:
- Ignore – any encountered errors will be ignored during the execution
-
Ask for confirmation – asks for the user’s confirmation on any encountered error, whether to abort or to continue the execution:
Additionally, there are several optional package information that can be set, such as: Author, Company, Legal notice, Comments, and Application name. All of these package information will be visible to the end user in the database installer GUI, when the About button clicked.
There are several options that can be set for the Executable installer:
Create CLI executable file – Creates the Command Line Interface (CLI) file (.com) in addition to the main GUI executable (.exe). The CLI file is generally meant to automate and run the package unattended. It can be used from the Command Line or by calling it from any script file/program to execute the process, without the need to use the GUI.
Create script file – This optional feature allows further customization of the database installer, by allowing changes to the script, which will be executed by the database installer. When this option is checked, a file with extension .dat is created along with the executable installer. This file contains the SQL code which will be executed from the installer. In case any modification to the SQL code is needed, it is enough to edit this file, by opening it in any text editor, making changes and saving them. The database installer can work without the .dat file, as it contains the same SQL code, but if some customizations are required, this option can come in handy.
|
Quick tip: If the Use compression option is checked, the Create script file (.dat file) option doesn’t need to be checked to ensure successful package execution |
Create manifest file – This option should be used when there is a possibility of any security or compatibility issues with the installer executable on some other PC/OS. The manifest file contains the information needed for Windows to define the type of security level and operating system version the installer is created to work on. Generally, these options are automatically recognized by Windows, so this file is needed only in situations when the recognition could fail, for example: different OS or privileges on some other PC where the installer will be executed.
All three options are additional and the Executable installer can work without the script and manifest file, as it contains all of their default settings.
At the remote site, double-click the installer, and the following window will be shown:
If the About button is clicked, all of the package information that was set in the Package tab, will be shown:
In the Options window, the Error handling options can be changed:
If everything is in order, click the Run button and after the execution is completed, the following message will be shown:
Q: What is the difference between the C# solution and Executable installer?
A: The C# solution is the source code of which the Executable installer is compiled from. The main difference is that in the C# solution, this code can be changed depending on what the user wants. If you have some programming skills, the code in the C# solution can be altered so that the installer has a different color, or to resize its window, or to change its function, etc. The executable installer is compiled code and it cannot be changed once it’s made.
Q: Is there any option that can be changed for the Executable installer by the end user?
A: The only option that can be changed when it’s created is the Error handling option. When you double-click the installer file, click the Options button and you can change the Error handling option there.
Q: How can I change the caption of the installer window?
A: The caption of the installer window can be set under the Package tab, by adding combination of tags in the Package name field, such as: %server%, %database%, %date%, and %time%. In the Preview field the caption (package name) is automatically shown/changed while combining tags:
Q: Can I change the name of a database on the server where the deployment package will be executed?
A: Yes. Changing the name of the database can be done by checking the Indicate default database option and typing the desired name for the database, which will be entered into Database filed in the installer window by default. Also, any other database can be selected from the drop-down list from the Database field, in the installer window.
Q: Can a deployment package be created via the CLI?
A: Yes. In the following example, a local database with Windows authentication (server and authentication switches are not needed) is used:
ApexSQLDataDiff.com /d1:SourceDB /d2:TargetDB /ot:ne /of:D:\SyncInstaller.exe
These switches will compare SourceDB vs TargetDB and based on the comparison result, the synchronization script for the Executable installer will be created in the specified location. To create the C# solution, switch ne (net_executable) with cs (csharp).
Q: Can a deployment package be run via the CLI?
A: Yes. If the CLI executable file is created, along with the executable installer, it can be run via the CLI. In order to run it via the CLI, navigate to the folder where the executable file is located and call it by entering the ApplicationName.com, server name, user name and password, and a database name:
AdventureWorks2014.com /s:SQLSERVER14 /u:admin /p:Adm!n123 /d:AdventureWorks2014
Additionally, the /e switch (error handling) can be added with abort, ignore, or ask (for confirmation) values. If this switch is omitted, abort option will be used as default.
Note: Server name, user name and password are not needed, if a deployment package will be executed on a local database.
Q: Can I see the result of script execution? Where can I get the log file?
A: Yes. Upon finishing with the execution, the Executable installer shows the window with the execution results:
The package logging folder is by default located in the following path:
…\AppData\Local\ApexSQL\ApexSQLDiff\LOG\Package
Package logging is enabled by default and the path can be changed in the Package tab, under the Logging section:
Q: How much compression do packages offer over script files?
A: Compression depends on the original package size and it can vary between 500% and more than 4000%.
Q: Can end users edit the script at all e.g. by accessing the .dat file? Or is the script compiled into the binary?
A: Yes, the end user can edit the script by editing the .dat file, but the script is also compiled into the binary. It’s compiled into the binary, as a backup, if the .dat file is deleted or lost, the database installer can be run without it. When the database installer is run, it’s searching for the .dat file with the same name (as installer’s) and if any change is made in the .dat file, the installer will execute the SQL code saved in the .dat file.
Q: What will happen if I create the .dat file and then erase it?
A: Since the same script is also compiled into the executable installer, the package will be executed without any issues
Q: What problems might I experience if I don’t create a manifest file?
A: If the manifest file is not created, the possible issues are:
- Inability to execute the package on different PCs, due to different/restrictive privileges, if the Windows User Account Control level differs. By creating the manifest file, it can be right-clicked, opened in any text editor and edited, in order to overcome issues with privileges
- Inability to run the executable installer, due to different OS version. If the manifest file is created, it can be edited in order to add a missing OS version to the list and run the executable installer without issues
Q: Can I embed the installer code into a larger application?
A: Yes, the C# solution code can be embedded into a larger application or the CLI executable file can be called by a script file/application.
