January 26, 2009 at 12:46 pm
Hi everyone.
I'm just starting to look into moving our main Access DB to SQL server and I have a couple questions that hopefully can get answered here.
1. Upsizing Wizard from Access, SQL's SSMA, etc... What's the best way to move the data over while still documenting the script that creates everything or whatever? I'm assuming that's very important info for DR reasons and what not.
2. After it does get moved and put into production and say that server completely dies, what does it take to get that DB restored to a different machine or something? (Assume that clustering or mirroring is not viable, so I think that leaves only log shipping left).
Essentially, I'm just trying to make sure I do everything right the first time. If there is anything else I should be aware of that I'm missing, please post your input.
January 26, 2009 at 4:37 pm
In addition to other replies you will recieve let me say backups, backups, and more backups both the database and log files. Depending upon your amount of data change activity (addition, updating, deletion) set up a backup schedule. And then perhaps more importantly if something does go wrong you will be under considerable pressure to get that db back on line, so create in writing your restore procedure, step by step no matter how trivial you think the step may be. And then equally important practice restoring the database - and the first practice restore should be before you place the db into production. Periodically additional practices should take place after it is in production. Each time review your written procedure for refinement.
January 27, 2009 at 6:33 am
I'll agree on making sure you have a backup strategy that works for your situation. Read the entire books online section on backup and restore to make sure you understand. Restoring a backup to another server is a relatively simple process, but you need to have a viable backup to restore.
The upsizing wizard from Access does a pretty good job. You should look over everything it generates and make sure it still works well in MSSQL. As far as keeping the scripts it uses - they are pretty much worthless. Keep a pre-upgrade copy of the Access database and immediately make and keep a backup of the MSSQL database before any data changes are made.
Don't underestimate the complexity of MSSQL. It has some great tools and Management Studio looks like a nice-easy GUI. Because of this many people jump right in and start clicking away. Don't be that guy. If you cannot convince your company to spend $2k and send you to a SQL administration class, convince them not to do the migration. If neither of these work, get a SQL administration book and read it cover to cover before you even begin. Treat this upgrade as if you were going to migrate to Oracle on a UNIX box (I assume you know nothing about Oracle and UNIX administration).
If you have not done any SQL administration before and have had no training, this should be a little scary - if it is not, you are underestimating the administration. At some point, you are probably going to need to know how to use PerfMon to look for memory bottlenecks, SQL Profiler to find deadlocks, read query execution plans to optimize them, etc. If you spend a couple of weeks learning before you start, you will stand a better chance of avoiding problems before they happen.
January 27, 2009 at 7:42 am
Good information.
I have been to SQL admin and development classes, so I have a real basic understanding. I have used SQL management studio before and am familiar with it enough to get around.
I have a MCTS Implementation and maintenance book for SQL 2005, but it's pretty vague in some parts, so I will check out the BOL some more.
Our database is a small one for the most part (less than 1 Gb) and has maybe 100 - 200 changes a day at best. If possible, what are thoughts on just creating a new DB in SQL and then importing the data over?
I would be able to script all of the db, then back it up, import the data over, back up again, do my testing on our dev server and see how it goes. I could then try restoring to make sure all works ok. Does that sound like a viable option?
Thanks!
January 27, 2009 at 7:47 am
That would work. Upsizing from Access eliminates the somewhat unnecessary steps, but if you want to start with an empty database - go for it.
If you have foreign key constraints, importing data becomes a bit more tricky because you are going to have to import all of it in the correct order. You will also have to turn identity_insert on and off appropriately if you intend to bring autonumber fields over as they are. This is where is wizard can make life a lot easier.
January 27, 2009 at 7:59 am
I'm currently involved in a project converting several Access bases to SQL 2005, and we've found that the SSMA for Access that you can download from Microsoft has proved invaluable. It converts Access Queries to SQL Views correctly and it allows you to store the whole thing in a project so it is easily adjustable and repeatable.
Also some of our users wanted to keep the Access Forms applications they'd written, and the SSMA adjusts the Access database to use linked tables automatically.
I've got to agree though that the key to a good conversion is plenty of backups. Much better to be safe than sorry.
January 27, 2009 at 8:03 am
I've done this before, and I do recommend the upsizing wizard. It worked out quite well for me.
- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread
"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon
January 27, 2009 at 8:08 am
has maybe 100 - 200 changes a day at best.
When suggesting backups, backups based on the amount of data added, updated or deleted I should have said, if you have a major disaster can you recover all the source documentation for those changes so when they have to be repeated it will be accurate and will be all of them.
January 28, 2009 at 5:57 am
We are wrapping up a project converting an Access 2003 db to SQL Server 2005, and the SSMA does a great job with a few minor exceptions.
The key to a successful conversion is to run side-by-side testing of the pure-Access version against the Access/SQL version. To be conservative, we only migrated the tables to SQL and kept all the queries in Access. A few queries needed to be replaced with pass-thru queries running T-SQL or stored procedures because some of the Access queries performed poorly.
We have three environments set up - development, QA, and production. The db was converted into dev, and then backed up & restored into QA and production for the initial migration. After dev tweaked & verified that the side-by-side worked, the QA person repeated that testing before releasing to production.
The DBA's set up the standard maintenance plans on the SQL server for all three db's.
The exceptions noted - the SSMA uses the SQL Server provider for ODBC, which is the 2000 version, to link the tables. We had to relink the tables using the SQL Native Client for 2005 for better performance.
Also, we had a bitmap logo stored as a OLE object in Access, SSMA converted that as a varbinary(max) which worked fine with Windows authentication, but for some reason it would not display when using SQL Server authentication. We changed that to a image datatype & it works fine.
After the conversion, you can use SSMS to generate a create database script to document the structure of the SQL database.
January 28, 2009 at 8:33 am
What did you use to analyize the the pure access and the sql/access versions?
Your scenario sounds like what will play out here. There will still be an access front end until a .NET solution can be provided.
Does the SSMA get installed on the SQL box or where?
Thanks!
January 28, 2009 at 9:07 am
You can install the SSMA wherever you need it. We installed it on a seperate PC as there is a policy to keep application software off the SQL Servers (and this was officially classed as a application:rolleyes: )
Some of the Access queries were parameter driven and so these were kept in Access, others were converted to views in SQL Server where appropriate and the tabled linked through and (like William) we changed the VBA forms to use pass-through queries and re-wrote them in T-SQL.
The analysis was done using just experience, no tools.
January 28, 2009 at 9:22 am
That's cool. Since my last post I did install SSMA on my machine and did a test to our dev box. Seemed to work ok. I will go through it and see what's been added. I see some timestamp columns got added and I don't know what else.
Let's me know what I need to change in access perhaps before I consider doing a migration for real.
Other than the connection type it uses to link tables back to access, has anyone else experienced somethings that the SSMA changed or did to their database that I may not be aware of?
January 28, 2009 at 5:10 pm
Hyperlinks get converted to text, so you have to go into the Access app & change the controls back to hyperlinks.
Memo fields are converted to nvarchar(max) and all the data moves correctly into SQL, but Access thinks they are text(255) and retrieves only the last 255 characters of the field. You have to go into SQL and change the datatypes to nvarchar(4000) and then it works in Access.
If you use DAO and Recordsets, instead of
Set rs = db.OpenRecordset("MyTable")
you have to use
Set rs = db.OpenRecordset("MyTable,dbOpenDynaset,dbSeeChanges)
when you have an identity column in a table (same as autonumber).
SSMA migrates relationships that use referential integrity. It ignores & warns you if you have any that don't enforce RI.
Any form that allows new records needs the primary key included in the underlying table / query. If it's not in there you can't add records.
January 29, 2009 at 10:27 am
Can someone shed some light on what these are:
I see the linked tables in the access program, which is what I expected.
I also see these tables for it looks like each of my linked ones that say SSMA$tblStaff$local
They all start with SSMA$tablename$local
What is the purpose of these?
January 29, 2009 at 10:47 am
Are these tables the local copies of the upgraded tables? I believe after you use the upsizing wizard you will have two copies - your original (which may be the copies with the $ names) and then the linked copies that are on SQL Server.
There's a lot of things that Access will let you do, that SQL Server won't let you do, and vice versa. For example, the Access Upsizing Wizard will assign primary keys for you if you don't have any chosen in Access. Also, I've seen lots of Access databases that have column names like with spaces. These aren't as easy to work with in SQL 2005 as you aren't creating queries through the drag-and-drop interface of Access.
Viewing 15 posts - 1 through 15 (of 17 total)
You must be logged in to reply to this topic. Login to reply