DBAs Checklist for Designing and Reviewing SQL Server System Architectures

By: Sadequl Hussain   |   Comments (8)   |   Related: More > Database Administration

Problem

DBAs are often asked to design and plan database infrastructure for new applications or upgrade existing systems. To propose a new solution or verify an existing architecture, a DBA must have a clear understanding of the system's requirements. In this tip, I will try to list a few areas DBAs need to cover when they are designing new systems or reviewing proposed solutions.

Solution

Of the many hats an experienced DBA wears, one is that of a system architect. Development DBAs often spend their time reviewing detailed technical specifications, understanding pseudo-code and flow-charts then validating database object structures. They advise developers about missing table indexes, creating re-usable functions or benefits of using table variables.

An infrastructure DBA on the other hand, may not always have in-depth knowledge of the application. Instead, he will have broad knowledge about the enterprise application landscape. He needs to understand what applications are in production, what database back-ends are used, how storage and virtual servers are provisioned, among other things. If you think of yourself as an infrastructure DBA, here is a sample of tasks you could be asked to do:

• A new, third-party supplied document management system is being implemented in your organization and it's based on the SharePoint platform. The vendor is doing all the hard work and they have come up with a detailed architecture document. The work will begin next week and all the parties have agreed it's a good design. You have been brought on board at the last moment and asked to review the architecture. You need to give the final verdict whether the design conforms to good database practices.
  
• After months of development, testing and polishing off, the company is ready to take the next big leap as version 2.0 of a core application is ready for rollout. You need to suggest what will be the best path for a smooth migration. Also, what will be the rollback method?
  
• Management has at last given the green light to an application enhancement project and the project manager has kicked off weekly meetings with developers, architects and DBAs. A lot of brainstorming and whiteboard drawings are happening and people want to know what would be the best configuration for the database server.
  

Savvy DBAs should be able to look at a system's design and suggest the best solution for the database tier or recommend any better approach. The DBA should also be able to architect the database layer from scratch and be able to support his design. The key thing to do is to understand the purpose of the system and gather as much information as possible.

That's why it's best to have a list of questions and metrics available at hand. Having said that, not every project will have an all-encompassing, well rounded set of specifications. As the DBA asks his questions, not all the answers may be readily available. This can range from "we don't know, you tell us" to outright blank stares from around the meeting table. The DBA needs to take note of those unknown areas, suggest possible solutions or inform and educate his peers.

In the following sections, I will try to list some of the checkpoints DBA can refer to.

Purpose

First of all, try to understand the system's function and what will it be used for. In other words, get some background information first. Consider items such as:

• What's required from the database side?
• Is this the back-end for a new financial application?
• Will it be a configuration database for the corporate anti-virus server?
  Are they rolling out a new SharePoint system?

Understanding the purpose and the importance of the system will help you determine the best possible configuration and resource requirements. For example, you would not want to recommend fiber channel disks for a system that would be seldom used by few users.

New SQL Server System vs. Upgrade and Migration

Here are the items to consider with a new platform versus an upgrade:

• Is this a new system or is it an upgrade or migration?
  • If it's a migration then what's the migration plan and the timeline for it?
    • Will it be an in-place upgrade or a side-by-side migration?
  • Has the new server been built?
    • If so, has it got at least the same computing power as the existing server?
• For an upgrade or migration, what's the cutover plan?
  • What's the outage window?
  • What's the plan for any rollback situation?
  • Has here been enough testing done for a rollout?
  • Finally, what's the plan for decommissioning the old system?

Complexity of the SQL Server Architecture

For an understanding of the architecture, consider the following questions:

• Is there any architecture or topology diagram available?
• How complex is the physical solution?
• Is it is a single stand-alone database server they are after or a more elaborate setup involving geographically separated sites and servers?
• Is clustering or AlwaysOn technology needed?
• How is the database server related to other systems in the farm?
• Is the server communicating with heterogeneous data sources from Oracle and/or Unix?
• Will the solution call for merge replication with other SQL Server systems or will it be spitting out data to an FTP server?

Some of the architecture components may not strictly fall within DBA's domain, but you still need to be aware of including:

• Where will the SQL Server be located?  Behind a corporate firewall or in the DMZ?
  • If the latter then what account would you need to access it?
• What account will be used for the service start-up?
• Think about load balancing. If it's going to take simultaneous hits from a few thousand connections, where is the load balancer?
  • Is an F5 or NetScaler component shown in the diagram?
  • Are there multiple app servers available before the database tier?

