none
Multi-User Acces Using SQL Server Express

    Question

  • I am new to SQL Server but have been using Access to provide Multi-User acces to a central data store.  We are evaluating the replacement of Access with SQL Server.  With Access we don't need any special installation; I believe that the Jet Database Engine installed locally on each user's PC allows them to work with this in a disconnected manner; we use ADO.net 2.0 with VB.net 2005. 

     

    If each user has a locally installed SQL Server Express can they all attach to a database that resides on a shared network location?

     

    In another post on the Forum I see the following regarding multi-user access:

    Our application You will need to pick a central computer that everyone will be using the access the data and attach the file to the parent instance of SQL Express on that comuter, giving it a name that you can use to access it. Then you will need to modify your connection string to connect to the named database on the central server...

     

    Can multiple locally installed SQL Server Express instances attach to a single data store that resides on a shared network location?

     

    If not, what permissions do I need to create a parent instance of SQL Server Express?

     

    Thank you for helping me understand how to implement our solution.

     

    JudiR

    Thursday, March 27, 2008 1:48 PM

Answers

  • Hello,

     

    For each company, it's better to create a different database. All these databases may belong to an unique instance, that's to say there is only one "workstation" where is residing the Sql Server, with all the files residing on this workstation.

     

    I did not remember how many databases may exist on an unique instance ( at least 37,000 ).

    But before you arrive to 37,000 databases, you would have problem of memory on your workstation ( and also problem of drive spaces )

     

    To create a database, you have only to use a full creation script of a database.

    You have

    to read this script file in a String

    to change the name of the database in this String,

    to change the paths of the data file ( .mdf) and log file ( .ldf) in this String ( to reflect the name of the databas )

    and you use a SqlCommand with this String as commandtext.

    I used it to create upto 10 databases with same schema without no problem.

     

    Problems :

    - you have to check that a database with the same name doesn't exist already

    - to create a database, it's better to sysadmin or to have at least db_creator permissions

    - the creator will have to create the users ( so you must have this permission )

     

    I have not understood whether you want to use Sql server 4Express Edition ).

    If it's the case, Express Edition may not be able to use more 1 GByte of RAM.

    Higher the count of users, higher will be necessary the amount of RAM on the workstation where the Sql Server resides and higher will be the payload on it.

    I have already used with no problem an instance with 10 databases and 40 users ( there is no more connection governor with Sql Server 2005 Express Edition , as it was with MSDE 2000 before the SP1 ).

     

    I've succeeded to create on network databases, but i encountered problems so i prefer to put the data and log files on the drives of the workstation where is residing the instance of sqt Server Express.

     

    Choose the version with advanced services. You would have Reporting.

     

    Last thing : it's better to use SqlClient instead OleDb as it's native and you will have access to all the novelties and it's more efficient and quick.

     

    Have a nice day

     

    Friday, March 28, 2008 3:49 PM
  • Hello,

     

    1) I'm using Sql Server 2005 ( Express and Workgroup ) from its arrival. On the contrary of many people, i've alwyas considered Sql Server Express Edition as "the small brother" of the Standard/Workgroup/Entreprise and i rejected all the "special" uses of the Express Edition like user instances as it's not available for the "classical" and not free editions.

     

    2) You have in theory no limitation of connected programs ( except the Express edition is unable to use more than 1 GByte of RAM and the datafile may not exceed 4 GBytes )

     

    3) You have some limitations like replication,service broker, mirroring which are unavailable or reduiced to subscribers

     

    4) I realized during 6 months concurrency tests and i had never problems with 15 users connected to the same database on the same instances, except that some transactions have been slowed because the 15 users tried to write to the same table on the same instant. As the instance was installed on an AMD mono-core 2100+ with 512 MBytes RAM, i considered that was normal.

     

    5) on the contrary of ACCESS, when you program for Sql Server, you are on the Client/Server idea : that to say, you can't use the same process to access an instance from two computers. sqlserv.exe is mostly used for reading or writing the data. But the display of these data is using the resources of the computer where your prog is executing ( forget terminal server : it's a resource murder )

     

    6) there is a process ( Sqlserv.exe ) running on the computer where the instance is residing. Its role is to "serve" every client ( on the same computer or on another computer ) which is expressing a request of read or write on the instance ( a remark : officially, Sql Server Express Edition can't use several processors, but for Microsoft, several processors means several physical processors, for example a quad-core processor is considerd as an unique processor and multithreading is accepting with one condition : the same physical processor: if you have 2 separated processors on 2 different sockets E.E is unable to use them at the same time )

     

    7) Microsoft is not clear for the maximum number of users accessing  to the same instance , but the "bad time of load governor " of MSDE is away from 2002 : i believe that it exists a limit of something 32000 users acceding to the same instance ( except for some big entreprises, this limit is not annoying )

     

    8) I believe there is a limit of number of physical drives on which you may scatter your databases ( 16 ? )

     

    9) on the same instance of E.E. your are sure to are able to create at least 32,767 databases with at least 32,767 tables ( but the person who will create a database with 32,767 tables will the laughing stock of everybody )

     

    10) With Sql Server, there is a new type of file which appears : the log file ( or better the log transaction file ) where all the transactions ( update,insert,delete ) are written .So it's possible with this log file to rebuild tha contain of a database the day when this database is corrupted. You have to restore a backup copy and to  rebuild with the transaction log.

    As every insert,delete,update  begins to write the original records to the log file, you may have "bootleneck" as you are writing to an unique physical file.

     

    This is a summary of my own experience  ( my maximum number of users on a same instance is 24  at the same time ).

    Two importants points :

    - you must always foresee that an error is possible : so have to include in your prog the exceptions treatement ( to avoid to search desespairly why your prog is "bugging" ). In my programs, 25% of my code is for exceptions

     

    - SQL Server is suprising : the default option for locks is pessimistic, that to say, lock on the table(s) until the transaction is finished. So, under some circumstances, you may seem block , it's only because a lengthy transaction is running

     

    I hope i have been clear as english is not my mother tongue

     

    Have a nice day

     

    Sunday, April 06, 2008 2:47 PM

