Go Premium for a chance to win a PS4. Enter to Win

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 313
  • Last Modified:

Migrating / Exporting Database from MS Access 2003 to SQL 2005 Express Edition


I am moving MS Access 2003 .mdb database to SQL 2005 Express Edition ?

How to migrate MS Access DB ( Entire Database ) to SQL 2005 Express Edition ?

Also, i want to make sure that i can write Stored Procedures in SQL 2005 Express Edition ..

I am migrating the database ... as because i am facing poor performance in MS Access ..!
0
chokka
Asked:
chokka
  • 10
  • 9
  • 5
  • +1
1 Solution
 
chokkaAuthor Commented:
What you suggest .. Going with SQL 2005 Express will be better or 2008 will be better ?

Yes, i am migrating only Database and not Forms or Reports
0
 
DatabaseMX (Joe Anderson - Microsoft MVP, Access and Data Platform)Commented:
Well, not really an SQL guy; however, I'm pretty certain that 2008 would be preferred over 2005 - now 5 years old.

mx
0
What is SQL Server and how does it work?

The purpose of this paper is to provide you background on SQL Server. It’s your self-study guide for learning fundamentals. It includes both the history of SQL and its technical basics. Concepts and definitions will form the solid foundation of your future DBA expertise.

 
chokkaAuthor Commented:
Also i can write Stored Procedures .. and also SQL Express edition is a light weight edition and be easily deployed on any computers .. Am i right ?
0
 
DatabaseMX (Joe Anderson - Microsoft MVP, Access and Data Platform)Commented:
"and be easily deployed on any computers "
Not 100% sure on Express # of users.  It is limited to 4GB I believe.  I'm sure you can write Stored Procedures.

mx
0
 
Kevin CrossChief Technology OfficerCommented:
Yes, Stored Procedures are available in Express.  I would recommend SQL Server 2008.  With SQL 2008 R2 Express you get up to 10gb, there is no specific user-limit; however, Express is limited to 1 cpu and 1gb ram, so they figure you will outgrow it eventually. *smile*
0
 
DatabaseMX (Joe Anderson - Microsoft MVP, Access and Data Platform)Commented:
1 GB of ram?  Are you sure?  Doesn't make sense.  Most all new computers come with 4GB ...
0
 
Kevin CrossChief Technology OfficerCommented:
Yes, unfortunately. I was reminded of this the other day.  SQL Express will not take full advantage of the computer having 4GB.  Considering this is typically a solution for desktop usage, the extra memory gets used by the users other applications anyway.  Quite honestly, I run SQL Express and do a fair bit on it and don't notice the limitation to any point of frustration.  Think its limitation is solely to stop a high-use application to grow over time and the owner never having a reason to license higher editions of SQL.
0
 
Kevin CrossChief Technology OfficerCommented:
Now if you developing an application using embedded or compact edition, then the limit is whatever the OS can handle but database max is reduced to 4GB.

http://www.microsoft.com/sqlserver/2008/en/us/editions.aspx
0
 
DatabaseMX (Joe Anderson - Microsoft MVP, Access and Data Platform)Commented:
ok.  Interesting limitation, but I *suppose* ... it makes sense ...

mx
0
 
chokkaAuthor Commented:


Whether this approach will work?

1) Using SSIS, i am planning to export MS Access DB to SQL 2005 Standard Edition Database.

2) Once SQL DB is created, i will detach it and attach with SQL 2005 Express Edition

I am doing this double work, since i am afraid SSIS is not supported in Express Edition
0
 
chokkaAuthor Commented:


1) I have exported MS Access DB to SQL 2005.

2) Then i detached SQL 2005 DB

3) I copied DB to my local working folder ( .mdf and .ldf )

4) I am able to attach .mdf to SQL 2005 Express Edition

Now, i am not able to create a Table or Drop a Table or .. any activity ..
It throws an exception saying that Database is readonly
Issue.JPG
0
 
Kevin CrossChief Technology OfficerCommented:
Ensure the proper permissions are set as well as check the properties of the SQL database weren't set to READONLY in the transition.  It is likely the former issue, though, as the logins would not have transfered so you probably just need to map your login back to appropriate role on the database you attached.
0
 
chokkaAuthor Commented:
Do i need missing some steps while detaching from SQL 2005 and attaching to SQL 2005 Express Edition?
0
 
