Testing Tips

One of the Edgewood Solutions team members recently spoke at a testing conference held in Washington, DC by the name of Verify. During the presentation, a few different questions were asked about automating database testing with the native SQL Server 2005 tool set. In this tip, we will outline some options to automate database testing and verification with Profiler, SQLCMD and SQL Server Integration Services.

When conducting performance testing and tuning on a new system, most of the time a number of options are outlined to potentially correct the performance problem. To determine the best overall solution, each option is tested and the results are recorded. As lessons are learned options may be combine for a better end result and often as data is cached the overall query performance improves. Unfortunately, with the data in cache testing each subsequent option may lend itself to an apples to oranges comparison. How can I ensure during each execution of a new set of code that the data is not cached?

Sometimes when running processes there may be the need to create delays before the next step runs. This could be for processes that are run externally and therefore you have no control over when that process finishes, the need to mimic a delay in user response if you are doing testing or maybe you are collecting data at various intervals and want to delay the next collection time. These are just a few examples of the need to create a delay, so what approaches exist?

When diagnosing issues in SQL Server I've found that sometimes I need to be able to mimic a user's session state when attempting to repeat an error they may be receiving. The smallest differences can completely change the outcome, so I need to ensure all the session settings (QUOTED_IDENTIFIER, ANSI_NULLS, and so forth) are identical between the production session and my test session. Is there an easy way to determine these settings with a single query?

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. In this tip, we look at how to use the tools in Visual Studio to generate database test data.

I need to setup a virtual environment for development and testing. I've heard about a new feature in Windows 7 and Windows Server 2008 R2 called boot from a virtual hard disk (VHD) which allows you to create multiple VHDs and select them from the boot menu on startup without having to partition your hard drive to achieve multi-boot. I have a need for SharePoint and SQL Server test and development environments. Can you provide the details of how to set this up?

If you develop new functionalities for your SQL database then probably you already encountered the typical problem of testing large scale databases. I have to run a series of performance and functional tests on a database with a few million rows, but I do not have the necessary test data. I heard about some excellent commercial tools but they are expensive and my company cannot afford them. How can I generate the test data on my own and populate it to the tables on the test database?

I am the only SQL Server DBA at my organization and I feel like I am constantly pushing out code. I have Developers send me emails, I get help desk tickets, I have to go out to file shares and VSS to check for code that needs to get pushed out. I have been at a point where I am not able to get my other work done which is becoming frustrating. The other issue that I have is that I am unable to track the changes over time. With 4 different places that I need to check for code, this has become a nightmare that I have to get in control and soon. How should I go about doing this?

In a recent tip, you outlined steps for code deployment (Code Deployment Best Practices). That is a good first step to get a process in place to work with the team and set expectations. As a DBA, I need to find out the technical steps I should take when reviewing T-SQL code. Can you provide me with a checklist or recommendations to work through this process?

In our organization, I have noticed that database requirements are never included as a portion of the system requirements. The requirements always focus on the interface and we derive the database design from the interface as well as fill in some of the gaps. For some developers that process seems to yield a decent product, but not always. I think if we requested database requirements from both the business and technical management our overall offerings would be much better and we would have less patch\fix cases. For the requirements in our environment I have a couple of ideas in mind, but I am hoping you can give me a broader view of the situation with respect to overall SQL Server database requirements.

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 tip we cover how you can do this in Visual Studio.

Testing database applications for many organizations is a challenging task that is difficult to setup, automate and validate. Luckily, more time is getting allocated into development projects to conduct more rigorous testing. In this tip we will outline some of the realities to perform SQL Server 2000 and 2005 testing and opportunities to streamline the process.

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. In this tip, we look at how to use the tools in Visual Studio to generate database test data.

I need to setup a virtual environment for development and testing. I've heard about a new feature in Windows 7 and Windows Server 2008 R2 called boot from a virtual hard disk (VHD) which allows you to create multiple VHDs and select them from the boot menu on startup without having to partition your hard drive to achieve multi-boot. I have a need for SharePoint and SQL Server test and development environments. Can you provide the details of how to set this up?

When conducting performance testing and tuning on a new system, most of the time a number of options are outlined to potentially correct the performance problem. To determine the best overall solution, each option is tested and the results are recorded. As lessons are learned options may be combine for a better end result and often as data is cached the overall query performance improves. Unfortunately, with the data in cache testing each subsequent option may lend itself to an apples to oranges comparison. How can I ensure during each execution of a new set of code that the data is not cached?

If you develop new functionalities for your SQL database then probably you already encountered the typical problem of testing large scale databases. I have to run a series of performance and functional tests on a database with a few million rows, but I do not have the necessary test data. I heard about some excellent commercial tools but they are expensive and my company cannot afford them. How can I generate the test data on my own and populate it to the tables on the test database?

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 tip we cover how you can do this in Visual Studio.