Each of these parameters would affect your own implementation planning. If clustering or AlwaysOn is required, you know you have to work very closely with the storage engineers and complete extra configuration work during setup. If it's replication with heterogeneous data sources, you know you need to work with Linked Servers, and possibly install ADO libraries as well. These things will make the implementation more complicated and you would want to test it thoroughly and document everything before a handover. This would also help you better estimate your time. Rolling out a vanilla SQL Server on a single box may take two hours or half-a-day maximum, you may want to ask for two days or more for a cluster.

Looking at the architecture, you may often be able to suggest a simpler, easier solution tailored for your organization. In one of the projects I was working on, the vendor had come up with a detailed specification for the database server of an antivirus system's backend database. The solution was the best-of-breed: it called for failover clustering and implementation of a remote DR site for protecting the database.  Here are some key questions I asked:

• How many databases need to be created, I asked.
  • One.
• How big would be it with a full system load and logging?
  • A few hundred megabytes in two years' time, I was told.
• Will it require up-to-the minute recoverability?
  • Nope, it can stay in the simple recovery mode.
• How critical is it? Is this a 24x7 system?
  • No, it can wait a few hours if the DB is not available.

I knew where I was going put that database and I told them. It would be hosted in a server with ample power, but it would be co-hosted with other third-party databases of similar kind. The database would be fully backed up every night. It would be have DBCC's checked every week and it would also have indexes reorganized and its statistics updated. As for site recoverability, the server was a VM, protected by VMWare vMotion and clustering. The underlying storage system was already replicating to a remote DR site.

They agreed to my proposal and to this day, the database is running fine.

SQL Server Storage

If the architecture calls for a new environment, understand if it should be a physical or virtual machine. VMs are the choice of server hardware these days. Even for a VM, understand how the resources will be allocated. Will it be all contained within one or more files in a VMFS file system, or will the ESX-hosted VM be connected to iSCSI LUNs from an underlying storage pool?

For SAN LUNs, you would want to ask your storage engineers about the network speed of the LUNs to the VM. For a purely VM based solution, you would want to know if there is one or more underlying hosts connected in a cluster. For VMWare-based systems the clustering model would help protect the VM through a mechanism called vMotion.

Similarly, how many drives will be mapped? Personally, I still like a logical separation between data and log files and want to keep them separate from the rest of the system. For a database server, I would want:

• One drive for system and OS files (C:\)
• One drive for the SQL binaries and swap space (D:\)
• One drive for data files (E:\)
• One drive for transaction log files (L:\)
• One drive for backup files (B:\)
• One drive for tempdb data and log files (T:\)

Now why would you go to all of this trouble discussing these things with your server engineers? Well, think about the next set of questions.

• Is this a business critical, top tier application? Or is this something the business can survive without for a few hours or a few days?
  • The answer to this question will help you determine the resource requirements for a server.
• A mission critical application calls for the best possible I/O from its storage engine and the fastest speed from its network link. It will need regular backups and possibly a failover option too.
• The same features may not be necessary for a mid-level, non-critical stand-alone server.
• If you know you are dealing with the design of a tier 1 system, you would want to ensure you have done everything in your power to allocate the best resources for it.

Determining the SQL Server Processing Power

How many CPUs and cores will your server have? If it's a VM, how many vCPUs will be allocated?

How much memory will be allocated? The choice of memory will depend on the load and the use of the system. For a complex data warehouse doing a lot of number crunching and reporting, you would want to allocate plenty of memory for SQL Server, Analysis Services and Reporting Services. For a simple server hosting less than ten small databases you could probably be happy with 8 GB of RAM.

Ask about the system's peak load. How many connections will hit the database server simultaneously? The answer will potentially help determine the amount of RAM you would assign to SQL Server. As SQL Servers processes more and more queries, it would perform best if it has plenty of cache to store the data.

Check with your server administrators about the network speed. Will the system be connected to a 1 GB link or a 10GBpS link?

The choices become much simpler if it's an upgrade or migration. You know you have a baseline from the existing environment. You would want to mark that as a minimum requirement.

SQL Server Installation Considerations

Next comes the questions about the database engine.  Here are some questions to start with:

• Start with perhaps the most basic one: what will be the server's name?
• Will it be a default instance or a named instance?
• What versions and editions of SQL Server are being considered?
  • Is it only a SQL Server Express Edition system?
    • Then why is it being installed in a full-blown server?
  • Is this Enterprise Edition?
    • Then have the cost implications been considered?
    • Why does the architecture ask for an Enterprise Edition setup?
    • Is it for the ability to access more than 64 GB of RAM?
    • Is it for an Enterprise Edition-only feature?
