Dutch professional university upgrades 3.70 - 5.0 and to SQL

hvdhoeven

Dutch professional university upgrades from 3.70B to 5.0 and to SQL
As a lecturer at Avans Hogeschool, a professional university in the Netherlands, we use Navision to teach our students what ERP is.
So, we do not use Navision for our own business processes, buit only as an educational vehicle. We use a free educational licence for Navision. (200 licences)

At this moment the Dutch professional universities uses Navision 3.70B ans C/SIDE.
We plan to migrate to Dynamics NAV 5.0 and also to SQL server as DB platform. This will ben done before summer 2008.

In this educational setting we use six different orginal enties for for specific cases (books), that are called
- red
- blue
- green
- green-light
- minor
- cronus
For every case whe have got a database (with the same ‘colour’ name)
All cases are used by students (every student has his own Navision company) for 10 weeks.
After that the database is no longer of uses and is overwritten by the original entry for that case (by means of restoring a backup).
At present we can just copy the original database to the one that is not in use anymore, thus preparing a new startsituation. This is done through use of the Windows Explorer.

We have put 40 (the same) companies in one database. Also 40 user accounts (database-type) and the rule is: student01 uses company01 and so on.

These autorisation rights are not influcenced by copying. At the moment we have 20 databases running. Every database has the same user names and company names in order to simplify the copying process.

Now when we migrate to SQL, the simply copying is over, so I’ve been told. In SQL every user has to have his own unique user name. Consequence is that we have to expand the number of databases, because we have to attach users to specific database. Each database has to remain the same colour, so I have understood. If this is true, I would like to receive your tips and trics how to set up and maintain this large installation. I think I have to create at least 2000 users in SQL Server (and also in NAV). We plan not to use active directory.

Is there a way to use a script to
-create users in SQL and in NAV and at the same time setting the database and company they can use and also set the authorisation rights for that user at the same time? Do you have an example (of some examples) for me?

Another interesting topic is the increase in the use of the SQL Server report engine instead of the NAV reports. Maybe you can suggest the most convenient way for us to install the report generator of SQL Server? Is there only one way with a centralized databaseserver? Do we need additional settings in SQL Server? Can we block critical settings for students (we don’t want them to damage the installation).

We are not a commercial customer of Dynamics NAV, so offerings from consultants may not be so usefull for us, unless they are free.

Thanks a lot for your posts.
Best regards from the Netherlands,

Hans van der Hoeven
Avans Hogeschool
jpm.vanderhoeven (at) avans.nl

hvdhoeven

Please...

Joe_Mathis

Hi,

Kind of surprised that no one gave an answer... :?
Good thing you said please.

Of course I am making an assumption that no one is developing on these databases, because you would definately want everyone to use their own and cause their own problems.

In this educational setting we use six different orginal enties for for specific cases (books), that are called
- red
- blue
- green
- green-light
- minor
- cronus
For every case whe have got a database (with the same ‘colour’ name)

So far it looks like you'll need 6 databases in one SQL instance.

All cases are used by students (every student has his own Navision company) for 10 weeks.
After that the database is no longer of uses and is overwritten by the original entry for that case (by means of restoring a backup).
At present we can just copy the original database to the one that is not in use anymore, thus preparing a new startsituation. This is done through use of the Windows Explorer.

This would be different. After setting up each of the six databases you would need to make a backup using the SQL tools. (SQL Server Managment Studio for SQL 2005.) When the term is over you would restore the databases with your 6 backups with the overwrite option selected.

We have put 40 (the same) companies in one database. Also 40 user accounts (database-type) and the rule is: student01 uses company01 and so on.

This is still possible.

You need to add the users to the Security>Logins of your server instance.

Then inside Navision add the users (student01 - student40) to each database.

I would use the roles to limit which company goes with which student. So say for instance that you are giving the "Super" permission to student01. You would only give it for company01. Then they would error out when trying to log into the other company.

NOW IS WHEN YOU MAKE YOUR BACKUPS. :shock:

These autorisation rights are not influcenced by copying. At the moment we have 20 databases running. Every database has the same user names and company names in order to simplify the copying process.

If I am following correctly you use different databases for different classes?

To work around this you could set up a new SQL server instance for each class and have the same 6 databases with the same 40 users and customers in them (restoring from the 6 backups). The trick here is to use a script to move all of the logins to each instance for each class.

Microsoft has a script that will move logins between servers by running a stored procedure that generates another SQL script that you run on the server you are moving the logins to.

http://support.microsoft.com/kb/246133

I have successfully used Method 1.

It retains all the permissions and passwords for all the logins. Of course if the students change the login passwords you would still need a script to reset them.

This also complicates the restore procedure, you would have to restore all of the databases in each SQL Server Instance, but it isn't as bad as it sounds.

If everyone is using their own computers, you could install the free SQL Express and just have them install the backup of the database on their own machine on the first day of class. Then you would only need to make your 6 databases and pass them out. It comes with almost all the grown up tools and allows for a 4GB database. It also can run the reporting services.

Another interesting topic is the increase in the use of the SQL Server report engine instead of the NAV reports. Maybe you can suggest the most convenient way for us to install the report generator of SQL Server? Is there only one way with a centralized databaseserver? Do we need additional settings in SQL Server? Can we block critical settings for students (we don’t want them to damage the installation).

There are issues with report server and Navision databases with multiple companies. The tables are per company for the most part. So if you were to write a SQL statement you would have to use a table called company01$Customer for company01 or company02$Customer etc...

I had talked to a couple of Microsoft guys at Directions 2007 about how to work around multi-company reporting, but wasn't given a real good answer. They were talking about late attaching, but this is more like missed the bus attaching. I have been able to do it by creating stored procedures, but I'm not sure that is the best way... It's just what we made work.

As far as report server, it comes standard with SQL server. I believe that you can set up multiple instances on an IIS machine, but I would have to ask you to find someone more experianced to assist you with that setup. Probably your IT staff at the university.

I hope that this helps...

What classes do you offer?