1

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.

Improve this question
0

2 Answers 2

Reset to default
4

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.

Improve this answer
1
  • It's not so much about being determined to do it ourselves as being forced... We work for the government, and the people above us don't know a thing about DBA vs developer. They just expect us to produce a product.
    – Kristen Hammack
    Commented Jan 24, 2017 at 20:51
1

Community Wiki answer from question comments by Antoine Hernandez

I'm a proponent of using SQL Server Data Tools (SSDT) for development and source control. Most places I've seen that are .NET developers use Team Foundation Server (TFS) so natural step is to use SSDT for the database piece. Then you can treat your database development similar to your .NET development (assuming you have separate servers for the different environments). Using SSDT gives you source control, schema and data comparison tools, and a way to build the deployment of the database into your normal build scripts.

Source control is just one component of TFS and it can integrate with Git for the source control part no problem but that's not what you were seeking. As far as how to set up servers, each person that answers may have a different way of configuring the servers. Ideally, you would want your application server separate from your database server, have SQL Server services run with the separate, dedicated accounts, have the application use a dedicated, domain account to connect to the database, and have real data only in production. Anything else would depend on your available configuration options.

Not sure what version you are using but here is a good place to start:

Planning a SQL Server Installation (TechNet)

Improve this answer

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.