All replies

  •  

    I think you might have this backwards. You want to create a single, central store of data and have all of your users connect to it. You DON'T want each user to have their own, private, version of the data in a separate SQL Server Express database on their own desktop.

     

    If your users have been using Access to connect to an mdb. back end up to now, continue to do so. Connect those Front End mdbs (or accds) to your central SQL Server installation and proceed as before.

     

    I'm not saying that the upgrade to a SQL Server BE will not be without issues. It will, but certainly a lot less than replacing all of those Access Front Ends with some other Front End application.

     

    Morover, even if you DO put a copy of SQL Server Express on each user's desktop, you'll still need to provide a FE capability of some kind to them so they can get to the data in that server. And, again, that sure sounds like a good way to leverage the existing mdb FEs.

     

    Keep in mind that Access is both a data store (tables) and a tool in which you can create and run your interface (forms, queries, reports, etc,) SQL Server is a data store. It has some cool tools for manipulating data (stored procs, etc.). It has NO interface capabilities on its own.

     

    Long story short, an Access FE to ONE central SQL Server BE makes sense, IMO.

     

    Thursday, March 27, 2008 2:42 PM
  • Thanks for the prompt response. 

     

    Our application collects data for regulatory reports, similar in nature to a tax return or accounting for a single division of a company.  Each report the user must create goes into a separate data store, but more than one user at a company may need to be entering and analyzing data for a single report at the same time.  We have all resource data and many querys in a single mdb which we refer to as the master, and each time the user starts to prepare a new report, we copy and rename a template mdb that holds empty tables and that mdb becomes the data mdb.  The data for different reports is stored separately for business reasons.  We distribute a Windows Forms application that produces reports for a single regulatory period. 

     

    I would like to keep this model but have been told that SQL Server is more robust than Access, so I am trying to understand how to implement a similar process using SQL Server.  It sounds like each time a user needs a new data store, that we must programmatically create a new database in a parent instance of SQL Server Express.  Is that correct?

     

    What permissions do I need to create a parent instance of SQL Server Express?

    What needs to be on each users desktop to allow access to this parent instance of SQL Server Express?

    Is there a way to create a new mdf as a copy of an existing mdf?

    Could you point me to a sample of the code I would need to accomplish this?

     

    Thank you

     

    Judi

    Thursday, March 27, 2008 3:25 PM
  •  

    Wow. That creates a management task of serious proportions. I can't imagine how many different mdbs you must have to keep track of in your system. I understand if your business rules REQUIRE physical separation of the data, but I'm not sure that there is any factually greater degree of security gained by doing so. I mean, if person "A" has permissions on a folder containing 20 mdbs for 20 different reports, then it really doesn't matter that they are physically separate files. Person "A" can open each and every one of them. The only thing that is potentially gained is having to copy 20 different mdbs instead of one to get all that data onto a DVD.

     

    Anyway, that's just my two cents.

     

    Let me comment on each of your questions:

     

    "It sounds like each time a user needs a new data store, that we must programmatically create a new database in a parent instance of SQL Server Express.  Is that correct?"

     

    I don't think that generating a new parent instance of SQL Server each time would be necessary, but that really would require consulting with the people who determine that policy.

     

    Each time you create a new database within a server instance, you generate a new mdf file for it, although the default location for these files is going to be the same. So, if you must maintain a physically separate file for each report, then I would imagine that creating databases on a server instance should satisfy the requirement, all else being equal. 

     

    Keep in mind that SQL Server security allows you to manage permissions for each database separately, so having all of the databases in a single instance might meet your requirements. They'll be locked for the users to wom you give permissions.

     

    You can create a stored procedure to script your new databases from existing ones: I don't know of any demos, although a search should turn up some. Here's where I would start.

     

    In the SSMS, which the is tool you use to manage your server, you can right click on your "template" database and select "Script Database to" from the shortcut menu. Send it to the query window where you can edit it. It should generate a script which creates a copy of that database. Of course, it will default to the same name as the current db and the same properties, which may or may not be appropriate. When you run this script, you'll need to replace the db name in it with the new name.

     

    I searched for some simple demos, but nothing i found was really simple.

     

    "What permissions do I need to create a parent instance of SQL Server Express?"

     

    You'll need network admin rights on the server where you plan to install it. I'm assuming that you do want to make this a single, central installation.

     

    "What needs to be on each users desktop to allow access to this parent instance of SQL Server ?" Again, this depends. If you use a single central server instance, they only need to have the current Access FE. You'll need to create a stored proc that they can run from within a pass-thru query if you want to give them access to the script that generates new databases on the server. See the comments above re scripting.

     

    "Is there a way to create a new mdf as a copy of an existing mdf?" Yes, see the above comments re scripting.

     

     

     

     

     

     

    Friday, March 28, 2008 1:57 AM
  • I'm sorry if I was not clear; we produce a commercial application.  Each company that purchases our software installs the application and manages their own data.  Some of these users have several divisions, and each creates separate reports on the distinct and separate portion of business they represent.  However each report that is produced (the end product which consists of multiple files in different formats) may be prepared by multiple users.  So this is not a situation where the common data store serves many reports, although it could.  An analogy is a small business that operates as 2 companies and uses QuickBooks for accounting and TurboTax for taxes.  Each company of this small business would have a separate set of files for each application, even though they may share a single installation of QuickBooks and/or TurboTax.

     

    Also we do not use Access as a front end; our application is generated in Visual Studio 2005 in vb.net and uses an Access datastore, none of the User interface depends upon Access.  I believe that all we will need to do is change our ADO.net code that currently uses System.Data.OleDb to use System.Data.SqlClient.

     

    Thank you for the info on generating a script.

     

     

     

     

    Friday, March 28, 2008 1:07 PM
  • Hello,

     

    For each company, it's better to create a different database. All these databases may belong to an unique instance, that's to say there is only one "workstation" where is residing the Sql Server, with all the files residing on this workstation.

     

    I did not remember how many databases may exist on an unique instance ( at least 37,000 ).

    But before you arrive to 37,000 databases, you would have problem of memory on your workstation ( and also problem of drive spaces )

     

    To create a database, you have only to use a full creation script of a database.

    You have

    to read this script file in a String

    to change the name of the database in this String,

    to change the paths of the data file ( .mdf) and log file ( .ldf) in this String ( to reflect the name of the databas )

    and you use a SqlCommand with this String as commandtext.

    I used it to create upto 10 databases with same schema without no problem.

     

    Problems :

    - you have to check that a database with the same name doesn't exist already

    - to create a database, it's better to sysadmin or to have at least db_creator permissions

    - the creator will have to create the users ( so you must have this permission )

     

    I have not understood whether you want to use Sql server 4Express Edition ).

    If it's the case, Express Edition may not be able to use more 1 GByte of RAM.

    Higher the count of users, higher will be necessary the amount of RAM on the workstation where the Sql Server resides and higher will be the payload on it.

    I have already used with no problem an instance with 10 databases and 40 users ( there is no more connection governor with Sql Server 2005 Express Edition , as it was with MSDE 2000 before the SP1 ).

     

    I've succeeded to create on network databases, but i encountered problems so i prefer to put the data and log files on the drives of the workstation where is residing the instance of sqt Server Express.

     

    Choose the version with advanced services. You would have Reporting.

     

    Last thing : it's better to use SqlClient instead OleDb as it's native and you will have access to all the novelties and it's more efficient and quick.

     

    Have a nice day

     

    Friday, March 28, 2008 3:49 PM
  • Thanks for the advice, and yes since SqlClient is native I see it as an improvement that we should make in the near future.

    I appreciate the help

    Judi

     

    Friday, March 28, 2008 4:32 PM
  • I have a similar type of commercial VB6 application that uses an access database as a datastore.  Multi-user setup (for a small number of users) is easy.  All it requires is a shared folder on a file server.  When the user first installs the application, he/she is prompted.  The first user creates the file in the shared folder.  The second user browses for the same file. 

     

    We're writing our next version in .net, and would like to use SQLExpress.  Can we use the same process?  Can two users each  have SQLExpress running on their own PC, but open a shared .mdf file on a file server concurrently? 

    Sunday, April 06, 2008 10:42 AM
  • Hello,

     

    1) I'm using Sql Server 2005 ( Express and Workgroup ) from its arrival. On the contrary of many people, i've alwyas considered Sql Server Express Edition as "the small brother" of the Standard/Workgroup/Entreprise and i rejected all the "special" uses of the Express Edition like user instances as it's not available for the "classical" and not free editions.

     

    2) You have in theory no limitation of connected programs ( except the Express edition is unable to use more than 1 GByte of RAM and the datafile may not exceed 4 GBytes )

     

    3) You have some limitations like replication,service broker, mirroring which are unavailable or reduiced to subscribers

     

    4) I realized during 6 months concurrency tests and i had never problems with 15 users connected to the same database on the same instances, except that some transactions have been slowed because the 15 users tried to write to the same table on the same instant. As the instance was installed on an AMD mono-core 2100+ with 512 MBytes RAM, i considered that was normal.

     

    5) on the contrary of ACCESS, when you program for Sql Server, you are on the Client/Server idea : that to say, you can't use the same process to access an instance from two computers. sqlserv.exe is mostly used for reading or writing the data. But the display of these data is using the resources of the computer where your prog is executing ( forget terminal server : it's a resource murder )

     

    6) there is a process ( Sqlserv.exe ) running on the computer where the instance is residing. Its role is to "serve" every client ( on the same computer or on another computer ) which is expressing a request of read or write on the instance ( a remark : officially, Sql Server Express Edition can't use several processors, but for Microsoft, several processors means several physical processors, for example a quad-core processor is considerd as an unique processor and multithreading is accepting with one condition : the same physical processor: if you have 2 separated processors on 2 different sockets E.E is unable to use them at the same time )

     

    7) Microsoft is not clear for the maximum number of users accessing  to the same instance , but the "bad time of load governor " of MSDE is away from 2002 : i believe that it exists a limit of something 32000 users acceding to the same instance ( except for some big entreprises, this limit is not annoying )

     

    8) I believe there is a limit of number of physical drives on which you may scatter your databases ( 16 ? )

     

    9) on the same instance of E.E. your are sure to are able to create at least 32,767 databases with at least 32,767 tables ( but the person who will create a database with 32,767 tables will the laughing stock of everybody )

     

    10) With Sql Server, there is a new type of file which appears : the log file ( or better the log transaction file ) where all the transactions ( update,insert,delete ) are written .So it's possible with this log file to rebuild tha contain of a database the day when this database is corrupted. You have to restore a backup copy and to  rebuild with the transaction log.

    As every insert,delete,update  begins to write the original records to the log file, you may have "bootleneck" as you are writing to an unique physical file.

     

    This is a summary of my own experience  ( my maximum number of users on a same instance is 24  at the same time ).

    Two importants points :

    - you must always foresee that an error is possible : so have to include in your prog the exceptions treatement ( to avoid to search desespairly why your prog is "bugging" ). In my programs, 25% of my code is for exceptions

     

    - SQL Server is suprising : the default option for locks is pessimistic, that to say, lock on the table(s) until the transaction is finished. So, under some circumstances, you may seem block , it's only because a lengthy transaction is running

     

    I hope i have been clear as english is not my mother tongue

     

    Have a nice day

     

    Sunday, April 06, 2008 2:47 PM
  • Hello,

     

    Sorry, but i've forgottent to speak you about connection

     

    You have two ways of connection authentification :

     

    - the windows authentification which is the default. Useful when the users are accessing thru a domain network ( with a Windows Server 2003 or 2008 ), but nearly impossible to use when the network is a workgroup one ( without a windows server ). It's maybe the case when the computers have Windows HP Home as OS ( it's possible to connect with windows authentification if the Sql Server instance is on a Windows XP and the users are working on XP Home, on contrary, it's impossible ). The security controls are made by Windows. The connected person is using his own Windows user as username

     

    - the Sql Server authentification is the older way and is desactived by default. But there is a small problem, the "sa" login ( default system administrator ) is the only one login available for deblocking a corrupted database. Moreover, the Sql Server authentification is the only one available way when your are working on working network ( without Wondows Server ). If you decide to activate the Sql server authentification, it's nearly always possible except when the database "master" is corrupted. So you have to do it from the beginning and to distribute scarecely the sa password as it permits everything on the whole databases of the Sql Server instance. The connected person is using a Sql Server login

     

    When you will create your database(s) , foresee the security problems: who is able to read or write,delete,update a table. With Sql Server, you have 2 possibilities :

    - to give personnalized permissions for each username  or login

    - to group usernames or logins (? never used for me ) in application roles with pre-defined access permissions ( good solution when you can regroup a certain number of connected people with the same rights and especailly a time gain )

     

    Have a nice day

     

    Sunday, April 06, 2008 3:29 PM
  •  Papy Normand wrote:

    Hello,

     

    4) I realized during 6 months concurrency tests and i had never problems with 15 users connected to the same database on the same instances, except that some transactions have been slowed because the 15 users tried to write to the same table on the same instant. As the instance was installed on an AMD mono-core 2100+ with 512 MBytes RAM, i considered that was normal.

     

     

    I have the Smae proplem , I am using vb.net with Ms Access database for multi users , the user install the software then share the database folder to the other users to use the same database.

    now i want to use sql express edition , when i use it on the local machine it works fine , but when i am trying to connect it on other machine - the same i do with Ms Access - It gives Error

    Could you blease help me , as you said you have connected with 15 user at the same database

     

    my connection string is :

       ConnectionString = "Data Source=.\SQLEXPRESS;AttachDbFilename=" & appPath & ";Integrated Security=True;User Instance=True"

     

    appPath  :  is the location that the user selected for the database ( if loclal it will be for example c:\databasename.mdf , if server it will be \\server \folder\databasename.mdf - like Ms Access-)

     

    Sunday, April 27, 2008 3:32 PM
  • Hello,

     

     

    I've seen that in the connection string you have User Instance = True

    Have a look on the excellent article that Mike Wachal made  at this link :

    http://blogs.msdn.com/sqlexpress/archive/2008/02/22/sql-express-behaviors-idle-time-resources-usage-auto-close-and-user-instances.aspx

     

    go to the part treating of RANU and you will understand why you have an error : no remote connection with user instances

     

    I'm always growling against user instance : it's a good thing but with 3 importants limitations :

    - no remote connection

    - no Sql server authentification ( sa is really useful in some emergency cases )

    - it's specific to the Express Edition of Sql Server and not implemented into the higher editions ( Standard,workgroup,entreprise...)

     

    I think that user instance is quite nice because you develop very quickly with VC#/VB Express Edition you have SqlConnection,SqlCommand,DataSet in the toolbox : that's handy

     

    When in 2005, i discovered VC#/VB/ Sql Server Express Edition , i was glad: i'm developping quickly , but when i wanted to connect from another machine : horror, it was impossible and in these times, articles of Mike were not existing and i'd to search why.

    I understood the best way was to consider Sql Server as a nearly normal Sql Server with some limitations :

    - the size of data files of a database no more than 4 GBytes

    - 1 GBytes of RAM used by the instance

    - backup/restore more complicated

    - no complete replication

    - no complete broker service

    - only one physical processor ( but a multi-core processor is correctly used )

     

    For a developper , it's the good edition ( and free... )

    But please forget all the dataset,sqlconnection,sqlcommand wizards.

    You will have to code everything : ok it needs more time but you know what you are writing and it will be easier to modify ( don't forget to comment : you don't know whether you will not have to modify your code in 12 months  and i've many doubts about your capability to remember what you have done in your prog )

     

    When you are using wizards, the code is nearly always the same but you will never have a look on it.

    If you code everything, you will discover how to use methods and properties of SqlConnection or SqlCommand and you will learn and moreover, you will remember easerlier the pittfalls which have annoyed you.

     

    If you are an hobbyist, ok my advices are unusefull, but if you are a developper, it will be very useful and you will be recognized at work

     

    When you want to build your connectionstring, try to use SqlConnectionStringBuilder. You will be surprised by the facility of Intellisense to write a connectionstring without any error.

    For exemple :

    SqlConnectionStringBuilder bu = new SqlConnectionStringBuilder();

    bu.DataSource = "computer\instance";

    bu.InitialCatalog = "databasename";

    bu.IntegratedSecurity = true;

    SqlConnection conn = new SqlConnection();

    conn.ConnectionString = bu.ConnectionString;

     

    I wrote all this code from my memory ( it's automatic for me ) and i hope without no error

     

    If you define your SqlCommand by yourself, you will learn what is really a parameter, or how to execute it.

    A long process but it will permit you to find very quickly your errors : i applied it frm the beginning of my developper career ( some 28 years away for business deveppement and 38 years with scientific )

     

    In less 3 months, i nearly mastered the main classes of System.Data.SqlClient and i think that everybody is able to do the same thing

     

    Last thing : AttachDbFileName is mainly useful for user instance

    if you use DataSource, you have not to know the complicated path for the data file., you have only to know the database name ( fewer errors...) and more readable code

     

    I was really long but i've seen so many times your error in this forum that i needed to tell you what i'm thinking of user instances

     

    Last advice : keep trace of the Mike Wachal blog ,it's filled of many very good ideas and clearly explained.

     

    Have a nice day   

     

    Sunday, April 27, 2008 5:03 PM