I'm a sysadmin at a university. One of our College of Business programs is changing their curriculum. They are going to be teaching a database course this coming fall that potentially has 120 students. The students need to be able to create their own databases and then have dbo rights to their databases. The instructor needs to be able to connect to the students' databases.
We currently have a data analytics class that uses MSSQL but that course only has 30 students. For that course we have a single server with 30 instances of SQL/RS/AS and each student owns an instance. I'm not sure that scales well to the new course.
Approaches I've considered:
- Current model; Huge server; Instance for each student - performance questions; not desirable in our VM infrastructure; easiest to maintain
- Server per section - same as above but smaller servers (better in our VM environment); slightly higher maintenance; mild inconvenience to instructor
- Single server; single instance - Somehow use schemas to accomplish the user separation and permissions - less resource intense; not sure if this is viable or how to start; have one of our DBAs looking into it
- SQL as a service on Azure - Might be cost prohibitive on consumption; instructor connectivity could be challenging
- Azure Lab Services - facilitating instructor access is the complicating factor. Azure Lab Services isn't currently peered with our campus network; students can wreck their own machines and recovery will be more difficult
Anybody have any experience or thoughts on what might be the best way to proceed?
