changing MS Access to MS SQL DB as Website Backend DB

Question

changing MS Access to MS SQL DB as Website Backend DB

Tanmoy Das

SSC Rookie

Points: 36
More actions
October 4, 2006 at 12:09 pm

#367520

Can any body Tell me how to put up a MS SQL Data base to a website. Curently I am having an access database as backend. But I have throught of changing it to MS SQL Data base. It would be nice it some body elaborates me the process and steps involved. Also I have another quesion How much record In genreal how much data can an access database hold without significant drop in performance.

Viewing 7 posts - 1 through 6 (of 6 total)

You must be logged in to reply to this topic. Login to reply

Ian Griffiths Ten Centuries Points: 1025 More actions · Answer 1

Wow, where to start...?

The first thing to think about is the capacity of the PC you are running the database on. I assume that you've got a single PC server which is running both the web server and database. SQL Server has much higher requirements in terms of memory, processor and disk space than Access does, so you may find your PC won't even run it very well, if at all.

Assuming your PC can run SQL Server, the simplest way to go is to install the free version (SQL Server 2005 Express) on the PC, taking all the default options. You then have to move the database structure and data from Access into SQL Server. You also have to create a user account in both SQL Server and in the actual database (creating the account in SQL Server gives you the ability to attach to that instance of SQL Server as a whole but not to any database, adding that account to a particular database then allows you to give authority to read and write data in that database). If you're running an ASP.Net website with anonymous login, the Windows account you need to give access to will probably be NT Authority\Network Service if you're going to run with integrated security, or you could create a SQL Server account if you're not going to run with integrated security (in which case, you have to pass the user ID and password in with the connection string). Any other type of site, you'll have to look elsewhere for which accounts to set up.

The next thing to do is to change your connection strings in your application. Hopefully that'll be stored in just one place in a good app, but may be all over the place in a poorly-structured one. And if you're using ASP.Net, you may want to change the ADO.Net classes you're using from the OleDb ones you need for Access to the SQL ones for SQL Server. The classes are virtually identical, and the SQL classes give better performance.

I may have missed a few things, there's quite a lot to do if you don't know what you're doing. It's not too difficult once you've done it once or twice though, not as complex as it looks.

As to when Access runs out of steam, how long is a piece of string? Access doesn't perform well with multiple users, and if you have more than a few tens of thousands of rows in any table then I would expect it to start slowing down fairly rapidly.

AJN SSC Enthusiast Points: 195 More actions · Answer 2

Just to clarify

MSAccess db can be up to 1GB

MSDE - 2GB

SQL2k5Express - 4GB

Mike Prascak SSC Eights! Points: 930 More actions · Answer 3

in terms of Access performance & capacity,

if you are talking "how many users" , I agree with Ian, all but the simpliest of app's won't handle more than a few simultaneous users

in terms of "how many records", (single user, single PC) Access will easily handle tables with 100,000 records without a problem. We've gone up to a million and still had acceptable performance.

SQL advantageous: multiple users, security, flexibility, industrial strength reliability (Access will hang now and then), scalability.

scolvard SSC Rookie Points: 39 More actions · Answer 4

Hi AJN:

But for even further clarification, Access is actually 2Gb just like MSDE. This is from the access documentation:

High performance and scalability

In many situations, Microsoft SQL Server offers better performance than an Access database. SQL Server also provides support for very large databases, up to one terabyte, which is much larger than the current limit for an Access database of two gigabytes. Finally, SQL Server works very efficiently on Microsoft Windows NT by processing queries in parallel (using multiple native threads within a single process to handle user requests) and minimizing additional memory requirements when more users are added

Tanmoy Das SSC Rookie Points: 36 More actions · Answer 5

Thanks for the response, but all I want to know that how to upload the ".mdf" file of MS SQL to a Web Server. Even if I have upload the "*.mdf" file, I suppose it would not work as becasuse the "*.mdf" file is not mapped withn the MS SQL Server of my Web host. and My web Host says that -

"Due to security considerations, acces to the SQL server database through Enterprise Manager tool is not supported. We encourage and recommend using the Query Analyzer tool for all client database connectivity."

What the hell does that mean? How I am suppose to connect my "*.mdf" or Backup File to the Web Server's Data Base (i.e MS SQL Server). Or is it that they will connect /remap ny Database file for me.

OR - Does it mean that I have to type in some command to connect it myself. If that is the case can any one elaborate me the steps involved to remap the ".mdf" file, Or just How I am suppose to shift my actual MS SQL Server Data file to annother MS SQL sever i.e hosted over internet.

I am working with ASP. Previously I have worked with MS SQL Server, but that was just for test case and it was a Server Located in my School Lab. I have never migrated my Database before. This will be the first time I will be migrating (i suppose I would be using DTS for that purpose) and also moving this local ".mdf" file to a real Web Service Provider. By the way I suppose My Data base would be holding a round 20,00,000 records and each record have around 12 fileds (that is the max number of fields a table has) and there would be 6 tables altogether (other tables have lesser no of fields.

Again I repeat my Problem -

How I am suppose to connect my "*.mdf" or Backup File to the Web Server's Data Base (i.e MS SQL Server).

scolvard SSC Rookie Points: 39 More actions · Answer 6

Depending on your host provider, they may offer the service of providing SQL Server as database choice (which it sounds like they do based on your previous comments). If they will not allow you to connect from your local enterprise manager (assuming that you are connecting via SQL 2000 as the IDE is now different in 2005), then that is pretty ludicrous. I have a service provider (half price hosting) that allows me to connect via my local enterprise manager to the database provided to me and perform DTS transfers in order to poplate the data from my local database to the one that they created for me at their site (which is what I recommend you do instead of going through the process of attaching and detaching databases). Security should only be a concern if the provider doesn't set it up properly to begin with. Using SQL authentication versus Windows (although windows would be nice but since it is an app account......) and making sure that the account is properly setup for allowing only what is necessary, such as table creation, data population, etc.) should be the main focus. If your provider will not allow these things then you may want to consider switching host providers. One thing to keep in mind though, depending on the provider, some of them charge a fee based on storage space which can get pretty costly if you are going to be storing massive amounts of data. Some offer set plan fees for packages based on your data storage needs. Bottom line, do your research before purchasing.