Architecture Back End Big Data Database Infrastructure Management Performance Process Security Students Support

(74) Assessing the SQL Server Database Function Part 1 of 2

How do you ensure that your SQL Server employment is sufficient for your ongoing development efforts?

This is important for an architect because… an architect is responsible for all of a project’s technology and database support is a key piece.

TWO PART BLOG POST – In part II, we will cover database development, performance, and operations.

As an architect, you will want to sponsor periodic assessment and review of associated IT functions like database, security, networking, and support.

We will review several categories of things to assess and review for databases support.

Not Covering Best Practices.  This post is dedicated to explaining preliminary assessment areas.  Addressing the results of the assessment is a topic for separate posts.

A Couple of Organizational Concerns

Relationships with database administrators (DBAs) can get a bit tricky depending on the organization.  This is especially true for small and medium-sized organizations that may have only one DBA or perhaps only have one person doing it in addition to other responsibilities.

Overlapping Roles.  The roles of DBAs and developers overlap quite a bit – more than any other non-developer role.  Consequently, positive relationships with the DBA team are essential.

Different career paths.  Generally speaking, the software developer and architect career path is more lucrative, has more jobs available, and has more opportunities for promotion.  Developers typically get paid about 25% more than DBAs.  Architects are typically paid about 60% more than DBAs.

Physical DBAs vs. developer DBAs.  There are generally two kinds of DBAs – physical DBAs and developer DBAs.  Most DBAs are “physical DBAs”, who focus on technical rather than business issues, primarily for day-to-day system administration. Typical tasks center on physical installation and performance. Physical DBAs are rarely involved with actual implementation of databases and applications. They might get involved in application tuning when operating system parameters or complex SQL Server parameters need to be changed.  Developer DBAs focus on database design, SQL development, and ongoing support. 

Architect as a physical DBA?  One of the key differences between an architect and a developer is that an architect can typically handle a more diverse set of tasks.  Depending on the quality and availability of physical DBAs, the architect may be called upon to take on some physical DBA tasks and should be prepared to do so.

Not many developer DBAs.  Most organizations, especially small and medium organizations, don’t actually have any developer DBAs.  In fact, most organizations rely on application developers to do any SQL development and maintenance.  As suggested earlier, developers do get paid more, so more should be expected from the development team.

Typical IT department organization.  In most organizations, the DBAs are part of an Information Technology department while developers are part of a different internal organization.  Working in different departments makes differences of opinion harder to reconcile than if people are working on the same team.

Potential for strong differences of opinion.  Applications cannot run effectively unless their database is running effectively.  An architect may suspect there is a problem on the database end, but the DBA needs to understand it and act on it.  This may be problematic when the DBAs are not as proficient as they should be.  

A couple of examples.  In my personal experience, I have had DBAs suggest that their code did not need to be checked in, that VM configurations were the root cause of performance problems, and DBAs refusing to share production performance data.  These types of problems can shut an organization down quickly. 

Conflict resolution.  Problems with databases are often serious and irreconcilable problems.  While developing a consensus is preferred, if a problem is going to compromise your operations, that needs to be escalated to your manager in a timely manner.

Component Inventory

To assess what you have, you first need to know what you have.  Getting an inventory is pretty straightforward with SQL Server Management Studio (SSMS).  

The items you need inventories of include:

(1) Database Instances.   A single database instance can have multiple databases.  

(2) Databases.  Self-explanatory.

(3) Schemas.  The default schema for a SQL Server database is “dbo”, which stands for “database owner”.  You can explicitly create other schemas as well.

(4) Tables.  Taking an inventory of tables assumes that the columns and data types are also defined.

(5) Stored procedures and dynamic SQL.  Databases only to serve the SQL queries that are run against them.  An architect needs to understand all of the SQL that is directed against the database whether from stored procedures or from dynamic SQL.  Dynamic SQL includes direct dynamic SQL statements and those from database libraries like Entity Framework (EF).

