LINKS
SITE FEED
Support Forum
The fastest and most effective to get answers to your questions quickly. Click Here for more info.
FAQs 
Quick answers to common questions. Click Here for more info.
Troubleshooting
Known issues and basic troubleshooting techniques for problems or unexpected behaviors. Click Here for more info.
Knowledgebase
Technical tips, How-to articles, and other tutorials about ApexSQL Tools. Click Here for more info.
Online Documentation
All of our help files for download or convenient viewing online. Click Here for more info.
Videos
Watch product demos, training videos, or tutorials of our products' main features. We frequently upload new videos so check often for updates. Click Here for more info.
|
ApexSQL Knowledgebase Tips and How-to Articles for Apex SQL Tools Deployment Projects DESCRIPTION Writing source code (no matter if client or server-side) in a development environment is one thing. Deploying this source code from a development environment to a production environment is another. Most companies enforce a change management process to protect their running production environments from poorly written and/or tested code. For such purpose, ApexSQL Diff can be the used.However, there might be situations or even environments where a quicker procedure is appropriate. Such as bringing source code from a development environment to a quality assurance and acceptance environment or smaller companies in which the developer and quality tester is one and the same person. For such cases ApexSQL Edit offers a feature named "Deployment Projects", which I'm going to describe in this blog post. SOLUTION As usual I'm using SQL Server's Northwind sample database along with my 1:1 copy of it, a database named Seattle. And since the room in such a blog entry like this is very limited, I'm focusing on a very basic scenario in which I'm going to write a new stored procedure in the Northwind database (this stands for my development environment) and then going to deploy this stored procedure to the Seattle database (which stands for my production/testing environment). Result of this operation will be that both databases are identical with respect to stored procedures. Creating a new Deployment Project In order to create a new deployment project, you need to select File -> New -> New Deployment Project from the Main Menu:  This will open a new tab in ApexSQL Edit's query panel:  Let's focus for now on the grey area. Here you can enter general information for your deployment project, such as a name and a description. Next you can specify an SQL Server connection that will act as default connection whenever a connection to SQL Server needs to be established unless it is explicitly overriden on the deployment item level. Note that this connection needs to point to your database to which you want to deploy your objects from the development environment (in my case this is my Seattle database). In order to create a connection, just click on the "Set Connection" link:  This will open the typical ApexSQL Edit SQL Server Logon form in which you can specify the target server and database:  The next option deals with the source from which objects are deployed:  Here's an extract from the ApexSQL Edit help file explaining the meaning of each option: Use Current File: this will use the current file from the work folder of Source Control for deploying a database from. Get Latest Version: this will use latest file version for deploying a database from. Get Labeled Version: this will use labeled file version pointed in the right edit box for deploying a database from. For example, in the above graphic we entered “v. 1100.11” file version we want to use. Finally, you can specify how ApexSQL Edit should deal with errors that might happen during a deployment process:  It is not all that hard to guess that marking this option checked makes use of SQL Server's transactional features, e.g. the deployment script is wrapped within a transaction, so that in case of any error all changes applied up to that point are rolled-back leaving the database in a consistent state again. Let's now focus on the blue area of the deployment projects tab:  This part can be used to set the project's options. You can add custom groups and deploy your project from here. But let's have a look at it step by step. Below you can see a screenshot of the project options group:  How to use Deployment Groups? Deployment Projects in ApexSQL Edit consists of one or more "deployment groups" by means of which of can organize your projects. Deployment groups act as containers for items that you want to deploy. Especially for larger projects it makes sense to use more than one of these groups and organize the items to be deployed among these groups, by object type for example. You could have a group for stored procedures, another one for UDF's, and yet another one for views, or other objects. By default, ApexSQL Edit deployment projects contain one group when you create a new deployment project. It's named "Default Deployment Project" and is displayed right below the project option's group. To add a second, just click on the "Add Group" hyperlink:  Immediately a new group is displayed right below the last group. You can edit this group's properties by clicking on the "Edit Group" hyperlink. This opens a similar property page as the one you have already seen above when I explained the property page of a deployment project:  You can rename each group so that the names are more meaningful to you. In the above case I have chosen to rename to group to "My New Deployment Group". By default, each new group inherits the connection settings of the containing project and the setting from which source objects of this group should be deployed. Both settings can be changed individually for each group. The last control is probably the one that requires the most explanations, because we haven't had yet a screen control with a similar meaning: The Execution Prerequisites list. What is this list used for? Well, think of a situation where you're going to deploy a new stored procedure that accesses a new table. In this case the table would be a required prerequisite for the procedure. Not necessarily for the deployment process, as the stored procedure alone could be deployed just fine. However, executing this stored procedure would yield you nothing, as the underlying base table is missing. To avoid such a situation you would add the table to this list. Deploying a database object As mentioned in my introduction, I'm going to write a new stored procedure in my development environment which I then will deploy to my production/testing environment. So, here's is my new stored procedure:  Nothing fancy, but that's not all that relevant here at all. First of all I'm executing this script in my development database to create the procedure. Then I'm saving the script as FetchSingleOrder.prc to my filesystem and add it to Source Control. This last step of adding an item to Source Control is essential when working with Deployment Projects. It is the Source Control Explorer from which you drag items to your deployment groups. So, after finishing this steps, my Source Control Explorer looks like this:  As already mentioned above, adding items to be deployed to a deployment group is simple. Just drag this item from the Source Control Explorer to a particular deployment group and drop it there:  In the above screenshot I'm dragging the FetchSingleOrder.prc treeview item from the Source Control Explorer to the "My New Deployment Group" deployment group and drop it there. The result of this operation is:  Immediately after dropping the item, it is added to the list of objects in this deployment group. By clicking on a list item, you can access this item's deployment settings:  This action will open this items deployment property page:  Some values on this property page cannot be edited, while others can be edited. You cannot edit the Name, Source Control Path, and Local Path values. These are just displayed for informational purposes. You can however edit the connection settings and the setting from which source object this item should be deployed. By default, ApexSQL Edit will assume that the parent settings from the deployment project settings will be used. You can also see here the Execution Prerequisites list. You can specify on the object level which items are also required for a deployment if this item. In my simple scenarion there is no need for any additional step and so I'm now ready to deploy my stored procedure. This operation is initiated by clicking on the "Deploy Project" link in the deployment project option group.  This will start the deployment process. ApexSQL Edit displays a confirmation message box:  After confirming this message all items to be deployed are embedded in a script generated by ApexSQL Edit and then this script is executed against the target database of the deployment project, respectively the database(s) specified on the other levels on which you can set a connection property. Once the deployment process has finished ApexSQL Edit displays a report which informs you about the final deployment status of each single item that was deployed in this project:  In the above screen you can see that my stored procedure was successfully deployed to the Seattle database. To verify this you can check this via the Schema Explorer:  You can see that both databases (Northwind and Seattle) are identical with respect to stored procedures. Done. Conclusion There is probably much more to say about deployment projects. You can save such projects, open, and modify them just like any other project in ApexSQL Edit. But I would suggest that you take a look at this feature of ApexSQL Edit yourself. Deployment Projects offer a simple, yet effective way to safely push objects from one environment to another. AUTHOR Frank Kirkland LAST REVIEW DATE 31 August 2007 Labels: Apex SQL Edit |
© 2008 Apex SQL Tools All Rights Reserved | 1.919.968.8444 | Contact Us | Terms of Use