November 29, 2007 at 3:30 am
Hello,
In the early part of next year, I will be responsible for migrating my companies CRM Access database which has an in-house client front end to SQL server 2005. Now I am expecting this to be quite tough as the Access DB has grown over the years along with the business so its safe to say that it may be somewhat disorganized. I fully expect to have to develop quite a number of different database designs before I find one that is optimal and performs well. I wonder how much tweaking of the application will be needed for it to work well against the SQL database, but I am getting ahead of myself here and I would have to spend time with the app developer.
I really want to know if anybody here has ever done anything similar, where did you start and what methodology did you use? This is going to be a first for me and I could do with as much advice as I think I could possibly handle. Yes, I could just import the lot and it would work, but I want to make a big difference, especially as this is a massive learning opportunity. The database is not heavily written to. I dont want to be a good DBA, I want to be great DBA.
I know its early days, but I have not developed a database structure before so I am going to start gathering information and advice now. For shock value I should tell you that I only recently passed the implementation exam, have worked with SQL server for about a year, and I am currently improving my T-SQL knowledge and simultaniously studying for the 70-443 design exam which I expect I will take in January or early Feb, just in time to start the project. Am I being unrealistic, mad or brave?
Regards,
D.
November 29, 2007 at 3:46 am
D.
I think you are being brave, but the most important part of this is planning. The second is to attempt to get the datbase across as is.
Do not try to make improvements as you go along. Save those until you get the first group of users on and can take some measurements using Profiler or whatever.
The first decision is to get the tables acroos and to decide between things like char or nchar, varchar or nvarchar.
Get the queries across using those that are just used for reporting as views and those that alter the database as stored procedures.
Get your Security and Housekeeping correct. Decide on a maintenance plan and impletment it. Ensure only DBAs have sysadmin access. If you are the only one set up a second in case the first is compromised.
Set up your keys in a minimal status to start. Ensure all Primary Keys are Clustered and secondary or foriegn keys are set up where most needed. Be conservative to begin with.
Establish connection strings in a VB VBA or VB.NET module, but try to keep the code as is.
Move users across dept by department using SSIS to ensure the tables are synchronised.
When all departments are across then you can use the tools provided by SQL Server to measure the reposnses accurately that are being obtained by your users and then begin to implement improvements.
David
November 29, 2007 at 4:54 am
Thanks for your reply, there were a couple of things I was going to do but, after reading your post, I have already realised some things I had in mind would not have made sense. Ive actually been in IT for 8 years but as a Windows/Exchange/Active directory type with a few MCSE's behind me, but I have found that I enjoy working with SQL more than anything, so planning will be the last thing I will scrimp on, I'll never know how many holes Ive avoided having planned properly in the past.
Thanks again, I am sure I will be here quite a bit from now on, your post is a good template to work with. If anyone else fancies chiming in, feel free.
Kind regards,
D
November 29, 2007 at 5:24 am
To start with use the Upgrade Wizard within Access to move the tables across to SQL server. From recent moves I know it's quite simple and reliable.
Depending on your Access database size start your SQL database with the same size and keep everything in the Primary filegroup to start with.
The pleasure of this approach is that your Access database will have links into the SQL server but all else stays the same.
This allows you a quick test that the application can see the SQL data via Access. Proves that the application still works and provides a fallback to keep the application working in case the next steps go horribly wrong.
Next is to get your security right. Determine roles, then set up the roles and stick to them. Put users against roles and roles against tables. Even if it is tempting to make an exception for "just the one user" - resist it !
There's a lot more to security as you might already found out but with hindsight this is the step I really really would like to be able to get back to in time.
Another option is to check out filegroups. In short filegroups are files of the database above the Primary filegroup. If your database server has several physical hard drives then splitting heavily used tables, indices and other tables on different hard drives. Moving the temp table filegroup away from the SQL application hard drive can give you a performance boost - but you won't notice it really if you don't have many transactions.
Oh and change the sa password straight away after setting up SQL server. 😛
The rest you should look out for is nicely covered by the first answer. Just couldn't resist putting some of the things in that I always wanted to do when I set up a SQL server first time - so far I only took them over and a few were above just plain installation.
November 30, 2007 at 7:39 am
Hi,
Great responses, just another note: I am assuming that your front end is staying in Access, with the data moving to SQL. That said, once you complete the move over, and, given the possible large amount in data (though you will see a real improvement in response time for query in SQL vs Access for sure), consider pinpointing any complex queries and creating views for them, linking the view to the front end. If there are parameters, then set up the view and use a pass-through query from the Access side. I do this for reporting and it shows a marked difference in response.
You may find yourself having to learn a little Access VBA too. Just a heads up.
Laura
December 3, 2007 at 4:43 am
Hello,
Firstly, thank you all for your advice and suggestions, I now have a clear idea of what I need to look into which is great and I now have something to aim for. Laura, I will indeed be keeping the access front end (at least initially), your suggestion of a pass-through query is interesting, I'll definately look into that.
Thanks again everyone,
Regards,
D.
December 4, 2007 at 2:43 am
Here's some handy code to help you execute a passthrough query:
Public Sub PassThrough(strSQL As String)
On Error GoTo ErrHandler
Dim dbsCurrent As Database
Dim qdfPassThrough As QueryDef
Set dbsCurrent = CurrentDb()
'delete Q_EXEC if it exists already
For Each obj In dbsCurrent.QueryDefs
If obj.Name = "Q_EXEC" Then dbsCurrent.QueryDefs.Delete "Q_EXEC"
Next
' Create a pass-through query to execute a procedure
' in a Microsoft SQL Server database.
Set qdfPassThrough = _
dbsCurrent.CreateQueryDef("Q_EXEC")
qdfPassThrough.Connect = _
"ODBC;DSN=MyDatabase;DATABASE=MyDatabase;Trusted_Connection=Yes"
qdfPassThrough.SQL = strSQL
qdfPassThrough.ReturnsRecords = False
With DoCmd
.SetWarnings False
.OpenQuery "Q_EXEC"
.SetWarnings True
End With
dbsCurrent.QueryDefs.Delete "Q_EXEC"
dbsCurrent.Close
ExitErrHandler:
dbsCurrent.QueryDefs.Delete "Q_EXEC"
dbsCurrent.Close
Exit Sub
ErrHandler:
MsgBox Err.Number & ": " & Err.Description
Resume ExitErrHandler
End Sub
You just need to create an ODBC connection (if you haven't made one already) and pass it a string like "EXEC pr_MyProc 123, 'abc', '04/30/2007'"
Make sure you grant execute rights for your procedure to the appropriate role.
Hope this helps,
Carol 🙂
December 4, 2007 at 8:20 am
This is not an easy task. It took me about a year to get one of these done converting from Access 2000 to SQL 2000. Most of the time to rewrite the Access querys into SQL stored procedures for form and report loading.
The Access database had about 70 tables, 150 querys, 100 forms and about 200 reports. Below is a list of steps I set up. (As much as I can remember)
1. Access Upgrade wizard to to SQL (I kept using this until I had my tables converted the way I wanted them. Then I abandoned this and used SQL DTS import process packages to import the current data as needed. MUCH faster than the Upgrade wizard)
2. Set up SQL backup process.
3. Create a seperate staging mdb file. This had linked tables from the live Access db as well as copies of those tables with new field definitions for SQL. This alowed conversion from the original Access data into what I wanted to import into SQL Server. (This is important since many changes to design were occurring during the year long conversion process. I used this staging database to allow me to modify and test without effecting the original and live Access database.)
4. Create SQL Server import routines which truncate/delete SQL table data and upload the Access data from the secondary stagibng db.
At this point I could update the SQL tables with current Access data at any time, using dts packages. The speed was phenominal compared with the slow Upgrade wizard.
5. Convert the Access front end MDB file to an Access front end ADP file and link it to SQL server back end data. I think the upsize wizard can accomplish this.
6. Rewrite all Access update/delete style querys into SQL
7. Rewrite all Access form loading querys into SQL.
8. Rewrite all Access report loading querys into SQL.
For items 7 & 8 I had to get familiar with how Access forms and reports passed parameters to SQL server. This is done through the Input parameters property of each form and report. Form filtering was also tricky. I created stored procedures to set up the initial load and combox filetering, then used a me.requery statement in Access vba to requery these Sps and populate the forms. This was tricky to get the Sps just right.
@Lastname is null or @Lastname = tblname.LastName
OR
@Firstname is null or @Firstname = tblname.FirstName
9. test, test, test.
10. Here is a big gotcha. Access adp files are single use only. Each user had to run a seperate copy, instead of the Access model in which all users could start up one front end.
I was happy with the final result and the speed performance was sweet!
Good luck!
December 4, 2007 at 5:47 pm
The migration assistant is a handy tool:
http://www.microsoft.com/sql/solutions/migration/access/default.mspx
December 5, 2007 at 8:56 am
Hello,
This must be the most helpful forum ever!
I have another question regarding the SSMA, once you have migrated your access databases over to SQL 2005, is it then possible to do ad hoc querys on the database as you would a standard SQL database through SSMS if you wanted to?
Regards,
D.
December 5, 2007 at 11:55 am
Hello,
I've done this once, moving from an Access 2003 frontend/backend to an Access 2003 frontend/SQL backend. And I just started testing the vba-code in the frontend on sql server. Rewrote my code bit by bit. And worked for a couple of months with one frontend, compatible with both Access and sql server (in a live situation). For the incompatible parts I created a few special functions, like for example:
Public Function TrueFalse_strSQL(ByVal strBoolean As String) As String
Dim dbType As String
dbType = Right(CurrentProject.Name, 3)
Select Case dbType
Case "adp", "ade"
Select Case strBoolean
Case "True"
TrueFalse_strSQL = "1"
Case "False"
TrueFalse_strSQL = "0"
End Select
Case "mdb", "mde"
TrueFalse_strSQL = strBoolean
End Select
End Function
The incompatible parts mostly concerned sql-strings with dates, time, wildcards and the above true/false example.
And when I was convinced that I had created a fully compatible frontend (with both Access and sql server) I made the final switch, deploying the new frontend to all users, without my users noticing.
December 6, 2007 at 2:25 pm
Having been there (and got the T-shirts) a couple of times, I agree with all the above posts ...
You will certainly notice a massive speed improvement, especially if you have a combination of many users and large table ... Access seems to plateau out at around 20 concurrent users unless you've running it on really beefy hardware (which most Access applications are not) ...
Idea I found useful for .adp front ends ... Keep a master copy of the .adp file on a fileserver from which users can download a copy ... That way, you can do any mods to the master .adp and notify users to download the new version ... (I enforced a policy of cleaning out old versions and downloading new versions, or at least new copies, at fixed times - daily, weekly, whatever suits your development cycle) ...
Yep ... Good Luck ... !!!
Graham
December 7, 2007 at 5:06 am
I agree. A master adp is the way to go. I created a version table visible through a Help...About menu and placed some version checking in the software. Whenever I add a new version record to this table the app compares it to a hidden version ID on the main startup form. If it is earlier, then I drop the app with a message to download the newest version from f:\...
This keeps older versions from running against possible new table /SP and code changes.
December 10, 2007 at 12:43 pm
Very usefull to have the master app
easiest way to ensure that everyone has a current version is to deploy it via the user logon script to copy to a local drive or if using terminal services / citrix copy to the users home drive.
Make sure that you incorporate a piece of code when the application starts up to ensure that the Front end version is compatable with the back end.
Have Fun
Paul
Viewing 14 posts - 1 through 13 (of 13 total)
You must be logged in to reply to this topic. Login to reply