In a recent tip, you outlined steps for code deployment (Code Deployment Best Practices). That is a good first step to get a process in place to work with the team and set expectations. As a DBA, I need to find out the technical steps I should take when reviewing T-SQL code. Can you provide me with a checklist or recommendations to work through this process?

In our organization, I have noticed that database requirements are never included as a portion of the system requirements. The requirements always focus on the interface and we derive the database design from the interface as well as fill in some of the gaps. For some developers that process seems to yield a decent product, but not always. I think if we requested database requirements from both the business and technical management our overall offerings would be much better and we would have less patch\fix cases. For the requirements in our environment I have a couple of ideas in mind, but I am hoping you can give me a broader view of the situation with respect to overall SQL Server database requirements.

One of the Edgewood Solutions team members recently spoke at a testing conference held in Washington, DC by the name of Verify. During the presentation, a few different questions were asked about automating database testing with the native SQL Server 2005 tool set. In this tip, we will outline some options to automate database testing and verification with Profiler, SQLCMD and SQL Server Integration Services.

When diagnosing issues in SQL Server I've found that sometimes I need to be able to mimic a user's session state when attempting to repeat an error they may be receiving. The smallest differences can completely change the outcome, so I need to ensure all the session settings (QUOTED_IDENTIFIER, ANSI_NULLS, and so forth) are identical between the production session and my test session. Is there an easy way to determine these settings with a single query?

I am the only SQL Server DBA at my organization and I feel like I am constantly pushing out code. I have Developers send me emails, I get help desk tickets, I have to go out to file shares and VSS to check for code that needs to get pushed out. I have been at a point where I am not able to get my other work done which is becoming frustrating. The other issue that I have is that I am unable to track the changes over time. With 4 different places that I need to check for code, this has become a nightmare that I have to get in control and soon. How should I go about doing this?

I need to setup a virtual environment for development and testing. I've heard about a new feature in Windows 7 and Windows Server 2008 R2 called boot from a virtual hard disk (VHD) which allows you to create multiple VHDs and select them from the boot menu on startup without having to partition your hard drive to achieve multi-boot. I have a need for SharePoint and SQL Server test and development environments. Can you provide the details of how to set this up?

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. In this tip, we look at how to use the tools in Visual Studio to generate database test data.

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 tip we cover how you can do this in Visual Studio.

If you develop new functionalities for your SQL database then probably you already encountered the typical problem of testing large scale databases. I have to run a series of performance and functional tests on a database with a few million rows, but I do not have the necessary test data. I heard about some excellent commercial tools but they are expensive and my company cannot afford them. How can I generate the test data on my own and populate it to the tables on the test database?

When diagnosing issues in SQL Server I've found that sometimes I need to be able to mimic a user's session state when attempting to repeat an error they may be receiving. The smallest differences can completely change the outcome, so I need to ensure all the session settings (QUOTED_IDENTIFIER, ANSI_NULLS, and so forth) are identical between the production session and my test session. Is there an easy way to determine these settings with a single query?

In our organization, I have noticed that database requirements are never included as a portion of the system requirements. The requirements always focus on the interface and we derive the database design from the interface as well as fill in some of the gaps. For some developers that process seems to yield a decent product, but not always. I think if we requested database requirements from both the business and technical management our overall offerings would be much better and we would have less patch\fix cases. For the requirements in our environment I have a couple of ideas in mind, but I am hoping you can give me a broader view of the situation with respect to overall SQL Server database requirements.

Sometimes when running processes there may be the need to create delays before the next step runs. This could be for processes that are run externally and therefore you have no control over when that process finishes, the need to mimic a delay in user response if you are doing testing or maybe you are collecting data at various intervals and want to delay the next collection time. These are just a few examples of the need to create a delay, so what approaches exist?

One of the Edgewood Solutions team members recently spoke at a testing conference held in Washington, DC by the name of Verify. During the presentation, a few different questions were asked about automating database testing with the native SQL Server 2005 tool set. In this tip, we will outline some options to automate database testing and verification with Profiler, SQLCMD and SQL Server Integration Services.

When conducting performance testing and tuning on a new system, most of the time a number of options are outlined to potentially correct the performance problem. To determine the best overall solution, each option is tested and the results are recorded. As lessons are learned options may be combine for a better end result and often as data is cached the overall query performance improves. Unfortunately, with the data in cache testing each subsequent option may lend itself to an apples to oranges comparison. How can I ensure during each execution of a new set of code that the data is not cached?

In a recent tip, you outlined steps for code deployment (Code Deployment Best Practices). That is a good first step to get a process in place to work with the team and set expectations. As a DBA, I need to find out the technical steps I should take when reviewing T-SQL code. Can you provide me with a checklist or recommendations to work through this process?