Separate SQL Server or just separate SQL Database for test and production?

Question

I'm new to SQL Server, so this may be more of a SQL Server management question.

I'm creating test and production databases for a service, and I imagine I'll be wiping the test database quite a lot. Additionally, I'm going to want different replication and audit logging strategies.

Does it make sense to have both SQL Databases on the same SQL Server, which seems to be what the Azure Portal makes easiest to manage, or does it make more sense to create a separate logical SQL Server for the test and production databases?

Answer 1

Go with a separate logical server:

• It is going to cost the same because you are charged on the tier of the database not the database server.
• By providing separate servers you provide isolation between the two databases protecting one from the other.
• Unless​ you use contained users your users will be shared between Test and Production, if you happen to use the same user for both databases a change of the password in production would also require the same change in Test.

You might want to use the same server for a database if:

• You are creating a very large number of Databases across many Dev/Test environments as there is a limit to the total number of SQL Servers.
• You have two databases that need to be deployed as part of the same ARM template.

Answer 2

Putting them both on the same server is certainly the easiest. However, I'd argue in a business situation you shouldn't be able to do it.

A production database is one of the most important resources of a web app. It likely has much confidential data in it. It's also probably critical to the site's stability. So the production database credentials should be tightly controlled, and firewall rules should prevent access to it except from a small number of vetted machines. In addition to security considerations, the firewall rules also help prevent accidental test operations on the production database - if it can happen, it will sooner or later.

Edit: Shortly after I originally wrote this answer, Digital Ocean (one of the largest hosting providers) had an outage that would've been prevented if they had segmented their network appropriately. Learn their lesson instead of repeating the mistake.