Last post Jul 02, 2013 12:32 PM by geog272
Jun 18, 2013 04:49 PM|geog272|LINK
We are looking to update our websites to asp.net and to store the database on a separate server from the web files. The database will be access for now and sql server eventually. How can you connect to an access database on a separate server? Is storing
the database on a separate server a good or a bad idea, performance wise?
Thanks in advance!
Jun 18, 2013 05:31 PM|AceCorban|LINK
They can be on different servers, but they need access to eachother. Typically, both boxes will be on the same network, so you will need a network admin to set that up for you. Then you simply refer to the database by ip address and ensure that you create
a db user with sufficient credentials (and have mixed mode authentication set up).
Performance-wise, I am not sure what (if any) the significant difference will be. I seems like it would be more effiencent on high-load sites since the same box isn't rationing resources for multiple services, but I honestly don't know.
Jun 18, 2013 05:35 PM|jats_ptl|LINK
If your site is wuite big and needs lot of loading time you can go for different servers.
Many environments must deploy multiple servers to handle consistently high volumes of traffic, to support processor-intensive applications, to respond to sudden bursts in traffic, or to meet redundancy requirements.
Please have a look at the article below from Microsoft Support you will get the clear idea and steps for implementing it.
Hope this will help you.
Please "MARK AS ANSWER" if found helpful.
Jun 18, 2013 09:28 PM|oned_gk|LINK
storing access database on a separate server is a a bad idea, i think.
Its not affect performance only. Maybe you'll get into trouble when connecting to database.
You need database server
Jun 19, 2013 11:01 AM|geog272|LINK
Thanks for the responses. I've got it looking in the right place by using the IP address in the connectionstring:
<add name="TestConnectionString" connectionString="Provider=Microsoft.ACE.OLEDB.12.0;Data Source=\\IP address\C:\folder\db.accdb" providerName="System.Data.OleDb"/>
However, now I'm getting error messages about permission "It is already opened exclusively by another user, or you need permission to view and write its data."
I've made sure IUSR has permissions on the folder in which the database is located. In the past, this was all that was needed. Do you have any suggestions on why the permissions might be off? Is there something additional I need to do now that the database
is not on the same server as the website? It is IIS 8 and windows server 2012.
ETA - AceCorbin I see now you reference having a DB user setup. So, does that mean setting up a separate account other than IUSR... ?
Jun 23, 2013 06:42 AM|hans_v|LINK
Maybe you'll get into trouble when connecting to database.
For sure you'll have trouble connecting to an Access database on another server.
The database will be access for now and sql server eventually.
You better start with SQL instead of Access, because you'll have to rewrite a lot of code also because you;'ll need the OleDb namespace for Access and the SqlClient namespace for SQL. Also, the SQL syntax is different, so you'll have to rewrite some sql
Jun 24, 2013 09:18 AM|geog272|LINK
Unfortunately, I'm not the only player in this equation and it's not an option to use a SQL database right now. I know I will have to re-write some code -- 'tis the way of the world. I am going to try a bit more with connecting to the access database (haven't
had time to look at it since my last post) and would be interested to hear from anyone who has a positive experience doing so and how you went about it.
Jun 28, 2013 07:11 PM|hans_v|LINK
I am going to try a bit more with connecting to the access database (haven't had time to look at it since my last post) and would be interested to hear from anyone who has a positive experience doing so and how you went about it.
You're wasting your time!!! Acces isn't a real database, it is a file based system. In order ro use Access, you'll need to have Modify permissions on the folder where the mdb file is located.....
You'll only find experiences of people that have been struggeling with the same problem, I never seen a positive experience!
Jun 28, 2013 07:35 PM|oned_gk|LINK
Jul 02, 2013 12:32 PM|geog272|LINK
My wonderful IT specialist was able to solve this. He created a domain just for the webserver, so that it could talk to the database server in the other domain. Then, created a user and assigned it in IIS to the app pool. Set up a trust relationship between
the webserver and the domain server. Then, on the server that hosts the database, find the user previously created and assigned to the app pool, give it permissions on the database. This was IIS8. I think previous versions of IIS could maybe use IUSR but
not IIS8. The connection string was simply:
<add name="TestConnectionString" connectionString="Provider=Microsoft.ACE.OLEDB.12.0;Data Source=\\IP\path to db.accdb;" providerName="System.Data.OleDb"/>
I know Access is Very Bad, capital V, capital B. But this is what we have to work with until the organization is ready to change. Hopefully this will be helpful to someone else, in the same situation.