Kevin CrossChief Technology OfficerCommented:
Not really.  You are switching servers, so same issues/rules apply.  You can move the database, but you are not inheriting the *server* configuration.  Logins are a server level concern.  You just need to map your login to database user or role which should have copied over okay from detach/attach process.
0
 
chokkaAuthor Commented:
Okay,

Now, i have SQL Express DB which is working well and able to interact with .Net App

On taking build version or deploying this application along with SQL Express on client machine(s), do i need to pass the same connection string or connection string varies from Client Machine - Machine ..?
0
 
Kevin CrossChief Technology OfficerCommented:
As long as the clients have all installed the SQL Server client drivers you should be fine.  They just have to install everything in the same fashion -- If you are embedding the DB in an application you are deploying, you may also want to look at Compact Edition; however, you should be fine with SQL Express.  If it is real light weight, .NET has a library for SQLite also, but that might be a dirty word in a SQL 2005 thread so I will stop there before my SQL buddies here oust me from the club. *smile*

But seriously as IT professionals, it is good to love technologies like I love SQL Server; however, it is always prudent to know the alternatives and start your design vendor agnostic and then go hunting for the right pieces to the puzzle which it is fine to have a vendor of choice with which you do so.

Regards,
Kevin
0
 
Kevin CrossChief Technology OfficerCommented:
If you have difficulties with the .NET side, I would recommend asking a follow question on that side of the house with more code details ...
0
 
chokkaAuthor Commented:
Kevin .. i just have one last question.

My .Net Application has a local Database.
Initially, i was using MS Access and then i moved to SQL Express.

Now question is when i install .Net Application on client machine, do i need to install SQL Express Installer also on every machine ..
Or just attaching a local version of DB Is fine .. ?
0
 
Kevin CrossChief Technology OfficerCommented:
If I am not mistaken, you will need to install SQL Express.  The last time I did an application like that, it appeared as if you could just use the MDF/SDF on the clients, but I always found they needed to install Express (desktop edition when I last did that) or compact edition.  Therefore, unless this is drastically changed, the clients will have to one of the free versions of SQL installed.

SQLite is just a file, so in that case you would not need anything.

Again, you can verify this with the .NET geniuses.
0
 
Anthony PerkinsCommented:
>>Now question is when i install .Net Application on client machine, do i need to install SQL Express Installer also on every machine ..<<
It depends.  You will need to install it whereever you have the databases.  The rest of the workstations just need the client components and it would be over-kill and a waste of CPU/Memory/disk space to install SQL Server Express on all.
0
 
Kevin CrossChief Technology OfficerCommented:
That is assuming a server / client application, AC.  Sounded like the application uses a local MDF and question is does that require SQL Express to attach in application and believe the answer to be Yes.  Should it be done is a different matter which you just addressed, but can you confirm attaching MDF via .NET no longer needing SQL locally installed ?
0
 
Anthony PerkinsCommented:
>>Sounded like the application uses a local MDF and question is does that require SQL Express to attach in application and believe the answer to be Yes.<<
Right, if each one requires their own database then they will need to install SQL Server Express on all workstations.

>>Should it be done is a different matter which you just addressed, but can you confirm attaching MDF via .NET no longer needing SQL locally installed ?<<
You can attach a database from anywhere, so long as you belong to the local Administrators group.
0
 
chokkaAuthor Commented:
Yes acperkins, you are right ... This should be a locally installed database.

Later we sync with all these local database to the corporate database.
0
 
Kevin CrossChief Technology OfficerCommented:
>> Later we sync with all these local database to the corporate database.
Then it probably makes sense to install SQL Express or Compact Edition on each client as you will probably want to utilize the SQL Server Subscription Services having these endpoints all read from the Corporate SQL Server.
0
 
chokkaAuthor Commented:


1)  Can i assume, each and every computer will have different connection string.

i.e I am working on Computer1 with connection string as follows ..

Server=.\SQLExpress;AttachDbFilename=c:\mydbfile.mdf;Database=dbname; Trusted_Connection=Yes;

Whether this will change from Client1Computer to Client2Computer etc ..!


2) Database which i created from VS 2005 Environment is not reflecting or not able to drag from SQL 2005 Management Studio. What should be the possible reason?
0

Featured Post

What does it mean to be "Always On"?

Is your cloud always on? With an Always On cloud you won't have to worry about downtime for maintenance or software application code updates, ensuring that your bottom line isn't affected.

  • 10
  • 9
  • 5
  • +1
Tackle projects and never again get stuck behind a technical roadblock.
Join Now