SQL Server 2012 Standard SP1 setup. Questions....

[TRdesigner82]

We just set up SQL Server and created a couple test projects to see how it would go. After doing that, we've got a couple questions that I was hoping someone could help me out with. I'm planning on pushing this to Autodest too. I'll copy paste the questions here from an email...

We are using SQL Server 2012 Standard w/ SP1 as a back-end for Plant 3D.

1. What SQL permissions are necessary for Plant 3D users?
2. It appears that every new project creates 5 new databases. Is this correct? Is there a recommended way to manage these databases? This could easily get out of hand once there are 100 project in the system.
3. What information is stored in the SQL databases vs. on the filesystem?

It looks like the project files that are created are the same whether using SQLite or SLQ Server. Is this correct and if so, what exactly is the server doing then?

[quentin.contreras]

1. Everyone that is going to be accessing the project needs to have permissions set on the server where the SQL is located. You or your IT department will need to determine these settings. Also, make sure your SQL is set so it is Windows Authentication mode. Your IT people should be able to help you out with this.

2.Yes, there are five databases that are generated when a project is created. The project knows the location of these databases and they should remain as placed. I have not heard of any problems with other users with the existing database structure.

3.There are differences with the SQLite and MS SQL Server database structure. MS SQL Server is a client/server architecture. Meaning the database transactions are passed from the client to the server, processed and then sent back.

MS SQL Server uses a granular approach to record locking. It only locks a single database row if it needs to be updated instead of the entire database.

SQLite and MS SQL Server are functionally identical. The differences are that SQlite is a file based and SQLExpress/Server is a server based. Each user who writes to a SQlite db locks the file for the duration of the write and the other users wait. Depending on each user’s activities, performance will slow down as you add users. With a server based database, only the records that are being written to are locked. So, it can scale to many more users without seeing performance degradations. SQL Server may also be more robust and have less chance of errors when you have many users working on a project.