Strong database names on database server

Question

We have a SQL server with many databases in it. We have customers with multiple versions of a similar app and multiple apps for a single customer. Almost all databases are tied to specific websites.

How do you stay organized with your database names? Surely there is no single answer, but do you have a database naming strategy that is working for you?

We are considering:

Customer + Product + Phase of Development (Production, Staging, etc.)

But that becomes awkward when a customer runs three versions of a product.

Answer 1

I like to include the site name in my db naming schemes when possible:

client_sitename_app_version

If one client has multiple apps running the same version on the same site that each need their own db... that seems a bit odd. You would need another distinguishing factor there depending on use case. Append an instance number or usage reference at the very end.

Answer 2

Creating a Developmestuction where you have multiple instances of the same database (Dev, Production) on the same database server is just asking for trouble. If you can't get a second server (physical or virtual), you should consider creating separate instances of SQL Server.

Your databases name should reflect how you internally refer to the applications. You wrote:

We have customers with multiple versions of a similar app and multiple apps for a single customer

It's not clear what you mean. Do take one of your "base" applications and then customize the application for different clients? Do you create completely different applications for different clients? Is it a mixture of both?

My assumption is that you start by developing a one-off application and, if another client likes it, use that as a base application for future customers. Based on that, it would make sense to refer to your applications by name+customer, and name your databases accordingly.

• SalesManager_Wilco
• SalesManager_AbcInc
• SalesManager_Internal
• TimesheetKeeper_Internal
• ExpenseTracker_Wilco
• HelpBuilder_Wilco

Answer 3

I prefer to use client_appname_appversion_database-release

This considering that appversion changes much more frequently thatn database-release. So, we get:

blablabla_store_3.5_B

Answer 4

Our naming strategy is usually based on the concatenation of 'three letters acronyms' (TLA's) representing the different 'dimensions', of the database, separated by underscores. This rule is applied for sites, applications, clients, etc.

For example, when my app acronym is ABC, and our United Emirates Office is a suscriber to the ABC database, this database name will then be ABC_UAE (Using then the offical/ISO TLA of the country is of course a nice choice).

Once you give your clients a TLA identifier, you can use it to build your database name:

• ABC_ACM: for the main/central ABC database for Acme Company
• ABC_ACM_CAN: for its subscriber's in Canada

Now, if you maintain different database versions, you have the choice between:

• ABC_012_ACM if version 1.2 is maintained for multiple clients

• ABC_ACM_012 if Acme has its specific 1.2 version

In case these databases have subcribers, the meaning of ABC_ACM_012_CAN is then quite obvious. I'd advise you to use a standard 3 digits numbering for version numbers. It makes things really easier!

This rule suffers some exception when talking about development databases. For example, my developer's version of ABC_ACM database will be ABC_ACM_pgrondier, if development is maintained by client, or ABC_012_pgrondier if development is maintained by version. As a matter of fact, having the last 'dimension' of the database name not implementing the TLA rule is also quite common when you come to individual suscribers:

• ABC_ACM_012_username is subcriber to ABC_ACM_012

• ABC_ACM_012_CAN_username is subcriber to ABC_ACM_012_CAN

Answer 5

Echoing the prior posts and tossing in some comments:

Definitely, Company/Client and Application. Which goes first depends on which is more important--not to the client, but to the people supporting the software.

I totally agree that only Production systems should be on a Production server. Development, Testing, QA, Staging, EAP, whatever, they're all best served by being on their own box, SQL instance, or VMWare instance. ("Developmestuction". Love it.)

I question adding version to the database name. Can a database of version X be updated to version X+1 or X.1? If so, do you have to dig out and rename all your connection strings and other bits referencing database names? Versioning data is information I would (and do) store within each database. If an upgrade means migrating the data in an old database to a newly constructed one, I guess you could put the version in the name. And if you have to actively support multiple versions of a given database "type", AND it's useful for the people supporting the system to have that information clearly stated in the database name, then go for it (though again, beware updating/renaming issues).

Answer 6

At one of the large companies I worked with last year deploying the toolset my employer sold, we followed their regional naming conventions:

US

• <appname>{p|d} where p is for production and d for development

UK + HK

• <datacenter><appname>{p|d}

That works for them. Personally, I name my database instances around the appname and usage, too.

Answer 7

We are using container isolation to run different instances/multiples version of the databases (postgres and mysql) but the naming scheme problem is still the same. We follow the next one :

Customer name + product ID + task kind (prod, staging, test, accounting, etc.)

eg : nuxil_erp_test, nuxil_erp_prod, nuxil_erp_accountingTraining.

However, we'll probably let go off that naming scheme because customers have the ability to creating new databases, and they all have their own ideas about naming databases.

Answer 8

We use {product}{version}{Development|Test}_[optional feature description, owner, date]

Its lengthy, but does mean each database can be tracked and each database has an identifibale owner. So the main product database for version 4.2 is, say, Product42Development ... and that has an associated peer-test database Product42Test.

The peer-test database is separate from the main test database (which is on a different server and used purely by the test team): it's where developers stage their fixes prior to making it into the main tree, having it tested by other devs (hence peer-test) but keeping it out of the active development (which is constantly changing and unstable).

If there's a branch of the database needed because someone's doing very disruptive development, it would end up including the feature name, feature owner and date of creation. Something like: Product42Development_NewFeatureName_OwnerName_20090812 ... this makes sure that not only can the DBAs keep track of what databases exist, but (more importantly) so can all other devs so they don't bugger about with the database without being partially informed about what the DB's there for.