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 Interview Questions - Basics Concepts, Control flow and Data flow

SSIS Interview Questions for Basic Concepts and Event Logging

SSIS Interview Questions on Transactions, Event Handling and Validation

SSIS Interview Questions on Architecture and Internals

SSIS Interview Questions for Memory Management and Deployment

SSIS Interview Questions for Data Profiling, Protection Level and Package Configuration

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 Package Deployment Model in SQL Server Denali (Part 1 of 2)

SSIS Package Deployment Model in SQL Server Denali (Part 2 of 2)

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.

SQL Server Denali SSIS Enhancements Part 1 of 2

SQL Server Denali SSIS Enhancements Part 1 of 2

SQL Server Denali THROW statement to raise an exception

Raising exception with THROW statement in SQL Server Denali

Prior to SQL Server 2005, writing code for exception handling was painful for T-SQL developers. SQL Server 2005 introduced structured exception handling with BEGIN TRY...BEGIN CATCH blocks. Though this inclusion made managing exceptions in T-SQL programming quite easier, it has some limitations. In this tip I am going to talk about some of these challenges and limitations, then show how a new command THROW in SQL Server Denali overcomes those items, click here for more info.

Understanding Ad-hoc paging in SQL Server Denali

SQL Server Denali Server Side Paging

When designing a User Interface (UI) you normally display 10, 20, 50 or 100 rows on the screen for readability purposes and easy navigation. One way to do this, is to bring all the data from SQL Server to local memory and do the navigation locally. This approach looks simple, but has a couple of issues. First, it could choke the network during a huge data transfer from the server to the local machine (or machine running UI). Second, you could need much more memory to accommodate the data locally. Third, when you navigate to the next page you are actually viewing data stored locally and you will not be able to see updated records on the server until you re-fetch all the data again. So what are some options for server side paging and how does SQL Server Denali improves on this process? Check out this tip to learn more.

Diagnostic logging in SharePoint 2010

For diagnostic logging, the Unified Logging Service (ULS) has been available since previous versions of SharePoint. But what improvements and refinements have been done in SharePoint 2010? What are the options available for diagnostic logging in SharePoint 2010? How better we can control the amount of diagnostic information to be logged in SharePoint 2010? For more details click here.

Getting Started with the SharePoint Logging and Reporting Database in SharePoint 2010

In my last tip I talked about diagnostic logging in SharePoint 2010 and how it helps in troubleshooting. Troubleshooting with trace files is good but wouldn't it be cool if we have all the logging (usage and health) information on single place in consolidated form which help you to build reports and do analysis on collected data from all the servers of the farm? I heard SharePoint 2010 has new logging database. What is it, what does it store? How we can control the amount of information to be logged in the SharePoint logging database? For more details, click care.

Health Analyzer in SharePoint 2010

As a SharePoint Administrator you always wanted to have something in place which alerts you about the running issues or potential problems in your farm. SharePoint 2010 has come up with an 'out of the box' Health Analyzer feature which alerts you about the potential problems, and the locations where they occurred, the cause and how to resolve these issues. Let's explore it more.

Getting started with Code Snippets feature of SQL Server Denali

In SQL Server Denali under Manageability enhancements, code snippets feature has been introduced. In this tip I am going to talk about what it is, how to use it and if required how can we customize/add code snippet (script template) as per our need, for more details click here.

Short-cut keys for commonly used tasks in SSMS - Part 2

We often overlook different SSMS shortcut keys which provide a boost in DBA and Developer productivity. In the second tip of this series (SQL Server Management Studio keyboard shortcuts - Part 1), I am going to further explain shortcut keys for managing Intellisence, debugging, running your code and many more...click here

Short-cut keys for commonly used tasks in SSMS - Part 1

As responsibilities are growing every day, a DBA or developer needs to improve his/her productivity. One way to do this is to use as many shortcuts as possible instead of using your mouse and the menus. In this tip we take a look at common tasks you may perform when using SSMS and the associated shortcut keys, for more details click here.

Understanding SQL Server Net-Libraries

I know that SQL Server and its clients interact with each other using different Net-Libraries or network protocols. But what are these Net-Libraries that SQL Server uses and how do each of these differ from the others. Also, in which scenario is one preferred over the others? SQL Server provides different Net-Libraries, known as Dynamic-Link Library (DLL), to let clients communicate with SQL Server using different network protocols. These Net-Libraries specify which networking protocols to use while exchanging data back and forth between SQL Server and the clients. To learn more about it click here.

Service Broker and Poison Message Handling

Have you ever figured out why your Service Broker queue gets disabled automatically? What causes your Service Broker queue to get disabled in the first place? What is a poison message with respect to Service Broker? Is there anything new in SQL Server 2008 R2 for managing poison messages in Service Broker? To explore it more click here.

SQL Server 2008 Service Broker - Conversation Priorities

