If it's not too late, one compromise option that I've seen work well is rather than upgrade the permissions or replacing the developers' existing accounts, create a separate account that is only used when they need the elevated permissions.
So normally they work under individual "restricted" accounts (which I use loosely because these restricted accounts still need some hefty permissions — ie create, drop, alter for tables). But for that rare occasion when they think they need sa
, they can log in using this account. Then you can flag the account in your logs and do extra monitoring on it. You've given the developers the access they asked for, but in a way that's a little more controllable.
Eventually, if there's abuse, the logs on this account can be used as evidence to take it away.
Managing environments
I think you definitely don't want to be forced into a single database version. You've got enough developers that you will inevitably have multiple development work streams, and requirements to apply patches to the current production environment independent of development workstreams.
You can use Liquibase or a manual process to produce patch scripts to upgrade versions. I suggest starting out with a manual process and using the schema comparison tool for QA on the patches. Clean, automated, transparent synchronisation of a nontrivially complex database is a bit utopian.
Your central data model can be kept in whatever system takes your fancy. I've used everything from tedious enterprise repository tools to create table scripts. Create table scripts play nicely with ordinary source control tools such as subversion and not all repository tools do a good job of versioning.
Whatever you use as your master data model repository you need a fairly clean mechanism for deploying an environment from that model. It should be structured so that rollouts to an environment are easy. You also need a mechanism to patch from one released version to the next.
What I've done
I've done the following in the past when I was managing development environments. It's not particularly high tech, but it's amenable to version control and automated builds, so it makes it easy to roll out an environment to a specific version, and maitaining a large number of environments is quite practical.
Maintain a central repository: This could be a set of database creation scripts held in a version control systems, or a repository model in a data modelling tool. Take your pick. This model should have a build mechanism that allows an environment to be rolled out from the scripts without a lot of manual intervention.
If you have a lot of reference data you will need a load mechanism for it. Depending on how you want to do it, you could keep this in a database or in a set of files. The advantage of files is that they can also be versioned and labelled from the same version control system as your code base. A bunch of CSV files and bulk loader scripts in a source control repository can do this easily enough.
One option for deploying development environments is to take backups of the production database patched to the appropriate version and make them available for devs to restore into a development environment.
Make it easy to roll out: Like any CI build process, the database should be deployable from a single script. Set it up so that database connections can be paramaterised, or the script is location independent and can just be run through the connection.
Patch scripts: You will need roll forward and probably roll back scripts from each released version.
Build test environments from the repository model: This ensures that development on environments that are out of sync with the repository gets caught in testing.
Test the deployment process: Automated patching scripts, however they are created should be testable. Schema comparison tools are quite good for this, even if you dont't use them to generate the patch scripts.
Create a reference environment with the repository model build you tested against
Create a smoke test environment with either a backup of your production release or a build based on the current released version.
Run the patch script against the smoke test environment.
Use the schema comparison tool to compare the smoke test environment with the reference environment. The patch script should result in the two databases being identical, so you can investigate any differences.
What I like about this process
This is a bit heavyweight, and was designed for deploying into fairly bureaucratic and opaque production environments. However, it has the following strengths:
Developers can tinker where they need to.
Multiple branches and streams of development can be accommodated.
The deployment scripts themselves can be tested in a repeatable manner. This is very helpful to shut down deployment bunfights, as the repeatability can be demonstrated.
The schema comparison tools provide QA on the deployment itself.
Testing is always done against what is expected to be released, and this will catch issues arising from environments being out of sync.
Deployment based on repository models and patch scripts means that uncontrolled rubbish doesn't get accidentally migrated from development environments into production.
A lot of the process can be automated, although it is often desirable to prepare and test the deployment patch scripts manually.
Environments are cheap and easy to deploy without having to jump through hoops.
Developers are forced to make a system that is amenable to a simple build and deployment process.
Developers are forced to learn basic database administration tasks, but test and production environments are insulated from noob mistakes.
How it addresses your requirements
Developers have local copy of data to run development code against
The deployment scripts or DB images mean that they can set up an environment from any version that's available.
Able to rollback database structure to a previous changeset
Again, sorted by the deployment scripts. Either through DDL or test database backup images created through a controlled process, developers can bring up an environment to any specific version that you have.
Able to separate new feature schema changes vs schema design fix changes
Patches to a common version can be maintained in a separate fork in the svn tree. If database backups are used as the reference environments they need to be stored somewhere with the same folder structure as the branching of the source control projects.
Able to modify database structure locally for testing
The simple deployment process allows devs to tinker, and easily restore an environment to a local state, or bring up a reference environment to do comparisons and make change sets against.
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.
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.