SQL Server- 40+ programming students need own copy of the data

Tom Dunne

I seem to have assumed the role of system Admin/Go to guy in the college I work at. My latest task is to configure MS SQL server for access by 40 plus students. l would love to get some opinions on my proposed set up. I have full admin rights on the server and SQL Server is installed with the Northwind database. My background is in Oracle, its been a long time since I worked on SQL Server.

It's a .Net programming course, so each student will have their own login to SQL Server, so they can play around without affecting each other's data.

l was thinking of one, read only copy of the North wind database, so students can can copy it into their own schema as needed, or more likely, should they mess it up.

I assume each student will use their own login to the DB when setting up a Visual Studio data source.

Does this sound reasonable? Am I missing anything obvious?

Jim2007

Assuming you have a decent server, I would just create a database for each student and to make life easy, give them DBO on their own database.

SQL Server schemes are not exactly like Oracle schemes and I think it would become very time consuming for you, if you were to do it that way.

Out of interest why not just use SQL Server Express or SQL Server CE on the desktop, if it is just for students?

ChRoMe

Jim2007 wrote: »

Assuming you have a decent server, I would just create a database for each student and to make life easy, give them DBO on their own database.

SQL Server schemes are not exactly like Oracle schemes and I think it would become very time consuming for you, if you were to do it that way.

Out of interest why not just use SQL Server Express or SQL Server CE on the desktop, if it is just for students?

What he said, give each of them a DB locked to their individual account. Anything else is asking for trouble.

seamus

Yep, totally scriptable too. Create a copy of the database for each student, create the user logon and give them owner rights over their own database and public-only access to the database server. Means when they login they will only be able to see and work with their own database.

If it's scripted, it means you can refresh the environment as needed too.

Tom Dunne

Jim2007 wrote: »

Out of interest why not just use SQL Server Express or SQL Server CE on the desktop, if it is just for students?

Two main reasons:

1: Our ICT Support is beyond abysmal. They really, really try their best, but sadly, anything out of the norm is beyond them. We already have issues with slow response times of the student PCs (see point 2 below). Probably why I end up doing most of the techie stuff for our department

2: The build we have on the student PCs is already bloated beyond reason. In the overall schemes of things, this approach is the lesser of several evils

Seamus, I had hoped to script most of this - are you talking about something along the lines of a "CREATE DATABASE" type script? Along with associated population of the database data?

Or, given that I already have an instance of the Northwind DB up an running, is there a better way of doing it (copy DB, perhaps - a quick Google suggests that this is possible)?

ChRoMe

Tom Dunne wrote: »

2: The build we have on the student PCs is already bloated beyond reason. In the overall schemes of things, this approach is the lesser of several evils

To be honest having it centralized is no bad thing.

seamus

Tom Dunne wrote: »

Seamus, I had hoped to script most of this - are you talking about something along the lines of a "CREATE DATABASE" type script? Along with associated population of the database data?

Or, given that I already have an instance of the Northwind DB up an running, is there a better way of doing it (copy DB, perhaps - a quick Google suggests that this is possible)?

Yeah, there are a few ways of doing it. There's a copy database function in the GUI but that creates an SSIS package, so I'm not sure how scriptable it is.

One thing you can do is take a backup of an existing database and "restore" it to a new one.

So take a backup of your database and store the file in a specific place.

Then you can use that backup to create a new one using the kind of script as below, looping through the process for each student.

/*INSERT CODE HERE TO DROP THE DATABASE IF IT EXISTS */

RESTORE DATABASE [Northwind_student01] FROM  DISK = N'E:\NW_master_copy.BAK' WITH  FILE = 1,  MOVE N'NorthWind' TO N'E:\DATAFILES\Northwind_student01.mdf',  MOVE N'Active_log' TO N'F:\LOGFILES\Northwind_student01_log.ldf',  NOUNLOAD,  STATS = 10
GO

/* INSERT CODE HERE TO CREATE THE NEW USER AND ASSIGN THEM AS DBO TO THE DATABASE */

amen

why not just script the db as a script including the data?

Jim2007

amen wrote: »

why not just script the db as a script including the data?

It would be my approach too, then the students can rebuild it as often as they want.

I expect there must already be a few scripts around for the Northwind database.

DublinWriter

seamus wrote: »

creates an SSIS package

...which is basically an XML file that you can edit.