I won't go into the details of how to create and use Visual Studio 2010 database projects here (since I am developing a thorough document on the subject) but what I will do is tell you why they are indispensable. In the end it comes down to one thing - PRODUCTIVITY.
If you are thinking about using Visual Studio 2010 database projects but aren't quite sure why you should, read below. If you find these arguments compelling enough than go and give it a try.
1) Source code structure - Visual Studio database projects force the organization of all database source code into an intuitive and somewhat customizable structure that makes all objects easy to find, modify, and version.
2) Dependency checking - The Visual Studio build engine performs dependency checks. If a column name is changed it will immediately identify any dependent database objects such as views and stored procedures that have been adversely affected, saving the time of tracking down bugs after the code has been deployed which eliminates the time necessary to fix the bug(s) and redeploy the database to the affected environments. It can also perform dependency checks between databases. If you have a core application database as well as other system databases such as an audit/logging database, the tool can perform cross-database dependency checks whether or not the other database exist as Visual Studio database project.
3) Deployment scripts - The Visual Studio database project allows for pre and post-deployment scripts that can do things like move files to certain places in preparation for a database deployment, check versions to ensure compatibility prior to deployment and stop deployment if something is not right, populate tables with reference/lookup data after the deployment.
*Keep in mind that deployment includes both the initial creation/installation of the database as well as the upgrade of an existing database that is out of date and even the rollback of a database to a past version.
I have used this tool in conjunction with InstallShield to create install/upgrade processes for a commercial software product. It worked great.
4) Parameterization of any script values - The project allows for the parameterization of just about anything within the source code such as database name, database server, database file locations, etc. so that the same set of source code can be deployed to any environment, without any code modification, by passing in environment specific parameters. Any number of configurations and corresponding sets of variables can be created (e.g. Dev1 Local Dev, Dev2 Local Dev, QA, UAT, PROD) allowing for a two-click (choose the environment and click deploy) deployment to any existing environment.
5) No need for deployment “sub-system” code - The Visual Studio deployment engine removes the need for an abundance of code that would otherwise be necessary in a manual deployment environment. Code for things such as object (table, view, stored procedure, index, function, etc.) existence checks and drops, foreign key drops and recreation, wrapping of scripts in transactions, etc. is automatically generated by the tool. All that is needed is a single “create” script for each object. This dramatically reduces the complexity of source code by eliminating the need to manually create all the deployment scripts necessary to create or upgrade a database.
6) Fast database deployment on any machine - Visual Studio comes with freely distributable executables that provide the ability for non-database developers (ETL, Report, Dashboard developers) to deploy a complete data warehouse (or any other kind of database) with a single command line, .bat, or PowerShell script without the need for a Visual Studio license.
7) Complete build and deployment solutions - Using build automation tools such as NANT or MSBuild we have the ability to package up the above command line deployment capability with tools like the freely distributable SSIS dtutil executable that automates the deployment of SSIS packages and/or RS.exe for report deployment. Taken to this level, we can deploy many if not all SQL Server related software artifacts using a single call to the automated build (NANT, MSBuild) or install (InstallShield, Wise) tool of choice.
I'm sure this doesn't cover everything but it should give you an idea as to how Visual Studio database projects can make all things database source code related much easier and more efficient. Getting started takes about a half hour if you have a pre-existing database and use the import wizard. I strongly urge you to conduct your own experiment so that you can begin to visualize just how powerful a tool this can be.
Showing posts with label Deployment. Show all posts
Showing posts with label Deployment. Show all posts
Monday, January 31, 2011
Tuesday, October 12, 2010
RS.exe and rss Scripts for Automation of SSRS 2008 Administrative Tasks
Download .rss script
Most people that work with SSRS (2005, 2008, 20080 R2) are aware of two ways to deploy Reporting Services catalog items (reports, data sources, shared datasets, images, etc.). These include deploying directly from BIDS (Visual Studio) and by using Report Manager to upload files. There are also a number of web services available that enable us to do this this in a programmatic, automated fashion that allow for things such as dynamic data sources so that we can deploy reports to any environment without having to modify data source properties manually and specifying where in the Report Manager folder hierarchy we want our reports installed - again without having to modify data sources or data sets or references to either.
The following web services are available with for administrative tasks in SQL Server 2008 R2. Note that there is also a ReportExecution2005 web service that provides methods for actually executing reports.
ReportService2006 was used for SSRS instances that are running in SharePoint integrated mode.
ReportService2010 ties 2005 and 2006 together so that one web service works with both native and SharePoint integrated mode.
While you can work directly with these web services using your programming language of choice, Microsoft was kind enough to create RS.exe. This console application acts as a wrapper for these web services. It is installed with SQL Server and like any other executable it can be called by tools like NANT, MSBuild, InstallShield, etc.
My need for this tool arose when I was developing a set of reports for a commercial product that required installation through InstallShield. The development environment included ANT which we also had call RS.exe for automated deployment in an automated build environment.
Now I'll show you how to use RS.exe from the command line. I won't go into detail about how to use it from specific tools as that should be straightforward for anyone that is familiar with the tool they are using.
Below is a sample RS call with three input parameters.
-i is the file that contains your rss script. More on that later.
-s is the address of your ReportServer web service
-e is the web service end point. Remember from above that there are multiple web services available and you can target any of these endpoints using RS.
rs.exe
-i SSRSDeploy.rss
-s http://localhost/reportserver
-e Mgmt2010
Now that we know how to call RS, the key to making it useful is your custom .rss (Reporting Services Script). It is in these scripts that we can write custom code to deploy reports, data sources, images and create folders as well as delete items.
I have posted a sample rss script that has the following generic methods.
CreateFolder: Creates folders in Report Manager
PublishItem: Publishes reports and shared datasets
PublishImage: Publishes images
DeleteItem: Deletes any item
These methods are essentially wrappers around the rs.CreateCatalogItem, rs.DeleteItem, rs.CreateFolder, rs.SetItemDataSources, and rs.SetItemReferences methods. They add additional exception handling and deal with the nuances of publishing reports in locations different than specified in your Visual Studio project and .rdl files.
Note the use of what are referred to as Global Variables. You can pass values into your rss scripts by using Global Variables. You don't declare these variables you simply refer to a non-declared "variable" in your rss script and RS is smart enough to know that you want to set them at the command line. The way to set them is by using the -v argument. See below for another example.
rs.exe
-i SSRSDeploy.rss
-s http://localhost/reportserver
-v rootPath="/Sample"
-v DWServerName="localhost"
-v DWDatabaseName="MyDataWarehouse"
-v sourceFilePath="C:\SourceCode\SSRS"
-e Mgmt2010
With the sample script you should be able to quickly and easily create an automated process for working with your Reporting Services instances.
Download .rss script
Most people that work with SSRS (2005, 2008, 20080 R2) are aware of two ways to deploy Reporting Services catalog items (reports, data sources, shared datasets, images, etc.). These include deploying directly from BIDS (Visual Studio) and by using Report Manager to upload files. There are also a number of web services available that enable us to do this this in a programmatic, automated fashion that allow for things such as dynamic data sources so that we can deploy reports to any environment without having to modify data source properties manually and specifying where in the Report Manager folder hierarchy we want our reports installed - again without having to modify data sources or data sets or references to either.
The following web services are available with for administrative tasks in SQL Server 2008 R2. Note that there is also a ReportExecution2005 web service that provides methods for actually executing reports.
- ReportService2005
- ReportService2006
- ReportService2010
ReportService2006 was used for SSRS instances that are running in SharePoint integrated mode.
ReportService2010 ties 2005 and 2006 together so that one web service works with both native and SharePoint integrated mode.
While you can work directly with these web services using your programming language of choice, Microsoft was kind enough to create RS.exe. This console application acts as a wrapper for these web services. It is installed with SQL Server and like any other executable it can be called by tools like NANT, MSBuild, InstallShield, etc.
My need for this tool arose when I was developing a set of reports for a commercial product that required installation through InstallShield. The development environment included ANT which we also had call RS.exe for automated deployment in an automated build environment.
Now I'll show you how to use RS.exe from the command line. I won't go into detail about how to use it from specific tools as that should be straightforward for anyone that is familiar with the tool they are using.
Below is a sample RS call with three input parameters.
-i is the file that contains your rss script. More on that later.
-s is the address of your ReportServer web service
-e is the web service end point. Remember from above that there are multiple web services available and you can target any of these endpoints using RS.
rs.exe
-i SSRSDeploy.rss
-s http://localhost/reportserver
-e Mgmt2010
Now that we know how to call RS, the key to making it useful is your custom .rss (Reporting Services Script). It is in these scripts that we can write custom code to deploy reports, data sources, images and create folders as well as delete items.
I have posted a sample rss script that has the following generic methods.
CreateFolder: Creates folders in Report Manager
PublishItem: Publishes reports and shared datasets
PublishImage: Publishes images
DeleteItem: Deletes any item
These methods are essentially wrappers around the rs.CreateCatalogItem, rs.DeleteItem, rs.CreateFolder, rs.SetItemDataSources, and rs.SetItemReferences methods. They add additional exception handling and deal with the nuances of publishing reports in locations different than specified in your Visual Studio project and .rdl files.
Note the use of what are referred to as Global Variables. You can pass values into your rss scripts by using Global Variables. You don't declare these variables you simply refer to a non-declared "variable" in your rss script and RS is smart enough to know that you want to set them at the command line. The way to set them is by using the -v argument. See below for another example.
rs.exe
-i SSRSDeploy.rss
-s http://localhost/reportserver
-v rootPath="/Sample"
-v DWServerName="localhost"
-v DWDatabaseName="MyDataWarehouse"
-v sourceFilePath="C:\SourceCode\SSRS"
-e Mgmt2010
With the sample script you should be able to quickly and easily create an automated process for working with your Reporting Services instances.
Download .rss script
Friday, October 16, 2009
SSAS Deployment Error: No mapping between account names and security IDs was done. .
Check to make sure all the Active Directory ids in your role objects are valid. Most likely this error is being caused by a user or group that has been removed from your Active Directory.
Subscribe to:
Posts (Atom)