(6) Primary Keys and Foreign Keys.  Primary keys and foreign keys are technically constraints, but we want to treat them as a special case.  The other constraints, listed below, are important for keeping our data consistent, but the primary key/foreign key relationships are the most important enforcement mechanisms for relational integrity.

(7) Other Constraints.  This includes primary keys, foreign keys, “not null” constraints, unique constraints, check constraints, and defaults.  Some may be surprised to see primary and foreign keys on the list, but they 

(8) Cursors.  Cursors are part of SQL, but as architects we want to treat them as a special case.  Cursors are never mathematically necessary and can cause performance problems.  It is OK to use cursors if you absolutely must iterate through a data set rather than using set logic, but it is preferable to avoid cursors if possible.

(9) Views.  Not all databases have views, which are essentially virtual tables based on underlying query logic.

(10) Sequences.  Not all databases have sequences, which are simply used to produce sets of unique integers.

(11) Functions.  Functions are similar to stored procedures, but must always return a value.  Functions are generally preferred when you have some simple repetitive logic that returns a scalar value and is called from stored procedures.

(12) Triggers.  Triggers execute SQL in response to changes in data.  As an architect, you typically want to be skeptical about the use of triggers.  They should be used very sparingly and never used to enforce relational integrity.

(13) SSIS Jobs.  SQL Server Integration Services (SSIS) jobs are a typical means of providing Extract-Transform-Load (ETL) services for a database.  There are other ETL tools available, of course, so if you are using a different ETL tool, then you should inventory the components for that system as well.

(14) SSRS and PowerBI Reports.  SQL Server Reporting Services (SSRS) reports are a typical means of providing reporting for a database.  There are other reporting systems available, of course, so if you are using another reporting system, then you should inventory the reports from that system as well.  

(15) Other External Dependencies.  As mentioned above, ETL tools and reporting tools are key dependencies, but there may be other tools that depend on the database.  More importantly, there will typically be one or more applications dependent on the database as well.  Anything that depends on the database needs to be inventoried.  

(16) SQL Agent Jobs.   “SQL Server Agent” is a Microsoft Windows service that executes scheduled administrative tasks, which are called “jobs” in SQL Server 2017. 

(17) Data Dictionary.  For the architect, the data dictionary is the most important artifact.  A “data dictionary” is not a database artifact, but simply a definition of the meaning, or “semantics”, of each of the tables and columns.  The data dictionary typically is not that important to a physical DBA, but if the architect does not fully understand the meaning of the data, that will result in some obvious problems.

Configuration

Most of the configuration information is critical to the physical DBA, but can largely be ignored by the architect if only because a DBA team can be expected to have a high degree of configuration expertise.  It is nonetheless important to understand the configuration if only to understand whether you have optimized the use of system resources.

KEY POINT:  Assessments are important, but not all assessment areas are of equal importance to the architect.  In my experience, I don’t ask the DBA team much about these configuration items for existing applications unless there is a problem with them.

SQL Server edition.  As of SQL Server 2017, it ships with four editions – Enterprise, Standard, Express, and Developer.  A common mistake is to use a higher edition than necessary.  SQL Server is a very expensive software package, so you should use the least expensive edition usable for your application.

Hardware.  SQL Server hardware typically has to be much more robust than the hardware for other system components like application servers.  Typically, if any one hardware component fails then the system should keep running.  There is an article on this topic at https://www.mssqltips.com/sqlservertip/3235/sql-server-hardware-configuration-best-practices/.

Operating system/VM configuration.  Most SQL Server installations are installed to virtual machines (VMs) rather than directly on host operating systems.  Architects should not be too concerned about VM configuration against the host, rather concerning themselves with the operating system version and other resources that the database has access to.  It really doesn’t matter to an architect whether the OS is running on hardware or is virtual.

Compatibility level.  A database can be configured to act like a previous version of SQL Server.  For instance, you could install SQL Server 2017, but have a database running as though it were a SQL Server 2008 database.  There is an article on this topic at https://www.spiria.com/en/blog/web-applications/understanding-sql-server-compatibility-levels/.

