How to create development/test environment of TB reporting database in SQL Server

Question

I am working on a couple large reporting databases with lots of reporting and analytical queries and many ETL jobs. When I make changes I usually do in in production, be that changes in indexing or the code.

Just had a minor accident after changing some code so I am thinking to start creating a development/test environment.

But the databases are huge with some huge tables. The databases are located on a huge server with lots of CPU cores and memory. How can I make a light test environment on my own computer in a developer edition?

Another option would be to create a test environment on the production server which is a data warehouse server (i.e. there are lots of ad hoc queries anyway). Is this a better approach? I would just have to restore a backup and name the database something else...

There are many dependencies in the databases, so scripting out all the objects manually is too much work. E.g. some stored procedures use around 100 tables...

So my question is: how can I stop making all my changes directly in production?

Thanks

• McNets: Disks are cheap right now, how many TB are we talking about?
• xhr489: @McNets, around 5 TB.
• McNets: You can buy a 12TB external disc for about 225,ドル obviously in terms of performance is not the same, but you can keep your production environment safe.
• Dan Guzman: For a lightweight local dev environment, you could use docker. Rather than scripting objects out of production, keep those under source control and consider leveraging SSDT database projects as that will take care of dependencies.
• BCM: Good on you for recognizing the issue with making changes directly in production, and taking the initiative to change that. As mentioned above, hard drive space can be really cheap, especially when it's for dev. Talk with your sys admins about what you want, and they may be be able to show you some easy options.

Answer 1

The best option is to have a separate Dev/Test server where you can have a full instance of your Data Warehouse. If you don't have a seperate server, you might use an Azure Virtual Machine (or similar) that can be turned off when not in use.

And you should also have a local dev environment, probably with a subset of the data. This allows teams to work in parallel without stepping on each other.

If you have only one server, you can co-locate the Dev/Test database and the production database, but you have to be careful to not over-tax the resources on the server.

It's also completely appropriate to perform some Dev/Test activities directly against the production data warehouse. For instance when you're building or modifying reports, but not when you're modifying tables, views, or stored procedures, or working on your ETL jobs. A good rule of thumb is that if the work requires more than SELECT privileges, it shouldn't be done directly on production.