Free SQL Help for Devs, DBAs, and the Curious

For many organizations, dealing with multiple environment setups such as development, testing, staging and production is a common scenario. This blog post aims to delve into how to deploy Report Server Projects across these varied environments. The SQL Server versions considered are SQL Server 2012 and SQL Server 2014. We will also touch upon MySQL 5.6, DB2 10.5 and Azure SQL.

One of the fundamental steps in setting up Report Server Projects across multiple environments is creating a Shared Data Source and a Shared Dataset. A Shared Data Source provides a convenient way to manage database connection information. Similarly, Shared Datasets can be used across multiple reports.

Implementing the Shared Data Source in SQL Server 2012 and SQL Server 2014 involves using the SQL Server Data Tools. After launching the Data Tools, create a Shared Data Source by following these steps: right-click on the Shared Data Sources folder, then select 'Add New Data Source'. Fill in the necessary details in the wizard that comes up.

-- Create a Shared Data Source

USE ReportServer

GO

INSERT INTO DataSource (Name, Link) VALUES ('SharedDataSource', 'Data source link')

GO

In MySQL 5.6, you can create a shared data source by connecting to the MySQL server and executing the following SQL command:

-- Create a Shared Data Source

USE mysql;

CREATE DATABASE SharedDataSource;

For DB2 10.5, the process to create a shared data source is similar. You need to connect to the DB2 database and execute the following SQL command:

-- Create a Shared Data Source

CONNECT TO db2inst1;

CREATE DATABASE SharedDataSource AUTOMATIC STORAGE YES;

Once we have the shared data sources and datasets ready, the next step is deploying the report server projects. In SQL Server 2012 and 2014, you can deploy the report server project by right-clicking on the project in the Solution Explorer and selecting 'Deploy'. However, this method does not provide a way to manage deployment to multiple environments.

To effectively manage deployments across multiple environments, it's best to use a script or a tool that can automate the process. One such tool is the 'rs.exe' utility provided by Microsoft. This utility can be used to publish reports, create folders, and manage shared data sources and datasets. The utility uses a script file that contains commands in Visual Basic .NET code.

In conclusion, deploying Report Server Projects across multiple environments involves creating shared data sources and datasets, and then deploying the report server projects. To effectively manage the deployment process across different environments, it's best to use an automation tool like 'rs.exe'. The process of creating shared data sources and datasets varies slightly depending on the database technology used (SQL Server, MySQL, DB2, etc.), but the core concept remains the same.