question

test32 avatar image
test32 asked

What are some Best practices to test SQL Server Database?

I am doing Database testing of a web application built in ASP.NET. Till now i only check whether UI input is getting saved in DB? what else should i check?
sql-server-2012databasetestingtest
2 comments
10 |1200 characters needed characters left characters exceeded

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

Beyond appropriate data handling by the UI, what would you consider to be important features within the database that could be subject to future errors? I like @ThomasRushton's recovery and security checks. Are you looking for something along the lines of that, or more like database unit testing?
1 Like 1 ·
You have some good answers below. This web site runs by you voting and marking the best answers. For each helpful answer below, indicate this by clicking on the thumbs up symbol next to those answers. If any one answer lead to a solution, indicate this by clicking on the check mark next to that answer. This helps indicate your solution for anyone else with the same question.
0 Likes 0 ·
ThomasRushton avatar image
ThomasRushton answered
Some suggestions: * Check that you can recover the database if it all goes wrong. * Check your documentation of the database reflects the reality. * Check that you can recover the database if it all goes wrong. * Check that, where you're granting access to the database, you're granting it on the principle of "least required permissions" - ie not granting sysadmin rights when the application only needs to read data. Yes, I know I repeated one, but it's rather important.
10 |1200 characters needed characters left characters exceeded

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

Grant Fritchey avatar image
Grant Fritchey answered
Load the database with data and run your application against that. Make darned sure you capture performance and query metrics so that you know how the system behaves. Way too many testing regimes use little to no data and then are shocked when the 50million rows they knew were coming in production actually causes problems. Take your anticipated data load and then double or triple it. You should also look to Distributed Replay, a tool within SQL Server, as a mechanism for capturing tests from your app or even from production, and then using that as a way to run more tests. Basically, you need functional tests (the most common and the least missed), performance tests (validation of performance), and load tests (lots of data, lots of transactions, similar to, but separate from performance, validates when things are going to break). This is what I set up for systems that I know I have to be able to ensure will work when they hit production.
10 |1200 characters needed characters left characters exceeded

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

Tom Staab avatar image
Tom Staab answered
My answer goes beyond just the database, but I break down system requirements and testing into a few levels (ranked from what I consider most important to least): 1. Requirements documentation. Does the end result meet the requirements? If not, stop here. 2. Data integrity. This is what you've focused on so far. If the database doesn't store the correct data, nothing else matters. 3. Security. Depending on the customer and application, this might sometimes be a lower priority, but I generally list it here. 4. Stability. Ensuring the application is operational is obviously important. This step requires more communication with the operations support team because it can involve things like redundancy as well as recovery (for when stability fails). 5. Performance. What are the customer expectations? Are they being met? Are they reasonable? Use standard tools and metrics. 6. Happy to Glad. These are lesser (and often undocumented) requirements. Is the background color shade correct? Do you like the text font? Don't assume the initial test values will be true in production and/or over time. Establish a testing plan to ensure all of your goals are continuously met.
10 |1200 characters needed characters left characters exceeded

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

Write an Answer

Hint: Notify or tag a user in this post by typing @username.

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.