• Are we installing the latest version of SQL? If not, why?
  • In most cases this happens for third-party apps. These applications are often not certified to be compatible with the latest versions of SQL Server.
  • The question of technical support eligibility from Microsoft then needs to be considered.
  • The same can be said about service packs. You may want to rollout the latest patch, but before you do so, check with the application's specifications.
• What components of SQL Server need to be installed and what features need to be available?
  • Does the solution call for SSAS and SSRS to be installed as well?
  • Will it make more sense to propose a separate server for those services?
• Will there be any other application installed on the SQL Server box?
  • What do those applications do?
  • Why can't they be separately installed on different servers?
  • Who would be installing those applications and services?
  • How do you monitor those applications?
  • Will those applications use a specific account to connect to SQL Server?
    • If so, what sort of access would it need?

Finally, here is a question for your project team lead or the integration leadership. Has there been a commitment for the licensing cost of SQL Server? Will it be CAL or core based license? Has the client agreed to pay for it or is this something you or your team does not need to worry about? In many cases DBAs are not supposed to raise questions about this - but if you are in doubt, ask.

SQL Server Configuration Parameters

Closely tied with the general environment related questions, here are some SQL Server configurations parameters questions.

• Will filestream data be used?
• Is Data Quality Services or Table Partitioning part of the architecture?
• What will be the collation order for the SQL Server setup?
• Should any particular configuration property be changed to a specific value?
  • For example, the application may call for the MAXDOP setting to be changed to 1.
  • A specific user may need to be made an Analysis Server administrator or have full access to the OLAP database.
  • A certain Windows AD group may require Content Browser role membership in Reporting Services.
  • Legacy code could require that xp_cmdshell is enabled.
• What about protocols and aliases?
  • If the server is a replacement for an old system and has a different name, should an alias be created?
  • Should the network administrators be advised about DNS changes?
• What Linked Servers need to be created?
  • Where do those Linked Servers point to and what security credentials should they use?
  • Do you need to create credentials, proxies and operators for the jobs?
• Will the server need to have ODBC connections created?

SQL Server Security Questions

Consider these SQL Server security questions:

• What will be the SQL Server service account?
  • Is it the same for all SQL services or different for each service?
  • Do you have the required passwords?
• What accounts will need sysadmin privileges in the SQL Server environment?
  • Can you lock it down so only DBAs are members of that role?
• Is there any specific reason for the guest account to be enabled?
• Who would be the local Windows administrators?
  • Who will have remote desktop access?

SQL Server Database Properties

This is a very basic and perhaps often overlooked area. You as the DBA may not be required to create any databases at all. Your work may be limited to installing only the database software. Find out because this will help you investigate further. Here is the reason why.

• If you are not creating the databases, who will?
  • Will it be an application engineer running custom scripts?
  • Or will it be an application installer doing everything behind the scene?
  • Either way, this means you need to provide database server access to the account that creates the databases. And it will need to have at least db_creator server role membership.
    • Will it be a temporary account, to be disabled or deleted after the databases have been created?

If you are creating databases, know the order in which they have to created.

• You will also need to know the number and size of the databases. Why? Because that would help you define the storage size and design the disk layout.
  • If it's a data warehouse or data mart, you know the size will grow bigger and bigger, so you would want to start with ample space in the data and log drives.
  • If backups are saved on the same machine, you know you will need a dedicated volume for backup files.
  • If it's a high-transaction OLTP site, you would want a dedicated volume for tempdb files.
  • Can the databases be split across multiple servers for efficient operation or would a consolidated environment deliver the best results?

Again, many of these questions won't be answered for you. A DBA will need to use his own judgment and experience to suggest and recommend an optimum solution. Sometimes this will not be called for: the decision would have been made for you, but you still need to raise your points if needed.

Check for any specific database properties required. This is different than the SQL Server configuration properties. For example:

• Will the databases be used for foreign language texts?
  • If so, what will be the collation?
  • If there are multiple sites located in different geographies, will each server have a different regional settings for Windows and different collation setting for SQL Server?
  • If that's the case, will the databases be talking to each other?
  • You may then want to point out the risk of collation conflicts and possible mitigation strategies.
• Will the databases need to have compatibility modes older than the current server version? If so, why?
• What will be the recovery mode of the databases?
  • This will directly affect the recovery objective.
  • Make sure application engineers, vendors, project managers, designers and architects are aware of the recovery model you are proposing and what it means in terms of data recovery.

SQL Server Agent Jobs and Automation

Consider these questions related to the SQL Server Agent Jobs and Automation:

