372
Problem
We are getting requests all the time for new SQL Server databases for both internally developed and third party applications. How can we be properly prepared as SQL Server DBAs to support these new applications? What are the key questions we should ask? Can you provide a detailed listing of questions?
Solution
A resent article in SQL Server Magazine provided 17 questions to ask before supporting a new third-party database. This reminded me about my version of the Database Request Form that I have been using for several years. The Database Request Form we use is intended to help us with proper database configuration and documentation. It is a main source of the documentation. For example, it helps us to find information about the steps to migrate a database to a different SQL Server.
We use this Form for all new databases, not only third-party databases. This is part of the new database creation process.
The purpose of this questionnaire is to gather the following:
- General information (Application Version, Name(s), Contacts, etc.)
- Database documentation (Setup Document from Vendor, Database Diagram, Data Classification, etc.)
- Database setup requirements (Hardware, Software, Networking, etc.)
- Database growth and future disk space requirements (Capacity, etc.)
- Backup and Recovery requirements
- Security settings
- Remote access requirements
- Connections strings and other database related application settings
- Application operational information (For example: Automated processes run at night)
- Auditing requirements
Questions for the Database Requester
Every software application is different and some of the questions may not be applicable to every single application. So, when DBAs send the questions to a database requestor we usually ask them to make the best effort in filling out the questionnaire. Then we review the form and if any important information is missing we ask them to complete specific sections of the request form.
General Information
- What is the name of the requested application and version?
Why do we need this information? To make sure we properly interpret future users requests.
For example, we have 3 instances of the same application installed on the different SQL Servers, all of them have different application versions. The Database Request Form will have application version in case the databases have the same name. All we need to do is ask the user what application version the database belongs to. - What is the latest application version available?
We need to know this to avoid future upgrades. We want to make sure that the latest version is installed on the latest version of SQL Server supported. - Provide vendor’s database diagram or other documentation that describes database objects.
- Is this a financial application? Include information about financially important tables and specify any audit requirements.
For example:- Key Financial Application – Strict audit requirements, that requires auditing 90% of the tables (provide tables list)
- Not a financial application, but contains customer information (requires auditing of any access to the Customer table)
Pre-requisites
- Software requirements (SQL Server Edition, Version, Custom add-ons, etc.).
For example, Microsoft CRM 4.0 requires CRM Reporting Services Add-on installed on SQL Server. - Hardware requirements (SQL Server CPU, Memory, Storage, etc.).
- What users/logins have to be created before application installation?
For example:- Application service account – Domain user (application service startup account or application pool account for the web application)
- Application service account – SQL Server Login
- Application support user or vendor’s login needs db_owner access to the database for the application installation (remove after the installation)
- Project Manager (if project initiated) and/or Technical Lead contact.
- Implementer(s) contact information (who will install an application/database) for each environment.
- Application support contact(s) for each environment. Specify if different for server support (usually technical person) and for the application support (business person).
- Business owner of the application including department name (required for the Change Management approvals). Department name might be required for some of the databases statistics (for example, databases space used by department).
Provide multiple approvers if there are integrations involved.Timelines
- When is the database required?
We will ask to provide the dates for each environment in order to plan DBA resources availability.
For example: Development environment due date; Test environment due date; Production (pre-production date if required or/and go live date). - When application setup is scheduled and will be completed. This is required to make sure that a DBA is available in case of any issues during the installation.
Similar to the question above we ask to provide dates for each environment. - What is the initial database size?
We usually ask to provide size to accommodate 1 year growth. - Provide database growth rate:
- What amount of data in MB\GB\TB or in Rows changes daily/weekly/monthly (provide one)?
- How much data usually added each year (in MB\GB\TB)? Can you provide examples based on other customers statistics.
Security
- What Authentication will be used by the application to connect to SQL Server?
For example:- SQL Authentication
- Windows Authentication - Domain Login
- Windows Authentication - Domain Group (preferred)
- Users managed through the application; only application service account required.
- Does the application require DBAs to create logins going forward? If not - who will be the application administrator to create logins used in the application? What is the process of the user creation/deletion?
This information is required to answer auditors’ questions. - Does the application require a service account and what database/SQL Server permissions will it need? Provide reasons if SYSADMIN server role is required for the day-to-day application functionality. Use minimal permissions principal.
- Are there any specific permissions that are required only for the application installation (provide details and provide the date when extra permissions can be removed).
For example:- MS CRM installation requires SYSADMIN on SQL Server and Local Administrator’s rights on SQL Server host as well. Reporting Services access is required. These permissions are safe to remove/downgrade after the installation.
Installation and Configuration
- Please provide vendor’s documentation (or URL to the document) with database setup steps.
We also ask here to provide documentation with steps for the database migration to a different SQL Server. - How should the database be initially created?
For example:- Need an empty database, application will create database objects
- There is vendor’s script to create database objects
- There are data files or a backup provided by the vendor to initialize (attach or restore) the database.
- What is the suggested database name? Does application support a custom database name?
In most cases the name will be adjusted to the company’s naming standards, but some applications do not support a change of the database name. - List remote access requirements.
For example:- Do users from remote offices require direct access to the database (for example with SQL Server Management Studio)? Are there any firewall rules that have to be implemented to support these connections?
- Are there any requirements for the offline data access (consider replication)?
- Are there any additional security considerations?
For example:- Very sensitive customer data, regulations require to keep database on a dedicated SQL Server with limited access
- External clients need access to the set of the tables through the external web site (consider replication or data export)
- Real time read only access required for the application support or developers (consider readable replicas with read-only intent).
Backup and Recovery
- How often does the database change during the day/week/month?
For example:- once a day
- every minute/second
- couple of times a week
- read-only
- configuration database (rarely changed)
- How much data loss is acceptable? Will you require point in time recovery (for example - there was a failure at 2:00 PM and you need to recover the database to 11:15 AM which is a point in time on the same day)?
For example:- one day is acceptable
- up to 3 hours, users will re-enter missing data (non-frequent database changes)
- up to 5 minutes, minimal data loss is acceptable
- no data loss is acceptable (this may have additional costs to have proper High Availability infrastructure).
- Are there any other systems that help to bring the database up to date after full backup is recovered?
For example:- There are SQL scripts generated for an application every hour that could be executed to bring the database up to date
- Users may re-import missing data from Excel
- Users will run one or two simple batch processes using an application
- Are there any requirements to keep databases in sync with integrated system(s)? Please provide details and steps to synchronize the data if both systems can not be recovered to the same point of time.
For example:- An application uses three databases and all of them have to be in-sync
- When Application “A” database is restored Application “B” database have to be restored from the same day/time backup
- Users create SSRS reports using an application. Both – SSRS reports have to be in-sync with the database as reports information is saved in the application’s database.
- Application “C” depends on files located on the network share; have to be in-sync
- A database “D” has database link to an external database; data in both has to be in-sync. Data will be re-imported base on a timestamp from the external database in case of database “D” restore.
- Does the application have any resource intensive tasks/operations that will run during the day or afterhours?
For example:- Application runs items in a roll-up job at 11:00 PM which conflicts with one of the database maintenance tasks. The schedule needs to be coordinated with the DBAs to prevent database locks and long running jobs.
- How is the database configuration performed and where is connection information specified?
This will help during future databases migrations to different SQL Servers.
For example:- There is an application configuration tool
- Database connection information is specified in web.config file (provide server name and file’s path)
- ODBC connection on application server or desktop client
- Database setting are in *.ini file (provide the location of the file).
- Provide application server(s) names.
- Provide any dependencies on SQL Server features or services.
For example:- SQL Server Reporting Services
- Partitioning
- Full-Text Search
- Provide non-SQL Server dependencies.
For example:- There is a network folder that used by the application’s bulk-insert operations and service account requires access to this folder
- Scripts that executed by SQL Server Agent Job/SSIS package located on a network drive
- TNSNAMES.ora file is on the network drive (required for data imports from Oracle to SQL Server)
- Integrations require another system (for example, Oracle database) to be available.
Contacts
Capacity
Operational Information
Dependencies
Questions for the DBAs
The following information may not be available during initial requirements gathering and will be filled out by a DBA when database/application goes to Production.
- Database Server name/instance.
- Database Administrators’ accesses.
For example:- Using Windows Authentication (preferred) and SQL Server Management Studio
- Using third-party SQL Server Management tool
- By using VPN connection to the external server
- Using SQL Server Authentication (only when Domain Authentication is not available).
- Additional database user setup requirements.
For example:- Read only (back end) database access for the application support person in the Test environment
- Audit setup (if applicable).
For example:- DDL trigger created to capture all views changes for the Delta_Prod database and send e-mails to the DBA
- Extended events setup to capture all security changes
- SQL Server Audit setup required to track any databases changes
Next Steps
- Read other Database Administration tips including tips about databases planning and growth.
- Read this great tip about Third Party Application Checklist for SQL Server. In addition to the sections in this tip it has also questions related to the Architecture and Performance.
- The same author for the SQL Mag article, Tim Ford, also shared a downloadable SQL Server Requirements Document here – Check it out!
