{"id":317,"date":"2014-10-14T08:57:44","date_gmt":"2014-10-14T07:57:44","guid":{"rendered":"http:\/\/www.codereview.co\/?p=317"},"modified":"2014-10-14T09:57:56","modified_gmt":"2014-10-14T08:57:56","slug":"testing-options-in-sql-server-2005-and-2000","status":"publish","type":"post","link":"http:\/\/www.codereview.co\/index.php\/sql-server-tutorials\/testing-options-in-sql-server-2005-and-2000\/","title":{"rendered":"Testing options in SQL Server 2005 and 2000"},"content":{"rendered":"<p><span style=\"color: #000000;\"><strong>Scenario<\/strong><\/span><\/p>\n<p><span style=\"color: #000000;\">Testing database applications for many organizations is a challenging task that is difficult to setup, automate and validate.\u00a0 Luckily, more time is getting allocated into development projects to conduct more rigorous testing.\u00a0 In this tip we will outline some of the realities to perform SQL Server 2000 and 2005 testing and opportunities to streamline the process.<\/span><\/p>\n<p><span style=\"color: #000000;\"><strong>Solution<\/strong><\/span><\/p>\n<p><span style=\"color: #000000;\">SQL Server 2000 and 2005 offer a number of opportunities to perform application testing to include the following items:<\/span><\/p>\n<p><span style=\"color: #000000;\"><strong><em>People, Process and Technology<\/em><\/strong><\/span><\/p>\n<p><span style=\"color: #000000;\">The reality is that people, processes and technology are needed to conduct proper testing.\u00a0 Taking any one item out of the equation will not result in the expected outcome.\u00a0 So be sure to impress upon your team that it is necessary to have an equal amount all of three components for success.\u00a0 It is difficult for a great piece of technology to solve a difficult problem without highly talented people and a comprehensive process.<\/span><\/p>\n<p><span style=\"color: #000000;\"><strong><em>SQL Server Instances<\/em><\/strong><\/span><\/p>\n<p><span style=\"color: #000000;\">Both SQL Server 2000 and 2005 support\u00a0a single default instance and 15 named instances on the same server.\u00a0 What this means is that with per CPU licensing up to 16 instances can exist on a single server without incurring any additional licensing costs.\u00a0 If your testing is encapsulated at a SQL Server instance level, then up to 16 versions of the code can exist on 1 server.<\/span><\/p>\n<p><span style=\"color: #000000;\">With SQL Server 2005, additional instance options are available for other portions of the application, i.e. Analysis Services.<\/span><\/p>\n<p><span style=\"color: #000000;\">For more information visit:<\/span><\/p>\n<ul>\n<li><span style=\"color: #000000;\">Multiple Instances of SQL Server<\/span><\/li>\n<li><span style=\"color: #000000;\">Working with Named and Multiple Instances of SQL Server 2000<\/span><\/li>\n<li><span style=\"color: #000000;\">File Locations for Default and Named Instances of SQL Server 2005<\/span><\/li>\n<\/ul>\n<p><span style=\"color: #000000;\"><strong><em>SQL Server 2005 Express Edition<\/em><\/strong><\/span><\/p>\n<p><span style=\"color: #000000;\">Another option for testing is leveraging the SQL Server 2005 Express Edition which is a free copy of the database engine and Reporting Services with limitations on the number of CPUs, memory usage, etc.\u00a0 This version offers a fully featured GUI and all of the bells and whistles.\u00a0 This means that this edition gives you the opportunity to perform functional testing of the application on multiple independent machines at a very reasonable cost.\u00a0<\/span><\/p>\n<p><span style=\"color: #000000;\"><strong><em>Virtual Machines<\/em><\/strong><\/span><\/p>\n<p><span style=\"color: #000000;\">If the testing incorporates SQL Server and other components installed in Windows, then multiple SQL Server instances may be helpful, but they are not the solution because DLL&#8217;s may need to be redeployed for each test or build, limiting the capabilities for parallel testing.\u00a0 Leveraging the Express edition may be an option if many machines are available, but this too can quickly become unwieldy.\u00a0 One recommendation is to leverage Microsoft Virtual Server or VMWare as a means to have multiple virtual instances of Windows with SQL Server, IIS, your application, etc. all residing on 1 physical server.\u00a0 This results in the opportunity to have many independent and fully featured instances of Windows and with multiple code bases.<\/span><\/p>\n<p><span style=\"color: #000000;\"><strong><em>Synonyms<\/em><\/strong><\/span><\/p>\n<p><span style=\"color: #000000;\">If you are familiar with other database platforms, then synonyms should be no stranger.\u00a0 With SQL Server 2005, Microsoft introduced synonyms as a means to have a single name of an object reference a different underlying object in another schema or database or even on another SQL Server.\u00a0<\/span><\/p>\n<p><span style=\"color: #000000;\">Check out this information as a synonyms primer:<\/span><\/p>\n<ul>\n<li><span style=\"color: #000000;\">CREATE SYNONYM (Transact-SQL)<\/span><\/li>\n<li><span style=\"color: #000000;\">Using Synonyms (Database Engine)<\/span><\/li>\n<\/ul>\n<p><span style=\"color: #000000;\">*** NOTE *** &#8211; Synonyms are not available in SQL Server 2000.<\/span><\/p>\n<p><span style=\"color: #000000;\"><strong><em>Views<\/em><\/strong><\/span><\/p>\n<p><span style=\"color: #000000;\">Since synonyms are not available in SQL Server 2000, one potentially viable option is to leverage VIEWS as a means to have a single name that can reference another object.<\/span><\/p>\n<p><span style=\"color: #000000;\"><strong><em>DDL Triggers<\/em><\/strong><\/span><\/p>\n<p><span style=\"color: #000000;\">With many scripts underlying objects need to be created, altered or dropped.\u00a0 Depending on how the code is written and executed, it may be difficult to determine if these commands were successful or not.\u00a0 One way to fill this gap in SQL Server 2005, is by leveraging the new DDL triggers.\u00a0 These can be created to notify the team as the code completes and the objects are created, altered or dropped.<\/span><\/p>\n<p>&nbsp;<\/p>\n<p><span style=\"color: #000000;\"><strong>Thanks for reading this article,<\/strong><\/span><\/p>\n<p><span style=\"color: #000000;\"><strong>Next steps :<\/strong><\/span><\/p>\n<ol>\n<li><span style=\"color: #000000;\"><b><strong>Add this article to your database toolkit<\/strong><\/b><\/span><\/li>\n<li><span style=\"color: #000000;\"><strong>Share this with your colleagues because Sharing is Learning<\/strong><\/span><\/li>\n<li><span style=\"color: #000000;\"><strong>Comment below if you need any assistance<\/strong><\/span><\/li>\n<\/ol>\n","protected":false},"excerpt":{"rendered":"<p>Scenario Testing database applications for many organizations is a challenging task that is difficult to setup, automate and validate.\u00a0 Luckily, more time is getting allocated into development projects to conduct more rigorous testing.\u00a0 In this tip we will outline some of the realities to perform SQL Server 2000 and 2005 testing and opportunities to streamline [&hellip;]<\/p>\n","protected":false},"author":2,"featured_media":0,"comment_status":"open","ping_status":"open","sticky":false,"template":"","format":"standard","meta":[],"categories":[32,3],"tags":[171,13,12,170],"_links":{"self":[{"href":"http:\/\/www.codereview.co\/index.php\/wp-json\/wp\/v2\/posts\/317"}],"collection":[{"href":"http:\/\/www.codereview.co\/index.php\/wp-json\/wp\/v2\/posts"}],"about":[{"href":"http:\/\/www.codereview.co\/index.php\/wp-json\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"http:\/\/www.codereview.co\/index.php\/wp-json\/wp\/v2\/users\/2"}],"replies":[{"embeddable":true,"href":"http:\/\/www.codereview.co\/index.php\/wp-json\/wp\/v2\/comments?post=317"}],"version-history":[{"count":3,"href":"http:\/\/www.codereview.co\/index.php\/wp-json\/wp\/v2\/posts\/317\/revisions"}],"predecessor-version":[{"id":347,"href":"http:\/\/www.codereview.co\/index.php\/wp-json\/wp\/v2\/posts\/317\/revisions\/347"}],"wp:attachment":[{"href":"http:\/\/www.codereview.co\/index.php\/wp-json\/wp\/v2\/media?parent=317"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"http:\/\/www.codereview.co\/index.php\/wp-json\/wp\/v2\/categories?post=317"},{"taxonomy":"post_tag","embeddable":true,"href":"http:\/\/www.codereview.co\/index.php\/wp-json\/wp\/v2\/tags?post=317"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}