Thursday, February 25, 2010

SQL Saturday...

SQL Server Events and conferences are very helpful with respect to learningabout SQL Server, getting to know to your peers and experts in the SQL Server domain. These kind of events also provide an opportunity to network and exchange best practices. The SQL Saturday events was pioneered by SQL Server Central, it is a one day free event aimed providing insight into different aspects of sql server. A Sql server saturday event is being scheduled to be held on March 6th at Charlotte,NC. Here is the link to event providing registration,scheduling information. It has detailed information on all the tracks.

Friday, February 12, 2010

Disaster recovery...

Recently we were asked to present disaster recovery plans for different parts of IT, business intelligence and reporting group was one of them. There are lots of approaches to disaster recovery, it starts from backups for databases and runs all the way upto bringing up a failover server when the primary server crashes. These plans should be an integral part of oragnisation and should not be made as after thought. One of the approaches we took as recovery plan for our reporting database servers was that we had a failover server. This server was configured exactly as the primary server in terms of memory,space and databases. Apart from the regular database backups which were being done on the primary server, we also had to make sure that the failover server can perform as the production server in times of disaster. We did not choose log shipping or database mirroring in this case, instead we ran the same processes in parallel on both the primary and failover servers. This approach worked the best for our situation. One of the tasks we had to make sure happened also maintain the standard databases required for SSRS reporting, since these databases contains the report databases catalog. One of the key issues to be made part of the disaster recovery plan is to include items like how quickly can the failover server be up and running for the regular operations to continue.

Friday, February 5, 2010

SQL Search

Recently came across a mention about SQL Search developed by on the SQL Server Central Site. I decided to check it out, downloaded the software from the red gate site( Once SQL Search is installed, at the end of the installation there is a mention to open Sql server management studio. Once i opened the SSMS and connected to the server, there is a icon in the toolbar called SQL Search. Once you SQL Search it opens up a window like the one shown below.

The sql search window is split into two, on the top window, the term to be searched is entered in the textbox on the left hand side, in the next dropdown choose the kind of object (like tables,stored procedures,views) where the term need to be looked up, in the next drop down choose the database where term has to be searched. Once you start typing the word like product, the tool starts displaying objects that contain the term. When you click on the detail row, if the detail row is a table the control shifts to the object explorer where the table is present, if the row is a stored procedure or trigger, the text containing the term is displayed in the bottom window. In a nutshell SQL Search is a very helpful and intutive tool for database developers and administrators.

Monday, February 1, 2010


When working with designing and developing ETL processes, one of the important aspects that needs to factored in is the auditing of the ETL process. It is very important to keep track of the flow of process with in ETL. While working with SSIS, it is important to design auditing feature for a SSIS package. There are several ways of auditing SSIS packages, one is use to the SQL Jobs history to figure out if there was an error in the SSIS job step and then troubleshoot the problem. This might not effective way to audit, the other option would be is to create an audit table with in a sql server database and keep inserting records into his table at he beginning and completion of each step. This could be tedious, since there could be lot of calls to the same stored procedure using multiple execute sql tasks. One a new SSIS package is being created, SSIS itself provides logging capabilities. The logging option can be seen by clicking anywhere on the design surface of the SSIS package. When you click on the logging option, the following window pops up, there will be SSIS package and the various tasks in SSIS package on the left. On the right hand side within the window you have two tabs Providers and Logs, Details. When you choose the Provider type there are different types available:

SSIS log provider for SQL Server,
SSIS log provider for Windows Event Log
SSIS log provider for XML Files
SSIS log provider for Text Files
SSIS log provider for Sql Server Profiler
For my project I choose the SSIS log provider for SQL Server, Once this is done check the box next to the option SSIS log provider for SQL Server, then  choose the database connection where the logging will happen, when SSIS package runs it creates a table called sysssislog. In the details tab, choose the Events that need to be tracked, look at the figure below for reference.

Once this is done, click the OK button, Logging is now enabled for the SSIS package. Once the package is executed, look for the table sysssislog in the System databases under the database which was chosen in the Configuration column in the Provider and Logs tab. The sysssislog table has columns called source (contains the name of each task which is executed), message which contains more detailed message about the task,event (which contains the event that were chosen to be audited). The table also has columns called starttime and endtime which indicates the start and end time of each task.
SELECT * FROM dbo.sysssislog
To summarise, using the Logging option in SSIS, one can audit SSIS packages very effectively.