Developing and Deploying SQL Server Applications on HP Integrity Servers Abstract ..............................................................................................................................................3 Environment Setup ................................................................................................................................4 Installation ............................................................................................................................
Create the Cube on Windows Client .............................................................................................27 Deploy the Cube to Integrity .........................................................................................................28 Execute the Cube at Integrity ........................................................................................................29 Reporting Services .........................................................................................
Abstract Visual Studio 2005 is the latest version of Microsoft’s Integrated Development Environment (IDE) product that now offers an even richer set of tools for SQL Server development. Visual Studio 2005 enables software development for all three processor architectures that Windows supports: x86, x64, and IA64. BI Development Studio (BIDS) is a subset of Visual Studio 2005 focused on the development of Integration Service, Analysis Services, and Reporting Services projects.
Environment Setup Installation The development environment consists of an HP Integrity Server and a Windows XP client (any Windows machine x86 or x64 architecture is sufficient). Install the following software on these machines. Unless mentioned or missing in the step, choose the default settings or steps everywhere and continue. HP Integrity Server 1. Microsoft Windows Server 2003 with SP1 for IA64 2. Microsoft SQL Server 2005 Enterprise Edition for IA64 3.
• Visual Studio 2005 installs Business Intelligence Development Studio (BIDS) and .NET Frame Work Software Development Kit (SDK) 2.0 by default. • Visual Studio 2005 and BIDS are not designed to install on IA64 servers. 5. SQL Server 2005 Samples and Sample Databases. This is needed to execute the examples in this paper and your own learning purpose. This is optional. 6.
To launch Business Intelligence Development Studio, on the taskbar, click Start, point to All Programs, Microsoft SQL Server 2005, and then click SQL Server Business Intelligence Development Studio. Visual Studio 2005 SQL Server Projects (ONLY on Windows Client) Objects using .NET Framework languages and Transact-SQL programming language can be created from Visual Studio 2005 SQL Server Projects on 32-bit environment. The figure below shows the projects or solutions available from the installed templates.
Surface Area Configuration (on both Integrity Server and Windows Client) Through Surface Area Configuration for services and connection, enable the remote database connection. You must enable remote connections for each instance of SQL Server 2005 that you want to access from a remote computer. For example, connecting to Integrity server from a windows Client needs to enable this setting.
Other Tools Some of the other tools available are shown below. Click on each topic for more details. These tools are not explicitly used in the examples in this document.
Remote Debugger Configurations Remote Debugging Monitor (IA64) provides a graphical user interface for debugging applications directly on an IA64 server using the Visual studio on the remote machine. For SQL Server 2005 Debugging Your Windows user account on both the windows client and the HP Integrity Server must be a member of SQL Server's sysadmin group. By default, Windows administrators on a machine running SQL Server have SQL Server sysadmin privileges on that machine.
6. Click Advanced this screen to see any privileges required. 7. The following table describes the privileges required to debug various types of code supported by the Remote Debugging Monitor. See more at Microsoft web site required permissions to debug a program. Type of Code Being Debugged Privilege Required Native Code (C/C++ only) No special privileges required (on remote machine) to debug a process running under your own user account.
On Windows Client 1. Install the application to debug (along with the related PDB and other DLLs and necessary files) on the remote machine as part of the Visual studio 2005 Build. 2. Start Visual Studio 2005. Select the project, then properties. On Configuration Properties, select Build Events and then Post Build Events. 3. On Command line section type: xcopy $(TargetDir)*.* G:\$(OutDir)\*.* /E /Y . Map a network drive in the remote machine. ( eg: E:\OutDir).
• Place breakpoints in objects by double-clicking the object name in project Explorer, then click in the left margin of the Text Editor on the lines of code where you want to insert the breakpoint. • A message is displayed in the Output window stating whether the object was successfully deployed to the SQL Server database. The script in the Test.SQL file, (or whichever file was made the default debug script), will run, and if any of your breakpoints hit, you can debug the object.
T-SQL Trigger The following example shows step-by-step how to deploy a T-SQL Trigger from a Windows Client to Integrity Server with a single Click. The same procedure can be used for any T-SQL application development and deployment on Integrity Server. 1. To Launch Visual Studio SQL Server Projects, on the taskbar, click Start, point to All Programs, then Microsoft Visual Studio 2005, then Microsoft Visual Studio 2005. From Visual Studio select the File menu, select New, then Project. 2.
7. On the Solution Explorer, under the project, under scripts, you see the PostDeployment and Pre-Deployment folders, as shown below, that contain numerous scripts for tuning the deployment scenario. 8. On the Visual Studio Project screen, write your Trigger. This sample trigger will not allow programs to CREATE, ALTER, or DROP tables in the Adventure Works database. 9. From the Build Menu, select Build project.
2005. From Visual Studio select the File menu, select New, then Project. Choose the appropriate programming language, for example C#. 2. Select the database. On Templates select SQL Server Projects. Choose the name and the location of the project. Click OK. It will ask to add a database reference as shown below. You could add the database to a project later stage also from project’s Tool menu and then connect to database. 3. Select HP Integrity Server name and press Test connection.
5. To set the build environment, point to project Menu and select Properties. On Build choose platform target Itanium or any CPU and Uncheck Optimized Code. You can not debug with the optimized code checked. Click Ok. A sample page is shown below. 6. Enable SQL debugging by pointing to the Project Menu and selecting Properties, then click the Debug tab. in the Enable Debuggers section, select the SQL Server Debugging check box.
9. Right-click Solution Explorer, select Add, then New Item, then Stored Procedure. Select a name for the stored procedure. Click OK. 10. Write the code in the Visual Studio project window as shown below. This example is a simple Hello World program. 11. From the project’s Build Menu, select Build Solution. The status window at the bottom of the screen shows the activities. 12. If there are build errors, they will be displayed.
15. Open the SQL Server Management Studio on Integrity Server. Run the stored procedure as shown below. The output “Hello World:” string is displayed. 16. If there are run time issues on Integrity Server, use the remote debugger from the Visual studio. Set up the remote debugger as described in Section 2.3. Connect to the sqlserver process and run the stored procedure at Integrity Server and start debugging.
• SQL Server Import Export Wizard is available on Integrity machine for data transfer (ETL), but it has very limited capabilities for SSIS packages. • SSIS packages can be managed from SQL Server Management studio on Integrity Server. BIDS on windows client is only needed for the package design and debugging. The example below shows how a sample SSIS package can be developed, debugged, deployed and tested on Integrity Servers.
8. Select the control flow task and from the Debug menu select Toggle Break points to set or clear the breakpoints, a red circle shown in the figure below. 9. From the Debug menu select Windows and then select Breakpoints to display all the break points set in the task. In this example there is ONLY one control flow task and that is the Data flow task. 10. Right-click on the Control flow task to bring up all the available break points setting conditions to choose as shown in figure below. 11.
Deploy the Package to Integrity 1. From the Project Menu select Project Properties. In the Debugging section, set the Run64BitRuntime to TRUE to specify whether the Integration Services runtime uses an available 64-bit version of the SSIS run time provider. See Integration Services Considerations on 64-bit Computers on the Microsoft web site for more details. 2. In the Deployment utility section, set AllowConfigurationChanges to TRUE to manage the package later in IA64 machine.
5. Select Properties to export on the next screen. You can choose all properties or selectively set them. After the wizard finishes, the new configuration is added to the configuration list as shown above in the Package Configuration Organizer dialog box. 6. Create the package by selecting Build Package or Build Solution from the Build Menu. This creates the package, a Configuration XML File, and a Manifestation XML file for the deployment.
Execute the package at the Integrity Server 1. To execute an SSIS package that is saved in the file system, configured externally with a logging option use the following command, where pkg.dtsx is the SSIS package, config.xml is the configuration file and log.txt is the log file. dtexec /f "c:\pkg.dtsx" /conf "c:\Config.xml"/l "DTS.LogProviderTextFile;c:\log.txt" 2.
Script Task must be compiled before the package runs in a 64-bit environment. If you do not use the default precompile option, the script is compiled at run time and will not work on Integrity. Run time compile slows package execution, however the draw back is that a precompiled script cannot be debugged without all the relevant files. Using the package we created in the preceding sections, we’ll extent it with VB .NET code.
5. Click the Design Script Tab. It opens the Visual Studio Application designer. Cut and paste the following lines of VB .NET code. ' Microsoft SQL Server Integration Services Script Task Imports Imports Imports Imports Imports Imports System System.Data System.Math Microsoft.SqlServer.Dts.Runtime System.Data.SqlClient System.Data.
MsgBox("The max number of Addresses in the table is" and strTheId, 0, "SSIS Script Task Example") connection.Close() ' String Defenition Dts.Variables("sqlint").Value = strTheId ' Test MsgBox(Dts.Variables("sqlInt").Value.ToString) Dts.TaskResult = Dts.Results.Success End Sub End Class 6. From the Build menu, Select the Precompile option as shown. 7. From the Debug menu, select start debugging.
Analysis Services • Analysis Services objects (e.g., perform back-ups, processing) can be managed from SQL Server Management Studio on Integrity and you can also create Scripts project to manipulate and apply changes to objects on an Analysis Services instance. • These Analysis Services use a variety of languages like XMLA, MDX, DMX, ASSL, to manage its data objects.
Deploy the Cube to Integrity Two most commonly used deployment options are OnceClick deployment from Business Intelligence Studio and using the Analysis Services Deployment Wizard. OnceClick does not provide the flexibility of the Deployment Wizard. 1. From the Analysis Services Project Select properties. The Deployment Mode property controls how the project is deployed; either all of the project is deployed or only the changed objects are deployed. 2.
Execute the Cube at Integrity 1. From SQL Server Management Studio connect to the Analysis Services instance. After the deployment of the Analysis Services project the Databases tab will be populated with the new project. 2. Select the database or the individual cube within the database to process by rightclicking it and select Process.
Reporting Services • HP Integrity Server can play the role of Report Server database or Report Server or both as shown below. In fact, for performance issues it is wise to separate the Report Server and Report Server database. • Reporting Services projects are developed from BIDS on a windows client computer, separate from the report server. After your report looks the way you want, you can publish it to a report server, where it becomes available for general use either ondemand or subscription based.
1. From taskbar, click Start select Program Files, then Microsoft SQL Server 2005, then Configuration Tools, then Reporting Services Configuration. 2. Select Server Status as 3. Select the Report Server Virtual Directory as 4. Select the Report Manager Virtual Directory as 5. Select Windows Service Identity as 6. Select Web Service Identity as 7. Select Database setup and choose Integrity Server’s Adventure Works database. Select the Credentials as Windows credentials. 8.
The following example shows how to develop a Reporting Services project tbe debug, deploy and test it for Integrity Server. This sample shows how to develop a Report from the Adventure Works database on Integrity Server and deploy it. Create a Report on Windows client 1. From BIDS choose the Report Services project. Select the Report Server Project Wizard template. Type a name and the location. 2. On Report Wizard choose the Database source Adventure Works Database from the IA-64 machine.
3. From Build Menu select Deploy project or from the solution explorer choose project and select Deploy. 4. From Internet Explorer select the Deployed Report Server location. For example, URL http://IntegrityServer/ReportServer. The report solution we created is available for use at this site as shown below. Web-Based Report Management Browse to the URL http://IntegrityServer/reports. This is the location of the Report Manager.
Develop and Deploy Applications without Visual Studio Business Intelligence applications are developed in Business Intelligence Studio (BIDS) which is part of the Visual Studio tool set. Without BIDS very limited actions can be performed for the BI application arena. But most of the BI objects can be managed through SQL Server Management Studio directly from Integrity Server. • For Integration Services, package design and debugging needs BIDS.
The example below from an Integrity Server shows how to create a .NET assembly using a 64bit SDK environment and deploying to the SQL Server 2005 1. From the taskbar, click Start, point to All Programs, point to Microsoft .NET Frame works SDK v2.0(64 bit), select SDK Command Prompt. 2. Create a project folder underneath and run the following command to create the safe key for the .NET code to execute. Sn –k keypair.snk 3. Copy the HelloWorld project created in Section 3.
GO 7. Run the stored procedure from the SQL Server Management Studio. The result “Hello World” will be displayed in the Query Window pane. 8. Use the Microsoft CLR Debugger for bug fixes at compile time as well as run time. Native VC++/VB6 Applications Microsoft recommends using .NET languages for SQL Server 2005 programming. The purpose of this section is to help you port legacy applications to HP Integrity Servers. Do not code your new SQL server 2005 programming in native VC++ or Visual Basic code.
5. Choose this IA64 configuration for all the future builds for generating native IA64 binary. Also, from this properties window, several other advanced options can be set. 6. To enable SQL debugging on Visual Studio 2005 C++ projects, in the Property Pages dialog box, open the Configuration Properties node, and select the Debugging node. Set SQL Debugging to Yes. Click OK. 7. From Project Tools Menu Select Options, then select Projects and properties.
11. Some of the 64-bit runtime dlls must be copied to the IA-64 machine for VC++ application execution on IA-64. Those files are msvcp80.dll, msvcr80.dll, msvcm80.dll and Microsoft.VC80.CRT.manifest. To install these dlls and the Itanium compiler, select the "Itanium Compilers and Tools" check box during a Custom installation. Copy these dlls to the target machine. 12. Write your VC++ code and compile using the Itanium compiler. A sample HelloWorld program is shown below.
http://msdn2.microsoft.com/zh-cn/library/ms136025(SQL.90).aspx 6) SMO Programming http://msdn2.microsoft.com/en-us/library/ms162169.aspx 7) HP Windows Integrity resources http://www.hp.com/go/integrity http://www.hp.
