0

I am very new to SQL server and currently in my organization for an automation project, we need to create two databases each for Dev, Pre-Prod and Prod environments.

Our application is supporting SQL server 2014 but now i am confused how to create these databases for each environment. Can I create the 6 databases under a single instance or should I create 3 instances for each environment and then 2 dbs under each Instance.

Please guide me as I am not aware of best practice in this case. We dont have any dba in organization so we need to decide on our own about the approach. More guidance are really appreciated.

Thanks.

SSharma
  • 3
  • 1

1 Answers1

2

Ideally, you'd have separation of everything:

  1. Server for Dev with its own databases
  2. Server for Pre-Prod with its own databases
  3. Server for Prod with its own databases

In less ideal circumstances, you could put Dev and Pre-Prod together, but never put Prod in with the others.

There are a ton of reasons for this, but the tops ones are

  1. Security: Developers shouldn't have full access to Prod or Prod data, being able to change server settings, take out of band backups, or walk out the door with company data

  2. Safety: You cut down on a lot of "I thought I was in a different database" mistakes around dropping objects, overwriting

  3. Resources: You don't want people trying to tune queries and indexes, work on new features, or test out new features on the same hardware that you're trying to run a production workload

If your fear is around licensing, don't worry. Developer Edition of SQL Server 2014 is free, so Dev and Pre-Prod only cost what the hardware does.

Prod is the only place you would have to fully license.

Hope this helps.

Erik Darling
  • 136
  • 4