Settings.  There are over 75 settings that can alter fundamental ways that SQL Server runs.  For instance, you can change the maximum number of concurrent actions or the way memory is handled.  There is a guide to the settings at https://docs.microsoft.com/en-us/sql/database-engine/configure-windows/server-configuration-options-sql-server?view=sql-server-2017

Security

Your system is only as secure as your database.  If your database is not secure, then the entire system should be considered insecure.  As with the other categories here, there are entire books devoted to the topic.  The key concerns for the architect are with users, roles, and permissions.  It is generally safe to assume that physical DBAs can handle the rest pretty easily.  

Special attention to new applications.  The main time for an architect to pay more attention to SQL Server security is when moving a new application to production.  While the physical DBAs can handle this, most organizations have security personnel audit the security configuration before such a deployment is approved.  Experience suggests that if a deployment is deferred for security reasons, then the delay could be lengthy and will typically force missed commitments.  Consequently, working through a careful checklist – ideally provided by the security team – will typically prevent or minimize such a delay.

Difficulties understanding SQL Server security.  Many developers never fully understand SQL Server security.  SQL Server security is – by necessity – more complicated than simple application security models.  Understanding SQL Server security does require some study and some practice.  Microsoft has an overview at https://docs.microsoft.com/en-us/dotnet/framework/data/adonet/sql/overview-of-sql-server-security 

KEY POINT:  If you are working with SQL Server and you don’t understand the security model, then it is essential to spend some time learning it.

Logins and Users.  Usually “logins” and “users” are words which are interchangeable with each other.  In Microsoft SQL Server, however, they are very different things.  Because developers typically assume that they are the same thing, this gets confusing.

Logins are created at the database server instance level, while users are created at the database level.  Specifically, logins are used to allow a user to connect to the SQL service (also called an instance).  You can have a login defined without having access to any databases on the server.  In this case you would have a login, but no users defined.  The user is created within the database and then mapped to a login.  This mapping allows the person connecting to the database instance to use resources within the individual database.

KEY POINT:  Logins and users are not the same thing.  This is probably the most confusing aspect of SQL Server security for developers.

Roles.  Roles are key to any Role-Based Access Control (RBAC) system, hence the name.  In other systems, roles are often referred to as groups.  Roles allow users to be assigned sets of permissions rather than assigning individual permissions to users.  This is a standard model for computer system authorization.  SQL Server supports a variety of “built in” roles and allows you to create your own.  Examples of built-in roles include “sysadmin” with a permission set that allows users to do anything and “db_datawriter” with a permission set that allows users to add, delete, or change data in any tables in an individual database.  Further information is available at https://en.wikipedia.org/wiki/Role-based_access_control.

Permissions.  Permissions are self-explanatory.  It is noteworthy for architects, though, that permissions to execute stored procedures must be granted explicitly.

KEY POINT:  It is easy to overlook that stored procedure execution permission is different from permissions to change data.  It can cost you a couple of days of debugging if you overlook this point.

Authentication mode.  SQL Server supports two authentication modes – one  that is integrated to Windows authentication and one that leverages an authentication system that is built in to SQL Server.  While details are beyond that scope of this post, Windows integrated authentication is strongly preferred because it is more secure.  Additional information is available at https://docs.microsoft.com/en-us/dotnet/framework/data/adonet/sql/authentication-in-sql-server

Vulnerability assessment.  SQL Server has a built-in vulnerability assessment tool that can help you easily find security weaknesses.  It is not a replacement for knowledge and experience, but it is both cost-effective and helpful.  Running it is as easy as navigating to the menu option in the screenshot above.

Transparent Data Encryption (TDE).  TDE is method used by several major database vendors to protect the data storage – the data stored on disk.  It is generally a mistake not to enable TDE.  In fact, security auditors will typically insist on TDE being enabled.  Additional information is available at https://en.wikipedia.org/wiki/Transparent_Data_Encryption.

Leave a Reply

Your email address will not be published. Required fields are marked *