Introduction
This project is designed for addressing SQL Server Script deployment on multiple servers and databases. In large enterprises, scripts would need to deploy on 1000+ servers in one release. It is very much possible to have a single jump box to push scripts on a large number of servers. This application would help development and Database Administrators in creating a deployment batch file. Once you get the batch file from this application, you could deploy scripts from central jump box or if you've direct connectivity from your laptop to databases, then you can deploy scripts using batch from your laptop directly.
Background
This is an efficient way of deploying scripts on SQL Server.
This project is also uploaded to https://oneclickdbscriptdeployment.codeplex.com/. I'm the owner of this project on codeplex and code project website. I've not put about a flash page to this application.
Using the Tool
Brief Description About Application
This is a wizard-like application. It is very easy to use. It is simple to generate the batch file for SQL Script deployment.
The application has 3 screens:
- Welcome screen
- DB Scripts, servers and database mapping screen
- Batch file script screen
Welcome Screen
There are two options on the Welcome Screen:
- Multiple Scripts Option: This option is for deploying multiple scripts from a folder.
- Single Script Option: This option is for deploying single script from a folder.
Based on your requirement, you would need to select the option and click Next button at the bottom.
Multiple Scripts
When you want to deploy multiple scripts into a number of servers or single server, you can select this option. The following series of operations would explain to you how this option needs to utilize.
DB Scripts, Servers and Database Mapping Screen
When you select Multiple Scripts Option on the welcome screen and come to this screen, it would look like follows. This screen has the following fields:
Change Number: This field is for script deployment change number as per change management system within your organization.
Action: Type change number in the textbox.
Scripts Folder: This field is for selecting folder containing .sql DB scripts. If you’ve multiple folders containing .sql scripts, then select root folder. The application would include the .sql files from subfolder as well.
Action: Click on the button next to textbox for selecting script folder.
Servers List: This is a table like control. You can input the server list into this table by right clicking on a table. When you right click on the table, you would get a context menu with pastelist and delete row options. You can copy the server list into clipboard from Notepad. It is advisable to keep server list and database list typed into Notepad prior to starting this application.
Action: In order to input server list into Servers List table, you would need to type the server list into empty Notepad. Keep this list ready prior to starting an application. The example is given below. Once you typed the list, then select that list by Ctrl A for select all option, then Ctrl C to copy the list to the clipboard. Right click on Server list table, click on paste list menu option. It would paste the server list into server list table. This action is recorded as explained below.
Type server list into Notepad and select all and copy action.
Paste the server list into server list table.
The server Authentication column has a button to select the authentication option. The below animation would explain how to set server authentication. When you click apply to all option, then the same authentication method applies to all the servers into the list.
In order to delete the server from the list, select the server, you want to delete, then right click on Server List table, click delete row. This action is explained as follows:
Databases List: Follow the same method to copy paste databases list from note to Databases List table. There is context menu attached to this list box same as Servers List. You can paste the DB list and delete the databases in the same fashion as that of servers list.
Batch File Folder: You would need to select the final batch file folder where you want to save the deployment batch file on your local drive.
Action: Click on the button with ... and select the folder from a local disk drive.
Select All: Once you've updated all required fields, then you can click Select All button to select all the servers from server list and all the databases from databases list.
Clear All: This button is for clearing the selected server and databases lists.
Green Color Plus Button
Once you've updated all these required fields, then you can click on Green Color Plus Button. This action would load all the details into Batch File Scripts table. There are up and down arrow buttons attached to this Batch File Scripts table for rearranging the deployment script order. As per your requirement, you can order the scripts in the table. Also, if you want to delete any script from the table then you can use Red Crossed delete button.
Each script with the table has one drop down box to select your option if a script fails what action you would expect batch file should take. There are two options given as follows:
- Exit scripts execution upon failure
- Ignore failure and move to next script
You would need to select this option and click next.
Final Batch File Script Verification Screen
This screen would look as follows. This screen has two portions:
- Actual batch file script code portion for your verification
- Change description portion for updating the change ticket description
You can copy paste the Change description portion details in your change ticket. Also, you would need to attach scripts and batch to change ticket.
Please Note
The folders marked in the red line in the above figure are updated into application installation folder.
Usually, deployment folder location is C:\Program Files (x86)\OneClick\OneClick-DBScriptDeployment\OneClick.exe.config. You would need to update file OneClick.exe.config. This file has the following application settings by default. If you want to modify the folder structure on deployment server, then you would need to change folders in this file:
<applicationSettings>
<OneClick.My.MySettings>
<setting name="ServerScriptFolderLocation" serializeAs="String">
<value>D:\Releases\Scripts</value>
</setting>
<setting name="ServerScriptLogFolderLocation" serializeAs="String">
<value>D:\Releases\Logs</value>
</setting>
<setting name="ServerBatchFileLocation" serializeAs="String">
<value>D:\Releases\</value>
</setting>
</OneClick.My.MySettings>
</applicationSettings>
FOR USE
FOR DEVELOPMENT
- Visual Basic .NET 2013
- Windows OS 7.0
- .NET 4.5
Expected Users
- Microsoft SQL Server Database Administrators
- SQL Script Developers
- Release Engineers
History
This project is also uploaded to https://oneclickdbscriptdeployment.codeplex.com/. I'm the owner of this project on codeplex and Code Project website. I've not put about a flash page to this application.
