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
Best Answer
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.