• What jobs will be created in SQL Server?
• Will they be created by application engineers or the application installer itself?
• Will it require a DBA's support?
• What will be the function of those jobs?
• Are they going to invoke SSIS Packages?
• Will they require elevated permissions for running xp_cmdshell?
• How would they be log their output?
• Who troubleshoots them after a failure?

All these points will affect the choice of things like user accounts, credentials, proxies and so on.

Check if there would be Windows Task Scheduler Jobs created as well. If so:

• What would those jobs do?
• What user account would they run under?
• Has the account got required privileges in the server and the network?

Protecting the SQL Server Data

This is a "must have answer" category. You may think that database backups fall within the DBA's domain and applications owners need not worry about it, and you would be right; but you would also be surprised how many times DBAs are asked NOT to worry about backing up databases because the system has its own specific backup process and schedule.

Take the example of a SharePoint farm. DBAs may not be required to implement backups for the backend. This can be initiated from the application and that's what you might be told - that the SharePoint administrator would be taking care of the farm-wide backup. Another good example is a BusinessObjects farm. The BO administrator may have a Windows job that backs up both the file system repository and the configuration database to keep them in synch. The DBAs then don't have to backup the databases separately. Same would be the case for other customized or third-party solutions.

Whatever the scenario, make sure the topic of database backups has been covered in the architecture discussion. If the application does not have its own backup process, obviously it's your job to ensure the databases are backed up. In that case, you could be asking yourself the following questions:

• Where and how would the databases be backed up? Is it going to reside in the same server or a remote location? Is it through a proprietary application or a regular maintenance job?
• How many days' worth of backup need to be available? Is there enough space in that location? li>WWhat's the frequency of backups? Is it full backup once every day, once every week or once every month (for static databases)? Should there be transaction log backups happening?
• When would the backup start and approximately how long would that take? Would it impact system performance significantly? /li>

IIf you are reviewing an architecture document, make sure the author has addressed these questions there. If you are writing the database architecture document yourself, ensure topic has been covered.

Similar to database backups is the topic of disaster recovery (DR) and service continuity.

• Is the solution going to need a DR site?
• If it is a critical system, it probably will. How will that site be configured?
• For SQL 2012, it could be AlwaysOn technology.
• For older versions of SQL Server it could be Database Mirroring or Log Shipping.
• DR can be implemented through low-level storage replication as well. Would this be the answer?

SQL Server Monitoring, Alerting and Health Checks

Consider the following questions:

• How would you monitor the application status?
• How would you know if database services are up and running and all databases are online?
• Will you need to rollout agents in the server for a monitoring tool like OpenView, Nagios or Zabbix?
• How can you configure them to send alerts if the SQL Server service is down?
• Who gets the alert e-mails and text messages?
• How will you monitor the server's health?
• Will it require special attention or will your daily health-checks suffice?

SQL Server Ownership and Control

Consider the following questions:

• Who will own the system?
• Who are your stakeholders?
• What teams will be responsible for it?
• Will you or your team be involved in ongoing DBA maintenance or is it a one-off job?
• Try to see if an SLA has been agreed upon. What's the Service Level Agreement and Operation Level Agreement for database support?
• Check what's the escalation path for incidents and who needs to be aware about potential problems.

Answers to these questions will help you identify teams and individuals you may need to work with in the future.

Conclusion

As you can understand, this is not an exhaustive list by any means. Every system is different and so is every architecture. Ultimately it's about how your organization does business, what it considers critical and how you manage your own database systems. As I said before, sometimes you would not have an answer because people don't know the answer. You would need to use your own judgment and experience to come up with your own recommendations. Sometimes you would be expected to know what to do. Fair enough. Use your standard DBA practices in such cases.

Not all the points from this article may be necessary in every situation. For example, you may not need to ask any question at all in a straightforward server migration project where everything has been decided before you came on board. As you work through multiple projects, you will eventually get into a habit of asking the most relevant questions. To start off, you can use this article as a base template.

Next Steps

• Start creating a template or questionnaire for your next project's architecture review.
• Use simple YES/NO, checkbox, one-line answer sort of questions.
• Use this document for your architecture reviews, Q/A sessions, brainstorming or requirement gathering sessions.
• Refine the document as you progress from one project to another. Some questions may be redundant, while new questions may be added.
• Make sure stakeholders, clients, peers and other DBAs are familiar with your approach with this document
• Once this has become an integral part of your DBA interview technique, you can circulate it before any meeting so your peers, clients, stakeholders and engineers can pre-fill the document for you.

About the author

Sadequl Hussain has been working with SQL Server since version 6.5 and his life as a DBA has seen him managing mission critical systems.

This author pledges the content of this article is based on professional experience and not AI generated.

View all my tips