Anatomy SQL Server 11th automated tests on multiple versions of SQL Server

Anatomy of SQL Server 11th automated testing of multiple versions of SQL Server. dkautomated-testing-of-orcamdf-against-multiple-SQL-server-versions since I released OrcaMDFStudio, I have realized the differences between some system tables between SQL2005 and SQL2008. These differences

Anatomy SQL Server 11th automated testing of multiple versions of SQL Server since I released OrcaMDF Studio, I have realized the differences between some system tables between SQL2005 and SQL2008. These differences

Anatomy of SQL Server 11th automated testing of multiple versions of SQL Server)

Http://improve.dk/automated-testing-of-orcamdf-against-multiple-sql-server-versions/

Since I released OrcaMDF Studio, I have realized the differences between SQL2005 and SQL2008 in some system tables.

These differences cause OrcaMDF parsing to fail because the code is in the 2008 R2 format.

When SQL2005 compatibility is required, I gradually realized that I need to expand the test coverage of multiple SQLSERVER versions to replace the previous test on only one version.

In addition, I also need to perform specific tests on features of some specific versions (for example, sparse column tests can only run in SQLSERVER2008 or later versions)

NUnit TestCaseSourceAttribute solves the problem

NUnit supports inline parameter testing through the TestCase attribute. Better yet, we can also provide parameter data for dynamic test cases, using the TestCaseSource attribute and TestCaseSource attribute.

First, I implemented a simple enumeration to cover the versions supported by my current work:

public enum DatabaseVersion{    SqlServer2005,    SqlServer2008,    SqlServer2008R2,}

Then I created the sqlservertestattriceclass and inherited it from TestCaseSourceAttribute, just like this:

public class SqlServerTestAttribute : TestCaseSourceAttribute{    private static IEnumerable
 
   versions    {        get        {            foreach (var value in Enum.GetValues(typeof(DatabaseVersion)))                yield return new TestCaseData(value).SetCategory(value.ToString());        }    }    public SqlServerTestAttribute() : base(typeof(SqlServerTestAttribute), "versions")    { }}
 

The sqlservertestattriceclass tells TestCaseSourceAttribute to find the source data of the test case in the private static version attribute.

All DatabaseVersion values are listed in the version attribute and returned one by one -- make sure that the test category is set to the name of the DatabaseVersion value.

Next, I will convert the current Test to use the new SqlServerTest attribute to replace the previous vanilla NUnit Test attribute:

[SqlServerTest]public void HeapForwardedRecord(DatabaseVersion version){    ...}

This will cause all my tests to run once based on each enumerated value in the DatabaseVersion enumeration and automatically obtain each value in the input version parameter.

Support different development environments
Now, I don't want to force everyone to install all versions of SQL Server-they may just want the software to support SQL Server 2005 & 2008R2. In the OrcaMDF. Core. Tests project, I defined connection strings for each supported test database, just like this

     
      "SqlServer2005" connectionString="Data Source=.SQL2005;Integrated Security=SSPI"/>    "SqlServer2008R2" connectionString="Data Source=.;Integrated Security=SSPI"/>
 

If a database does not have a corresponding connection string (the DatabaseVersion enumeration value corresponding to the name), the test will not run. Because of this, I currently ignore SQL Server 2008 and only Install SQL 2005 and SQL 2008R2 on my machine.

To filter out currently available databases, I modified my test cases so that the base class can run the actual test and use the lambda expression:

[SqlServerTest]public void HeapForwardedRecord(DatabaseVersion version){    RunDatabaseTest(version, db =>    {        var scanner = new DataScanner(db);        var rows = scanner.ScanTable("HeapForwardedRecord").ToList();        Assert.AreEqual(25, rows[0].Field
 
  ("A"));        Assert.AreEqual("".PadLeft(5000, 'A'), rows[0].Field
  
   ("B"));        Assert.AreEqual(28, rows[1].Field
   
    ("A"));        Assert.AreEqual("".PadLeft(4000, 'B'), rows[1].Field
    
     ("B"));    });}
    
   
  
 

This RunDatabase method is declared in the SqlServerSystemTestBase class.

