Wednesday, December 14, 2011

SQL Server Service Broker Security

When we talk of communicating messages back and forth between Initiator and Target we need to ensure that communication is secure and no one on the way can temper with messages being sent. So what are the security features available for Service Broker to ensure secured communications?

To explore it click here.

Understanding SQL Server Service Broker Authentication

Service Broker endpoint supports both Windows Authentication and Certificate Based Authentication. Windows Authentication is preferred if the distributed Service Broker endpoints are in the same windows domains, and Certificate Based Authentication is used if these endpoints are in two different windows domains, for information click here.

Executing a SSIS Package from Stored Procedure in SQL Server

Recently we had a requirement to execute a SSIS package from one of our user stored procedures, but there is no direct way available in SQL Server for executing a SSIS package from a stored procedure. SQL Server has some undocumented system stored procedures for SSIS package management, but none is available that can be used to execute a SSIS package from the stored procedure. Now the question is, how can we execute a SSIS package from the user stored procedure? If the direct method is not available, is there any alternative for this?

Here are two different ways a SSIS package can be executed from a stored procedure. In the first approach, we will create a job, making SSIS package call a job step and executing it by calling the sp_start_job system stored procedure from the user defined function. In the second approach, we will enable xp_cmdshell to execute the DTEXEC command line utility from the user defined stored procedure, for information click here.

SQL Server Integration Services ( SSIS ) Interview Questions

When preparing for a SQL Server interview, it is helpful to understand what questions may be asked related to SSIS. In this series, I will try to cover as much as I can to help you prepare.

SSIS Package Deployment Model in SQL Server 2012 - Denali

Deployment has always been a challenge for SSIS developers to deploy packages. SSIS developers are envious of SSRS/SSAS developers as they have an easy way to create a single unit of deployment (deployment package) that contains everything needed for the deployment. The good news is the inclusion of the SSIS Package Deployment Model in SQL Server 2012 code name Denali. In these articles I cover what it is and how to get started to simplify your SSIS package deployments.

SSIS Enhancements in SQL Server 2012 - Denali

I heard there are couple of enhancements in SQL Server Denali for SSIS. What are these enhancements and how does improves the usability, productivity, manageability and performance? Check out this articles to learn more.