Learn more about SQL Server tools

mssqltips logo
 

Tutorials          DBA          Dev          BI          Career          Categories          Webcasts          Whitepapers          Today's Tip          Join

Tutorials      DBA      Dev      BI      Categories      Webcasts

DBA    Dev    BI    Categories

 

Third Party Application Checklist for SQL Server


By:   |   Read Comments (4)   |   Related Tips: More > Database Administration

New Whitepaper > 10 Ways We Can Steal Your Data >> download now


Problem

It's a pretty good bet that if you are a Database Administrator you are dealing with third-party vendors who have supplied databases for you to support within your environment. Just about all of us have to deal with a mix of in-house-developed and externally-supplied databases and the vendor sales, technical, and support staff that come along with them. Of course you hear the horror stories, however, there are good, even excellent companies out there producing great software running on Microsoft SQL Server.

When I started as a DBA many years ago I was never involved in the vetting process for new products we were looking at purchasing - for that matter, it is still quite frequent that the purchasing decisions in our organization are made well before the Information Technology department is even aware of such an initiative. What I've done is crafted a series of questions I provide to prospective (or already contracted) software vendors that will be supplying software solutions running on Microsoft SQL Server to complete as part of the vetting process or (as alluded to previously) the implementation process.

While many of these questions are quite often left blank by the smaller (or less knowledgeable) companies, most vendors are willing and capable of providing solid answers that will give the DBAs, Analysts, and Project Managers insight on how to architect the product's components, charge-back the budget for hardware, licensing, and SAN allocation, and possible load balancing if hosting the new database(s) on existing SQL Server instances.

The following list may not be complete for each of your organizations - it may be overkill for others - but in part or total, they initiate a dialog that enables the implementation process to proceed in a far smoother manner than without.

Solution

I've attempted to categorize the questions into logical units. These may not be appropriate to each of your environments, but I strongly suggest that you either create a standalone document to provide to your SQL Server development vendors or incorporate these questions into existing documentation you expect each of your software vendors to complete prior to purchase and/or implementation.

General Specifications

These questions speak to the general environment that must exist for the vendor's product to run successfully. Answers to these questions should give you a good idea from the start on whether you have an existing server with capacity that can host their database(s) or if you'll need to procure new hardware and secure additional licenses.

  • Version(s) of Microsoft SQL Server supported by the product.
  • Edition(s) of Microsoft SQL Server supported or required by the product.
  • Patch level currently supported.
  • Are there considerations that require the database to be run on a 32-bit platform? Is 64-bit supported? Typically this will only be an issue where the application does not allow for a distributed topology or there are application components (install or upgrade packages perhaps) that must reside on the server hosting the SQL databases.

Security

You need a solid understanding of the requirements for SQL or Windows-based logins for each aspect of the product that requires a unique security scheme. If the 'sa' login is used for any aspect of the installation or production usage I strongly recommend you do whatever you can to push the vendor to allow you to fashion a work-around that requires a lower-level connection, preferably never exceeding the db_owner database role.

  • Identify login and user requirements. Service accounts, differentiated groups for specific roles (administrative-level users versus perhaps reporting users) fall into this category. You should not be surprised by requests to create logins on your SQL Servers after the installation process has been completed.
  • Does the product support usage of a trusted security model such as Active Directory or Windows security or do they require a SQL login or logins be created?
  • Is security handled within the application, with perhaps only a single login assigned to the application?
  • Will the application installation program or supplied scripts attempt to create the logins and passwords and if so, will the DBAs be able to alter and strengthen them if any fail to meet the criteria of your environment?
  • Are any logins created, either for the installation or production use, required to have any server-level role rights such as Database Creator or System Administrator?
  • Does the vendor require a local Windows account on the SQL Server server?

Architecture