protected void RunDatabaseTest(DatabaseVersion version, Action
 
   test){    string versionConnectionName = version.ToString();    // Only run test for this version if a connection string has been provided    if (ConfigurationManager.ConnectionStrings[versionConnectionName] == null)        Assert.Inconclusive();    // Setup database and store file paths, if we haven't done so already    ensureDatabaseIsSetup(version);    // Run actual test    using (var db = new Database(databaseFiles[version]))        test(db);}
 

If the corresponding connection string is not declared in the configuration file, we will discard the test and mark it as uncertain-based on the current configuration, we cannot run it at all.

Next, ensureDatabaseIsSetup () executes the common configuration code (For details, refer to previous articles)
Although each database version needs to be executed for each firmware test at this time. The last OrcaMDF instance will be created and passed in the actual test parameters.

Supports features of different SQLSERVER versions
As mentioned above, I need to perform a test method for a specific SQLSERVER version.
Standard sqlservertestattriation automatically enumerated values in all DatabaseVersion enumeration, but I have no reason to create another sqlserver2005testattriation, just like this

public class SqlServer2005TestAttribute : TestCaseSourceAttribute{    private static IEnumerable
 
   versions    {        get        {            yield return new TestCaseData(DatabaseVersion.SqlServer2005).SetCategory(DatabaseVersion.SqlServer2005.ToString());        }    }    public SqlServer2005TestAttribute() : base(typeof(SqlServer2005TestAttribute), "versions")    { }}
 

What if I want to run the test on SQL Server 2008?

public class SqlServer2008PlusTestAttribute : TestCaseSourceAttribute{    private static IEnumerable
 
   versions    {        get        {            foreach (var value in Enum.GetValues(typeof(DatabaseVersion)))                if((DatabaseVersion)value >= DatabaseVersion.SqlServer2008)                    yield return new TestCaseData(value).SetCategory(value.ToString());        }    }    public SqlServer2008PlusTestAttribute()        : base(typeof(SqlServer2008PlusTestAttribute), "versions")    { }}
 

Once attributes is available, it is easier to run a separate test for our version.

[SqlServer2008PlusTest] public void ScanAllNullSparse (DatabaseVersion version) {RunDatabaseTest (version, db => {var versions = new DataScanner (db); var rows = tables. scanTable ("ScanAllNullSparse "). toList (); // sparse column Assert. areEqual (null, rows [0]. field
 
  
("A"); Assert. AreEqual (null, rows [0]. Field
  
   
("B "));});}
  
 

Support for ReSharper test runner
To run the test, we need ReSharper 6.0 because ReSharper 5.1 does not support TestCaseSource attribute.
Once you perform the test, you will see the following test results (SQL Server 2005 & 2008 R2 testing is supported)

Each test case automatically tests DatabaseVersion of multiple versions (except for the parsing test, because it does not implement SqlServerSystemTestBase, it cannot run multi-version tests ).

Most tests on SQL Server 2005 fail because I did not support SQL Server 2005. When I run the test, all SQL2008 tests are inconclusive.

Finally, all tests for SQL2008R2 have passed

Test Filter
Obviously, we cannot always test all versions of SQLSERVER, which is a waste of time. One way to disable testing for a specific version is to delete the connection string.

However, this will still produce unclear output, and it will be very troublesome to always modify the configuration file.

Unfortunately, ReSharper test runner does not support category Filtering for parameterized tests created using TestCaseSourceAttribute.
I have enabled a feature requirement on YouTRACK. I hope the ReSharper Team will consider adding this feature to ReSharper6.1. If you think this feature is great, please vote for it.

Fortunately, NUnit test runner does not support this filtering. Open OrcaMDF. Core. Tests program assembly in NUnit test runner to give you the following results:

Note: Before we run the test, how does Nunit know the parameterized test parameters! At the same time, you also need to pay attention to how Nunit enables DifferingRecordFormats to run only SQL2008 or above,

While the FGSpecificClusteredAllocation test only allows the system to run in SQL2005 or above.

By explicitly selecting a specific version category, we can choose to run some versions. Once running, the small dots in the header of the unselected category will become gray.

It can be noted that the running time is 89 seconds, basically one test per second, and 98% of the time is spent on testing the lob Type feature.
Because of the category format, I can select the main test categories, so that I can easily filter out long-running test projects and focus only on the categories that are quickly completed.

The lob type needs to be tested in particular, because before the database is started, they involve a large number of disk activities, creating configuration tables and configuration rows.

Future Prospects
Adding a new version is as simple as installing SQLSERVER. Add a connection string to the configuration item, and add the SQLSERVER version name to the DatabaseVersion enumeration. That's all.

Further, to some extent, I need to test multiple upgrade methods in order. Based on my own tests, a database upgraded from SQL Server 2005 to SQLSERVER2008 R2

It may be different from the database created locally in SQLSERVER2008 R2, or from SQL2008 to SQL2008R2. Therefore, I need to test various upgrade methods to ensure full compatibility.

However, the priority of the compatibility test is relatively low in my test priority list, because these compatibility tests will take a lot of time

11th