Sql-server – How to set up development servers SQL Server

developmentsql server

My .NET development team has been massively restructured recently, and as a result of the restructure, we now have the ability to do things like set up source control and database backups, and development databases. However, none of us is really a DBA, and so we have no clue how to do this on the DB side.

We would like to have some way to make it so that developers can be working with real data for testing, but are unable to change the data on the production server. Right now, we just have one database, so if we need to test a change to the website, we have to make dummy records to test.

Basically, we know this is a bad setup, but we don't know any better, and we really want to change. I can't find anything online that gives an overview of how to actually set up a development environment which can be used for both application development and database development.

I've looked at How to develop a database (workflow)? and How to setup local database development process for small web team?, but the first one doesn't address my problem, and the second one assumes a lot of knowledge that I don't have. I don't know anything about "normal" database development practices, rollout/rollback scripts, how to write tests for database development, schema comparison tools, etc. (These are all terms I've seen in my search, but I don't know where to go to learn about them.)

Does anyone know of a place where I can go to learn about this stuff? And/or can anyone give me some advice on how to set up the environment that I don't know how to describe?

My team is very averse to using TFS. I'm not sure why (I've never used it myself), but they like Git, and I know Git, so that is the source control we've decided on. My main question was actually how to set up separate servers for the different environments, while having current data to work with in development.

Best Answer

Most of these topics aren't overly difficult but there are so many that I think you would struggle starting from scratch.

Furthermore because you're coming at it from a developer workflow point of view you'll likely discover that this kind of information doesn't get talked about much. You're likely going to have to pay for a consultant to come in, help you set up the dev server with basic backups and maintenance, and otherwise create a workflow for you.

Does anyone know of a place where I can go to learn about this stuff?

If you were determined to do it yourself you'd search YouTube for videos on SSDT or SQL Server Database Projects which is part of Visual Studio; most of what you want is covered in some way by that (unit testing, source control through TFS or similar, deployments through msbuild, and also even schema compares). Unit testing is also commonly done with a framework called tSQLt. Deployments are sometimes done with Flyway. Database backups and maintenance are usually done with Ola Hallengren scripts or Minion Backup / Reindex.

You'd also take a look at SQL PASS which has a lot of chapters which have online training videos on each of those topics. However most of them will come up via a YouTube search.

Those are all free tools with caveats around Visual Studio of course.

For inexpensive non-tailored training Pluralsight has a video on each of these topics above and other database administration. But if you're not a DBA then you probably don't want to waste time on learning all of these, and you're still going to be stuck with creating your own workflow. There is no "developer workflow" from soup to nuts.

Red Gate does a lot of SQL Server developer software (in particular SQL Compare, SQL Source Control, and SQL Tests). You could search for their training videos for some ideas about how a workflow in these or other tools might look like. Again you can find these on YouTube, though they also publish some free downloadable books as part of their marketing.