This is a fairly broad topic, but important nonetheless. It's imperative that you have knowledge of any internal or external components, high-availability requirements, instance-wide settings, or non-standard database-level settings documented in order to further determine whether you need to dedicate a server or instance to this product. Questions in this category also allow you to understand if additional teams in your IT department may need to be notified and involved in the installation, configuration, and support phases of this project. Additional environments for test, build, training, and development may add additional overhead that will require yet more hardware and space.

  • Does the vendor require their databases be hosted on a dedicated SQL Server or SQL Server instance? Is a shared instance acceptable?
  • If a shared instance is acceptable, what is the approximate percentage of customers running on a dedicated versus a shared instance for this product?
  • What is the initial size of the database that is created?
  • Based upon the projected business usage levels, what is the estimated annual growth to be expected in the database?
  • Do they typically run the database under Full, Simple, or Bulk-Logged recovery? (Note that it will ultimately be the decision of the customer to determine the level of recovery and acceptable limits of data loss.)
  • Are there any associated scheduled SQL Server Agent jobs to be created?
  • Are there any server-wide settings that may need to be taken into consideration that are "non-standard" (think of lessening the restrictions on secure settings in the Surface Area Configuration Tool if we're discussing SQL Server 2005)?
  • Is the database collation different than the default collation for the SQL Server version?
  • Is their application cluster-aware?
  • Are there any high-availability considerations to be aware of (log shipping, mirroring, replication, or clustering?)
  • Will there be any external interfaces either to or from this database that need to be considered?
  • Is there to be a dedicated test, development, build, or training environment for the product in addition to the production environment? What will be the lifespan of any of these ancillary environments?
  • How is the database installation and configuration performed? Is it integrated with the application install or will the DBA be supplied scripts to run? Does the installation need to be performed as the "sa" user? (If so, please raise a ruckus so loud and vehement that you're one step away from foaming at the mouth!)

Hardware Requirements

The vendor should have hardware specifications documents that they supply to you for review. Be sure that the specification fit the usage levels you expect. There is nothing worse than building a database server to the vendor's specifications only to find out in the short term that your performance is tanking because their specifications were for an database with 50% fewer users than your average usage levels realize. In many cases, the vendor may recommend dedicated LUNs or RAID 1+0 for log files and RAID 5 for tables and indexes. If your environment is similar to mine, you may only have massive RAID 5 SAN assets that can not be configured for optimal performance. Ensure that this does not void any performance standards that may be part of an SLA.

  • Based upon projected usage, the amount of RAM required?
  • Number of processors/cores and processor speed?
  • Local versus SAN-attached storage requirements?
  • RAID levels expected.

Performance Considerations

This may be the hardest category of questions to get vendors to answer. I perhaps see a 5-10% return rate on these questions in the 10 years I've been asking. Most often you'll find yourself requesting your implementation team to perform full stress testing of a test environment so that you can make your own benchmark observations. The questions still need to be answered, if not for your company than for the next one that purchases this product. If enough customers ask the difficult questions the vendors may eventually take heed and do benchmarking of their products.

  • Based upon projected usage patterns how many reads/writes are to be expected per second? (Is this product highly-transactional? If so is it read or write intensive?)
  • What are their expectations for maintenance windows?
  • Does the vendor have maintenance scripts they require to be run? (this calls us back to the SQL Agent Job question asked previously.)
  • If you have your own custom maintenance scripts and methodologies be sure that there is no overlap and inquire as to if they will certify that your processes do not conflict with support contracts or void said contracts.
  • Are there high and low activity points of use (is the activity cyclical); nightly data loads from a separate source, end-of-month processing, etc.?
  • What are the vendor's rules for allowing customer IT departments to alter indexes based upon monitoring of usage after deployment?
  • In further regard to indexing, what methodologies were used to determine index creation (field candidates, fill factor, etc.)
  • Based upon usage patterns does the vendor support allowing customer IT departments to move tables or indexes to different filegroups if tuning warrants doing so or will it void any SLAs in place?

Support

Or as I like to call it: "now what?" Will the vendor need to touch the databases after go-live? If so, do they expect to be able to have full access to the SQL Server itself? How do they handle remote issues with the databases? What is their expected response time for support? Do they offer 24-hour technical support? The database encounters a problem... now what?

  • Will the vendor require any access to the database during the installation process?
  • Will the vendor require any access to the database after go-live?
  • Will the access be limited to connecting to the database via SQL Server Management Studio on a separate workstation or over VPN from their own environment or will they need some level of access directly on the SQL Server hardware?
  • Does the vendor offer (and are we paying for around-the-clock technical support)?
  • What aspects of the database are the DBAs expected to support versus the vendor? Tuning? Index Maintenance? Backups?
  • What is the process by which the vendor diagnoses and resolves issues with the database? Are we expected to send compressed backups to the vendor for assessment or do they do their support remotely?
Next Steps
  • Next time you are looking at implementing or upgrading a third party application that runs on SQL Server consult this handy checklist to get as many answers ahead of time.
  • Also, if the vendor is telling you that you need to purchase the Enterprise Edition of SQL Server find out exactly what features they have implemented that require this version. If you do a little digging you may find out that the application will run fine on the Standard Edition of SQL Server.


Last Update:


signup button

next tip button



About the author
MSSQLTips author Tim Ford Tim Ford is a Senior Database Administrator and SQL Server MVP. He has been working with SQL Server since 1999.

View all my tips
Related Resources





Post a comment or let the author know this tip helped.

All comments are reviewed, so stay on subject or we may delete your comment. Note: your email address is not published. Required fields are marked with an asterisk (*).

*Name    *Email    Notify for updates 


SQL tips:

*Enter Code refresh code     



Monday, August 17, 2009 - 2:42:58 PM - Chuck Hottle Back To Top

This is a great checklist.  We've been talking about the need for something like this and your list will get us started on implementing it.  Thanks.


Friday, August 14, 2009 - 9:51:26 PM - gvernon Back To Top

This is not just for third-party applications but for other microsoft applications, such as Microsoft's SMS, which also says it does not need a local DBA to support the product.  After all during the SMS backup it stops the SQL Server service and copies the SMS databases along with the necessary SMS file infastructure.  What they forgot was if the SMS database is on a shared SQL Server where stopping the service could impact other applications and the need to backup the SQL System databases.


Wednesday, August 12, 2009 - 4:55:20 PM - aprato Back To Top

You may want to also throw in Microsoft into your blame list.  They were positioning SQL Server as a low maintenance
database solution ("Hey, you don't need a DBA").  

I was in a consulting position years ago where I was installing financial software at client sites.   Luckily, I had the db expertise to go in and make sure the suite would install and run smoothly. Once I installed, it was up to the client to maintain the database BUT there was rarely a DBA on staff who could supervise backups etc. It was usually a help desk or IT person with no training.  

Good times, indeed.


Wednesday, August 12, 2009 - 3:03:25 PM - Ray Tomlinson Back To Top

Hi Tim

This article has stepped on such a raw nerve I joined up to MSSQLTips  just to reply and thank you..

And copy the checklist verbatim..
 
I formally worked as an implementation consultant for an international software vendor in it’s UK division who started supporting SQL server at the 2000 edition for their solution database. I remember well asking for training on MS SQL at the time in preparation for implementations (Note how they announced support for MS SQL before training any implementation staff, you can already see where this is headed...)
My seniors stated “You don’t need it, it’s the customers responsibility to support the database. Just run the installer if they ask you to”
 
Within a few weeks I found myself one Monday morning staring blankly at a major financials spanking new MS cluster with instructions to “Get it all up and running by tomorrow cos we need you back in the office Wednesday”
I was lucky in that my bosses had “Given” me the weekend(!!) to trawl thru the MS website and so was clutching a 200 page document complete with large checklist in my sweating hands ready to start building the SQL failover... No one had ever installed the product on a cluster before so this was real going over the top on your own stuff.
At least it was only UAT.
And the customer placed a good sysadmin beside me
Yes, Happy days, happy days
 
Some of my colleagues were not so lucky (including one who ironically agreed wholeheartedly with managements position on SQL training). The result being after a few months I found myself flying around the country visiting irate customers, explaining what “Full” recovery model REALLY meant whilst clawing space from any available spinning platter in the server room....
 
The company did eventually send me on a training course for MS Cluster services a year after I’d installed the first one.
 
I never did get any SQL training off them
 
Who to blame?
 
a)  The customers who bought a product without having enough MS SQL knowledge to catch issues early (or failing to place the required technical staff at the heart of the planning process)
 
b)  The software vendor who only placed sales & financial staff at the most senior management levels
 
c)  The techies who didn't have the courage to refuse unreasonable demands from their management because they don't want to be perceived as technically weak (Professional pride will kill us all)
 
You can guess who I don't blame
 
But I'm biased...
 
RT

 


Learn more about SQL Server tools