How to refresh Test, Dev Systems with Prod Data?

When I attended PASS Summit last October, there were many people whom I met during breakfast and lunch and sometimes in the lobby and we discussed a variety of topics on  SQL Server. During one of those conversation, I was asked to give my suggestion on how to refresh their Development and Test environments with the Production data. So I thought it would be nice to write the same, so that it might help some of those who are looking for the same info. 

Backup & Restore

One of the most common mechanism of refreshing environments is using database backups on the production server and restore on the DEV, QA server to refresh the existing one. The advantage of using backup / restore is it is easy to do it. You don’t have to perform any special steps in this procedure other than restoring, coz your most recent full backup of production database should be good. However, the downside of this is that your DEV / QA database would be the same size of the production and your DEV / QA machine might not be anywhere near the size of your PROD box. The disk space too might be a factor to consider when using this procedure.

SQL Server Integration Services (SSIS)

If you are aware and comfortable using SSIS packages, you can utilize this platform to refresh and  make other customization after your DEV / QA database is refreshed. With SSIS, you can automate the database refresh and setup a SQL Agent job and when you have to refresh you can run the job manually (or on a pre-determined schedule).. Some of the benefits using SSIS is the manual tasks that might be needed after you refresh the data, such as scrubbing off critical data (such as SSNs or Credit Card data etc), adding additional Developer / Test users etc.. You might also not want all of the data from your production databases, only the most recent year’s worth of data is good to test, in such cases backup restore will not solve the purpose, the data that is not necessary has to be dropped after restoring the database backup…

Import Export Wizard

Import Export Wizard would be another handy tool that can be used for data refresh, using this tool does not need any other program (such BIDS or SSDT as in the case of SSIS). You can pick only those table(s) that needed in the DEV / QA environment.. The disadvantage of this tool is that you cannot customize beyond a certain level and scheduling is not an option unless you save the SSIS package at the end of this wizard.. Also, you cannot push data simultaneously to 2 different databases such as DEV and QA at the same time (where as in SSIS you can do so using Multi-Cast Data Transformation).. 

Other options

Finally there are other options such as Scripting the database objects and transferring the data using stored procedures at either the source or destination (which involves a linked server between the source and destination servers) and / or command line utility called Bcp. 

Overall, I would prefer SSIS as my choice to refresh QA / Dev databases..

Do you like this site? Like our FB page @ Facebook.com\LearnSQLWithBru so that, you know when there is a new blog post.

–Bru Medishetty

SSIS – Creating a new folder and deploying package in that folder

In this blog we shall learn how to create a folder in MSDB using SSMS and deploying a package in that folder. This would be beneficial when there are a large number of SSIS packages deployed on your SSIS Server. Having separate folders enables you to keep all SSIS packages related to a specific group / department as one group and makes it easier for managing them.

Creating a folder

First connect to the SSIS instance using SSMS. Expand the node Stored Packages and then expand MSDB. The below screen shot displays such an example. Note that Accounting folder is expanded to display the list of folders currently available. We would be creating a new folder in Accounting.

On the folder under which you would like to create a new folder (in this example Accounting folder) right-click and choose New Folder from the pop-up menu.

Create New Folder dialog box is displayed, when you can enter the name of the folder that we would like to create. Click OK to create the folder.

Once the folder is created, you can check in SSMS if you see the folder name under the parent folder.

Deploying the package in a folder

Once you are done with creating the folder, then you can start deploying your SSIS packages to the new folder. For step by step instructions on how to deploy SSIS packages, please read one of my earlier blogs here. In this blog we shall only look at the step that matters.

When deploying the package, in the step where you have to choose the SQL Server Target where the SSIS package is to be deployed, type the SQL Server name and then under Package Path, click the ellipses button to display the folder structure of that SQL Server and navigate to the destination folder.

Select that folder and click OK..  

Click Next and continue deployment of the SSIS package.

Do you like this site? Like our FB page @ Facebook.com\LearnSQLWithBru so that, you know when there is a new blog post

— Bru Medishetty

List of SSIS Error Messages

Today, when I was working on SSIS packages, I got an error which as often in SSIS is not clear and descriptive. I searched online for the description and explanation and found this link in MSDN. http://msdn.microsoft.com/en-us/library/ms345164.aspx

The page contions all messages in SSIS (be it Error or Warning or Informational etc). It contains the error cumber and what does it mean.. Hope it helps you too in finding the explanation/ description for your error/ warning code.

Do you like this site? Like our FB page @ Facebook.com\LearnSQLWithBru so that, you know when there is a new blog post.

– Bru Medishetty

Code page warning in SSIS

This article tries to address the code page warnings. This is a common warning encountered in SSIS packages especially when using OLE DB Sources.  The warning is a way to alert the user that SSIS is unable to fetch the Code page of the data source.  The picture shown below is displayed when the preview button is clicked to see preview the source data.

When you click on OK in the above dialog box, the preview is displayed, after previewing, you move on and finish saving the control flow component. The warning message is still displayed on the component with a yellow triangular symbol. The picture shown below is how it displays the tool top message when the cursor is moved over the data flow component.

In order to suppress this warning message, right click on the data flow component and select property. This will ensure the properties window appear, if it has been closed / hidden. In the properties window of the data flow component, under Custom Properties, change the default value False to True for the property AlwaysUseDefaultCodePage (picture below) and save the package. The warning message disappears.

Do you like this site? Like our FB page @ Facebook.com\LearnSQLWithBru that let’s you know when there is a new blog post.

Bru Medishetty

Configure logging in SSIS packages

In this article we will look at the steps to enable logging in SSIS packages. Each control flow task in a package have multiple events  and logging enables you to look the execution details of these events.

Steps to configure logging

Open the package in Business Intelligence Development Studio (BIDS), see that you are in the design mode. When you are in the Control Flow, right click (do not right click on the control flow tasks) and select Logging from the drop menu displayed (picture below).

A dialog box Configure SSIS Logs is displayed. In the left hand pane, there is a tree view is displayed. Select the package by selecting the check box corresponding to it (picture below). You can check individual tasks also.

Upon selecting the package or task, you can then configure logging through the available logging providers from the drop down list as shown below. You can add multiple logs of the same type and/or another type. In our example we will look at selecting only one log provider and that is SSIS log provider for Text Files. After selecting the log provider, click on Add button.

Once the Log type is selected and added, the dialog box looks like the picture below. Choose the log file by selecting the check box to the left of it and go to configuration column to configure the location of the log file in our example it is a text file.

There would be a drop down list when you go to the configuration column, under which you would get a <new connection> listed, choose that and it will open a small window which would be similar to the one shown below.

Choose create file in the usage type and click browse button.. It would open a dialog box and we need to navigate to the directory where the SSIS package log file will be created. I am choosing the default Log directory of that instance here. (picture below) 

After choosing the location and the name of the file to be used, select Open button in the current dialog box that would take back to the previous dialog, select OK to configure the file location. Now we are all set, except the events that would be logging into this log file. To select the events, switch to the details tab as show below. Choose the events which needs to be logged into the log file. Choosing the events selectively is important, since we do not want too much of information is written into the log file, making it difficult to find information when needed. I always choose OnError and OnTaskFailed events for every task and some additional events in case of Data Flow tasks.

Continue to click a series of OK buttons to have the logging configured.

Do you like this site? Like our FB page @ Facebook.com\LearnSQLWithBru so that, you know when there is a new blog post.

–Bru Medishetty