In a recent article I talked about creating Service Broker application and communicating between Initiator and Target. Service Broker ensures Exactly Once in Order delivery for messages inside a conversation. But what if you want some important messages/conversations (with higher priority) to be processed earlier than messages/conversations with lower priority? Is there any way to define priority in sending or receiving messages in Service Broker? To learn more click here.

SQL Server Service Broker - External Activation

SQL Server Service Broker allows for setting up two types of activation, Internal Activation or External Activation. To handle increased message traffic, in internal activation you specify a stored procedure (multiple instances might be created depending on your setting) to be called and this way you scale up your distributed application, whereas in case of external activation Service Broker sends notification (QUEUE_ACTIVATION event) to an external application/program outside SQL Server to read the message from the queue and process it. This way you actually scale out your distributed application. External activation allows putting heavy weight processing logic outside SQL Server in a separate process than SQL Server which gives better performance and scalability or might run under different credential than the SQL Server service account.

In my last article I talked about setting up internal activation, writing a stored procedure which will be called upon on activation. In this article, I will be talking about external activation in detail. For more details click here.

Service Broker and Poison Message Handling

Have you ever figured out why your service broker queue gets disabled automatically? What causes your service broker queue to get disabled in the first place? What is a poison message with respect to service broker? Is there anything new in SQL Server 2008 R2 for managing poison messages in Service Broker? To explore it more click here.

Understanding SQL Server Net-Libraries

I know that SQL Server and its clients interact with each other using different Net-Libraries or network protocols. But what are these Net-Libraries that SQL Server uses and how do each of these differ from the others. Also, in which scenario is one preferred over the others? To learn about it click here.

SQL Azure - Getting Started With Database Manager

The Database Manager for SQL Azure is a lightweight, easy to use, thin web-based database management tool for connecting to a SQL Azure database and managing different database management tasks like; creating and editing tables, views and stored procedures, editing table data, writing and executing T-SQL queries, etc...for more information click here.

SQL Server Service Broker – Internal Activation

SQL Server Service Broker allows you to create activation stored procedures, which are called automatically (by Service Broker - Queue Monitor) whenever a message arrives in the queue (very much like a trigger but not exactly the same). Not only this, you can specify the number of instances of the stored procedure to be created to process messages from different conversation groups in parallel if the queue is overloaded with the arrival of lots of messages. In this article, I will be talking about setting up internal activation and writing a stored procedure, which will be called on activation, for more information click here.

SQL Azure - Creating backups and copies of your databases

As a DBA you always followed a practice to back up your database (or take a snapshot of your database) before making any changes so that you can revert to your old database state if something goes wrong. Also to setup a development or test environment you use a backup of your database and restore it in the respective environment. If you are moving to SQL Azure, what would you do in these cases as backup / restore and database snapshots are not supported as of now? For more details, click here.

Writing SSBS Applications Across SQL Server Instances - Getting Environments Ready

In my previous couple of articles, I introduced you SQL Server Service Broker, what it is, how it works, its different components and how are they related to each other. Then I talked about writing SSBS application when both Initiator (Sender) and Target (Receiver) are in same database and SSBS application when both Initiator (Sender) and Target (Receiver) are in different databases on the same SQL Server instance. Now let’s move on and see how to write an SSBS application when the Initiator and Target are in two different databases on two different SQL Server instances (machines), for more details click here.

Writing SQL Server Service Broker Applications Across Databases - Initiator in one database and Target in another database on same instance

In my previous couple of articles, I introduced you SQL Server Service Broker, what it is and how it works, what its different components are and how they are related to each other. Then I talked about writing an SSBS application when both Initiator (Sender) and Target (Receiver) are in same database. Now let’s move on and see how to write SSBS applications when Initiator and Target are in two different databases on the same SQL Server instance, for more details click here.

How the Recycle Bin Works in SharePoint

The idea behind SharePoint Recycle Bin is the same as Windows' Recycle Bin, but we have more granular control in SharePoint. So how does the Recycle Bin work in SharePoint? How do you configure it? What are the different stages of the Recycle Bin (item deletion) in SharePoint? For more details click here.

Generating SQL Server Test Data with Visual Studio 2010

As a database developer or tester sometimes you need to have production like data in your environment for your development or testing, but you cannot have the production data because of security and privacy issues. So how you can generate test data or replicate similar data as in production for your development or test environment, for more details click here.

Database Unit Testing with Visual Studio 2010

As a database developer you always want to ensure stored procedures, UDFs (User Defined Function) and triggers perform as expected. And more importantly you want to ensure that a change in an existing SP, UDF or trigger does not break the functionality. That is to say, you want to have smooth and fast regression testing for your database code. How would you do that, how would you write database Unit Test Cases (UTC)? In this article we cover how you can do this in Visual Studio, click here.