By: Tim Ford | Updated: 2009-08-12 | Comments (4) | Related: More > Database Administration
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.
About the author
Tim Ford is a Senior Database Administrator with MindBody.This author pledges the content of this article is based on professional experience and not AI generated.
View all my tips
Article Last Updated: 2009-08-12
