A community of more than 1,600,000 database professionals and growing

Today we have a guest editorial as Steve is traveling.

I am going to guess I am preaching to the choir on this one. Very few DBAs tolerate inconsistent setups on their managed database servers. I suppose if you are a small shop and there are only a few servers, the server setup doesn’t matter as much. When you are in a larger shop, consistency becomes very important.  I have worked places where the test servers had completely different setups from the production servers and that made life difficult for me.

Some things, like consistent drive letter setup, can be really nice to have. When all the data, log, temp db files are on a consistent drive and folder, it is easier to manage and know where things should go. Sure, you can and should setup the default locations in the SQL Server settings, but having the database files in the same location is one less thing to remember. Similarly, with backup locations, who wants to have to remember different backup share locations based on the server / site you are on?

I also prefer to have the database names be consistent across dev, test, stage and production. When dev and test servers add _dev or _test to the end of the production database name, I go nuts. Since a server is already a dev or test box, I would rather keep the database name the same as production. I have run into some issues when stored procs access multiple databases. They break when the dev or test environments are refreshed from production. In this scenario the stored procs have to be updated to point to the new database name with the _dev or _test appended to it.

Now perhaps the issue for some of us is that dedicated dev and test servers don’t exist. Perhaps you have one server that serves as both a dev and test box. I believe since virtualization has come so far and is relatively easy to use and setup, this shouldn’t be stopping too many people anymore. In other cases, I am sure there are just old servers and old setups that have been inherited. Still, the time spent cleaning things up to make them consistent once and for all will be worth while in the end.

Scripts are easier to manage when database server setups are consistent. Nothing complicates a PowerShell script better then having to accommodate different sites drive configurations. Or worse yet, to have different scripts because the setups are too diverse to have a single script for all sites. Then of course you run the risk of accidentally copying the wrong script to a site. Also, there is the extra burden of having to update multiple versions of a script when some change is needed.

So how about you? Is your current environment consistent? Does it bother you when it isn’t? Share an inconsistency server setup nightmare you have experienced.

Join the debate, and respond to today's editorial on the forums

GeneratedAlwaysType is a new column value that is system-generated. This property is related to the new feature - temporal tables.

SQL Server 2016 introduced support for system-versioned temporal tables as a database feature that brings built-in support for providing information about data stored in the table at any point in time rather than only the data that is correct at the current moment in time.

Applies to: SQL Server 2016 (13.x) and later

Ref: ColumnProperty() - https://docs.microsoft.com/en-us/sql/t-sql/functions/columnproperty-transact-sql?view=sql-server-2017

» Discuss this question and answer on the forums