April 24, 2008 at 3:11 pm
I am about to migrate objects from an Access 2003 to a SQL 2005 DB. They are tables, queries, forms and reports. I will use the Upsizing Wizard.
I realize that there is a new version of SQL 2005 that exists:
(2005.090.3054 Service Pack 2)
Do you recommend I upgrade to the lastest SQL version before doing the migration? Or is it possible to do it under the following conditions:
FROM:
Microsoft Access 2003 Version 11 Build 5614
ADO Version : 2.18
VBA version 6.04
Jet Version: 4.0
On a Windows 2003 SE SP 2
TO:
Microsoft SQL 2005 Product 8.0.760 SP3
Microsoft SQL Server Management Studio9.00.3042.00
Microsoft Analysis Services Client Tools2005.090.3042.00
Microsoft Data Access Components (MDAC)2000.086.3959.00 (srv03_sp2_rtm.070216-1710)
Microsoft MSXML2.6 3.0 5.0 6.0
Microsoft Internet Explorer 7.0.5730.11
Microsoft .NET Framework 2.0.50727.1433
Operating System 5.2.3790
On a Windows 2003 SE SP 2
April 24, 2008 at 4:21 pm
->>If you are running other applications on the same server, I will take a little caution...you never know what a SP has...
But if is the only application running on that server, is a good idea to apply the service pack before the work.
April 24, 2008 at 5:14 pm
I see you listed your 'current' sql as '2005 ... 8.xxx SP3'. This is incoherent as sql2000 was version 8.xx and it has a SP3 while sql2005 is version 9.xx and has only been SP'd twice. Recent announcements indicated SQL 9, SP3 will be out AFTER sql2008 ships.
So, perhaps your question is whether you should migrate to 2000 or 2005.
I am unaware (i.e., low confidence in my opinion is appropriate) of a limitation in SSMA for migrating to version 2000. However, once there, you will still need to move from 2000 to 2005 since MS will soon discontinue mainstream support for 2000.
I'd suggest using 2005 as your target. Sql2005 Express should work since the only pertinent limitations are issues such as scheduled jobs and CPU utilization, none of which should be problems for an ACCESS source. It is very easy to migrate from express to workgroup or standard (just backup from one and restore to the other.)
-----------
Oh, and BTW, notice my use of 'SSMA'. This is the Sql Server Migratin Assistant [for Access]. This is much preferred over the upsizing wizard embodied in Access which is much less reliable than SSMA and generally should be avoided.
I was involved in a test of UW by the Office team a few years ago to see what could be done to fix it. Instead, the SQL team built SSMA (I believe the folks conducting the test moved with the project). Additional SSMAs are out or forthcoming (Oracle, DB2?, Informix?).
April 25, 2008 at 8:41 am
Scott,
Thanks for your answer.
When I goto "Add or Remove Programs" on the server, I see:
"Microsoft SQL Server 2005".
3 Clicks below is an entry for
"Microsoft SQL Server Desktop Engine"
There is a sub link which say beneath it:
"Click here for support information"
When I click this link, the display reads:
"Version 8.00.761"
When double-click the "SQL Server Management Studio" on the desktop, the brief dialog box appears "SQL 2005". Then it prompts me to connect to the database engine.
When I connect, I right-click on the instance which holds my database s. I goto "Properties". Another box opens up. It holds an option to click "View Connection Properities" When I click on this link, there, under "Product" , there is the following:
Product Name: Microsoft SQL Server Desktop Engine
Product Version: 8.0.760 SP3
So, now, I'm confused even more! :w00t:
You stated I'm using SQL 2000 given the specs in my previous post. Everything visually I see says "SQL 2005", and in the "Add/Remove Programs". But the "Version" is 8.x.x.
Am I to understand that SQL 2000 is Product Version 8.x.x.x and SQL 2005 is Product version 9.x.x.x? If so, I don't understand the "Add/Remove Programs" entry and the Dialog boxes which appear when I click on the icons.
This is a wonderful forum, and I appreciate the feedback thus far. Its all the pecularities with these applications which I enjoy reading about and interested in their characaterizations. But at this point, I'm frankly dismayed by the inconsistency I'm seeing in front of me. :hehe:
April 25, 2008 at 12:45 pm
OK.
I think I can reconcile this for you.
The key is the "Microsoft ... Desktop Engine". This is usually abbreviated to 'MSDE' and is the predecessor to Sql Express and is the junior counterpart to SQL Server 2000. If I were more knowledgeable, I would have spotted that with the version number 8.xxx that you provided.
It is possible to manage MSDE instances using the Sql Server Management Studio of Sql Server 2005. There are actually two versions of SSMS - one for SQL Express and one for all the others. The SQL Express version can manage MSDE instances as well as SQL Express instances but NOT higher levels of sql2000 or sql2005. The other version of SSMS (ships with all other versions of SQL2005 can manage ALL levels of ANY vintage of sql server.
There never was an 'SSMS for SQL Express' counterpart to for MSDE. You had to use the 'Enterprise Explorer' for a graphical interface and that was only available through the higher level products of SQL2000. Or, you could/can use an Access project (.adp) connection which provides access to certain object types in the MSDE engine. A note on this: a friend of mine has used Linked tables to sql2000 for his application and a parallel adp Project to manage the SQL2000 objects.
The Add/Remove Programs list does not list the version level of the CLIENT, only the ENGINE. You are probably using the SQL Express level client. When you are prompted to select an instance to connect, don't select the one you have been but the sql express instance (that is probably in the list somewhere since you have the client installed).
April 28, 2008 at 7:26 am
Scott,
I'm please with your feedback. However, please clarify the following 2 paragraphs:
Scott MacCready (4/25/2008)
The key is the "Microsoft ... Desktop Engine". This is usually abbreviated to 'MSDE' and is the predecessor to Sql Express and is the junior counterpart to SQL Server 2000. If I were more knowledgeable, I would have spotted that with the version number 8.xxx that you provided....
The Add/Remove Programs list does not list the version level of the CLIENT, only the ENGINE. You are probably using the SQL Express level client. When you are prompted to select an instance to connect, don't select the one you have been but the sql express instance (that is probably in the list somewhere since you have the client installed).
It is possible that the version level of the client that shows in Add/Remote Programs (SQL 2005) be different than the 'MSDE' (the "junior counterpart of SQL Server 2000?" - I'm sorry, I'm not familiar with this nomenclature) So the SQL DB "Server" is running SQL Server 2000? and the client is running Sql Server 2005?
I cannot understand your clarification unfortunately, and now I am trying to come to grips with what are the 1:1 relationships between Version and Product name. Is the following correct?:
SQL Server 2000 = Product Version 8.x.x.x
SQL Server 2005 = Product Version 9.x.x.x
I'll ask my question on SSMA in a seprate post. if we can just focus on this for now, that would be great.
April 29, 2008 at 1:12 am
Yes. You have it: version 8.x implies one of the versions of the SQL Server 2000 engine. The actual pattern following the '8.' indicates which version and which service pack. If I recall correctly, there were three primary products in that generation: MSDE at the low end, with Standard and Enterprise at the high end. In addition, there were others that had niche foci: personal edition; developer; evaluation; and compact. All of these were version 8.something.
In the 2005 vintage, the main products were expanded to 4 with the edition of 'workgroup' - between sql express (the replacement for MSDE and the personal edition) and Standard. As price increases, the feature set and the level of hardware utilization (ie speed and capacity) increases.
The developer, evaluation and compact editions were also carried forward.
Yes, you are using one of the vintage 2005 clients to manage your vintage 2000 engine. They are backwards compatable. From what you have said, I can't say whether your client is part of SQL Express (more limited) or one of the other products. You can tell by looking to see if it displays features such as the SQL Agent and Jobs. If those features are present in the client, you have regular Sql Server Management Studio. If you do not, you are running the SQL Express version of SSMS. If you are using the SQL Express client, you can only manage sql Express 2005 and MSDE with it. All other engines are invisible to it.
It is sort of like using a more modern interface to manage an older product.
Hope this clarifies.
This IS complicated. But, you'll get your arms around it.
April 29, 2008 at 9:08 am
Scott,
Once again, thanks for your feedback.
SQL 2005 is the application front-end while the MSDE is the Database Engine --- a "complier", so to speak, --- with SQL 2005 as the interface.
MSDE converted to SQL Server Express, which combines both engine and interface. (I'm getting this from: http://en.wikipedia.org/wiki/SQL_Server_Express).
So when I see SQL 2007 and MSDE in the Add/Remove Programs (along with the Product Information of that SQL 2007 I provided before), I'm using the the SQL 2007 interface to work with the objects with the MSDE engine doing the work.
I didn't install and config. the SQL App. Is this the way it appears upon a SQL 2007 install?
When I run NET START on the command line, the following entries appear running:
MSSQLSERVER
SQL SERVER ANALYSIS
SQL SERVER BROWSER
SQL SERVER INTEGRATION SERVICES
SQL SERVER REPORTING SERVICES
All this leads me to believe the SQL 2007 / MSDE set up I'm on.
April 29, 2008 at 10:27 am
Actually, you mean SQL Server 2005, not 2007, just for clarity.
The Net Start results imply you are running something more advanced than sql express. These:
SQL SERVER ANALYSIS
SQL SERVER BROWSER
SQL SERVER INTEGRATION SERVICES
are not included in Express.
If this was purchased from TechSoup, it is SQl Server 2005 - Standard Edition since that is all they offer. Just guessing.
It behooves you to NOT use MSDE if you can put everything to SQL Standard. MSDE is going to be deprecated from active support by MS very soon and it is quite crippled comparatively. There can be some gotcha's moving from MSDE to Express (they don't affect most situations) that don't occur migrating to the SQL Standard or Workgoup Editions.
I wonder why you have MSDE running. It just occured to me that it may have been installed as part of the Business Contacts extension of Outlook 2003. If so, and you are not using those features, you might want to uninstall it to simplify your SQL environment. Otherwise, you should figure out why it is installed and decide whether to keep it or convert it's databases to sql server 9.x...
SQL SERVER BROWSER is used by the client to search for running SQL Engines on your network. If you did not set up the SQL '05 product, you might contact the person who did to get more information about how you might gain access to the 9.xxx engine (assuming it is running somewhere on your network).
You will need a login (best to use Active Directory/Windows account but a SQL account will work if necessary) and, if you are expected to create and manage databases in SQL, some server roles such as dbcreator, securityadmin, setupadmin, and serveradmin.
Given that you are just starting out, and assuming you are running sql server Standard Edition (SS-EE), it might be useful to create another instance of sql on your server for testing / development. SS-SE supports up to 16 separate instances on a single box so adding one or two more won't violate the license and can help you isolate your sandbox work from the big sand castle you will be building on the beach. In the sandbox instance, you could be given virtually full rights. You possibly shouldn't aspire to the 'sysadmin' role on your production instance until you've got a higher than green belt as a DBA. It wouldn't be so dangerous to learn that role on the sandbox instance.
As you have probably surmised by now, you have a lot of learning ahead of you. The world of SQL contains concepts, methods, and functions that largely don't exist in ACCESS. Adopting SQL Server as your datastore at least doubles your learning burden as well as the sources of problems and responsibilities. With Access, your burden almost exclusively application management.
SQL is about industrial strength data management and all that implies. Much can be automated, but where automation fails, as it will, you will need to dig much deeper for solutions than you ever would with Access alone.
So, take this step with your eyes open and your knees bent.
Good luck
April 30, 2008 at 7:07 am
I forgot to ask about my other important question:
SQL SERVER MIGRATION ASSISTANT! (SSMA) YEAH!
Using SQL 2005 I try to run the SSMA to migrate a local .mdb with 3 tables (as a test). I get the following pop-up error:
"This Version of SQL Server is not supported. You must connect to an instance of SQL Server 2005)".
I am connected in the background to the DB, and I have SSMA open: I'm including the following information on my system:
C:\Documents and Settings\>sc query mssqlserver
SERVICE_NAME: mssqlserver
TYPE : 10 WIN32_OWN_PROCESS
STATE : 4 RUNNING
(STOPPABLE, PAUSABLE, ACCEPTS_SHUTDOWN)
WIN32_EXIT_CODE : 0 (0x0)
SERVICE_EXIT_CODE : 0 (0x0)
CHECKPOINT : 0x0
WAIT_HINT : 0x0
Add/Remove Programs = SQL 2005 Server
C:\Documents and Settings\>netstat -aon | findstr 1433
TCP 0.0.0.0:1433 0.0.0.0:0 LISTENING 1932
Under Register:
HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\Microsoft SQL Server Migration Assistant for AccessBuild Version: 0x00000354 (852)
HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\Microsoft SQL Server\MSSQL.2\MSSQLServer\CurrentVersion 9.00.3042.00
Any suggestions
I have Microsoft SQL 2000 Product 8.0.760 SP3. This information is revealed through the SQL Server Management Studio under the Properties > Connection Info when right-clicking upon the INSTANCE NAME.
Once again, I'm connecting through SQL Server Management Studio, but cannot connect through the SSMA. I can configure settings through SSMS, but cannot through SSMA.
From what I can conclude from the error message, I will not be able to use the SSMA to migrate Access 2003 objects to this version of SQL 2000. And that I will have to use the upsizer. Am I correct?
Once 'gain, my grateful thanks for the assistance so far. Am more enthusiastic to encounter many of the obstacles ahead.
For the rest, Scott, what a wonderful reply! Many thanks. I look forward to the road ahead.
(I may have also cross-posted this question under another appropriate category).
April 30, 2008 at 2:17 pm
I believe that msg is telling you that your destination database must be hosted on an instance of SQL2005. If you are trying to connect to sql express and getting this message, it must want it to move to sql workgroup or standard or enterprise. You won't be able to migrate to any 8.x version using SSMA.
BTW, are you using SSMA v.4 CT1? This is the latest (not finalized, hence the CT1) which may work better than earlier versions. I just discovered it yesterday (loaded on a new machine from MS' SSMA site).
After you license it, create your migration project, and add you MDB objects, you will need to connect to a SQL Server 2005 instance. If the instance is on a machine other than the one you are running SSMA on, you address it as [machineName] or [machineName\instanceName]. Note that this is NOT a UNC address so it does NOT begin with '\\'.
May 2, 2008 at 10:27 am
Scott MacCready (4/30/2008)
I believe that msg is telling you that your destination database must be hosted on an instance of SQL2005. If you are trying to connect to sql express and getting this message, it must want it to move to sql workgroup or standard or enterprise. You won't be able to migrate to any 8.x version using SSMA.
Scott, your absolutely correct! I noticed the following on MS SSMA download website:
"The ability to access the computer that hosts the target instance of SQL Server 2005. " And obviously explains the error message I'm receiving. DUH! :Whistling:
Scott MacCready (4/30/2008)
BTW, are you using SSMA v.4 CT1? This is the latest (not finalized, hence the CT1) which may work better than earlier versions. I just discovered it yesterday (loaded on a new machine from MS' SSMA site).
At the MS SSMA Web site they show the file name as:
SSMA for Access_1.2.852.msi . I know this is not the version you are referring to. This is the version I had been using.
I don't need the enhanced report capabilities of the SSMA. Can I trust the upsizer to migrate tables, reports and forms? I'll like to have an alternative front-end to the tables in SQL rather than Access. Any suggestions?
Scott, thanks!
May 2, 2008 at 4:34 pm
I think I mentioned earlier (not sure if in this thread), no you can't count on the ACCESS Upsizing Wizard or the SQL Server Import Wizard to capture everything. It MIGHT, but if it doesn't, you won't know why. Therefore, I suggest using either or both of the wizards and compare results. Record count comparisons between source and target will be your first test. If they don't match, try SSMA.
One possible downside of SSMA is that it may gratuitously add a LOTS of indexes to your tables. This can really slow down response time. Check em. And, it may insert default values (zero for numeric fields) that you don't want.
Nothing upsizes forms or reports. In principle, reports could be converted to Reporting Services documents but it doesn't. If this is what you want, you must look to the Reporting Services tools.
.
Once tables and queries have been moved to a SQL db, you can link to it using the File>External>Link menu path of Access to connect to them from an Access mdb front end file (containing your forms and reports (and queries(?))) or you can use the New database wizard from the task bar of Access2003 to create a new project (for ADP/ADO architecture). It is not possible to do this with Access2007 - hence (way back) my inference that ADP/ADO is being deprecated.
The SQL Server based presentation is Reporting Services which requires IIS. Simple Access reports can be converted simply but more complex stuff will require a labor intensive migration. The SQL Team has been working on alternative ways to present the information implied by SQL Server hosted data. One of their recent offerings is an Excel-based tool which does a pretty good job for analysts. This is a free download from somewhere in M~S~.com.
MS has recently introduced a graphics component for use in Visual Studio apps. There are many 3rd party graphics libraries on the market if this is your path.
For presentation, outside of Access/Excel, there is a wide variety of tools out there.
We haven't talked much about what the database contains and what you want to learn from it. Answers to those questions could drive you into a smaller set of alternative solutions. So, use Google on 'sql data presentation' or some such with additional qualifier as implied below.
Many tools are domain-oriented, AKA vertical market tools.
Probably the most commonly used alternative to Access for low-cost/function point reporting is Crystal Reports. It's distinctive competence is simultaneous connection to a wide variety of host data storage engines. It can build cross-architecture queries which can pre-empt consolidating data into data marts or warehouses.
If that is what you need, hard to beat. It has evolved toward an Access style user interface over time and is not an easier to use solution than Office for SQL ONLY hosted data.
For data entry, outside of Access, you are looking at VB or C# or ASP or... No further specific advice except to check your assumptions about the advantages of moving to a much more labor intensive approach. The new LINQ feature in VS and SQL2008 can be advantageous if you are going in this direction. More efficient programming. However, notice this is NEW in SQL 2008
May 6, 2008 at 2:34 pm
Scott MacCready (5/2/2008)
I think I mentioned earlier (not sure if in this thread), no you can't count on the ACCESS Upsizing Wizard or the SQL Server Import Wizard to capture everything. It MIGHT, but if it doesn't, you won't know why. Therefore, I suggest using either or both of the wizards and compare results. Record count comparisons between source and target will be your first test. If they don't match, try SSMA.
Do you have any "for examples" to what the Upsizer wizard will not "capture". At the most, I need to migrate tables and queries (and from my understanding, reports and forms cannot be Upsized ---thats okay with me, as long as I can merely link them from Access to the back-end SQL tables and queries residing on the server after the Upsize has completed)
Scott MacCready (5/2/2008)
For data entry, outside of Access, you are looking at VB or C# or ASP or... No further specific advice except to check your assumptions about the advantages of moving to a much more labor intensive approach.
Just to clarify your observation, can VB or C# or ASP applications be developed replicate the functionality of Access Reports and Forms? In your paragraph you mentioned just data entry (i.e. forms). I'm sure these prog. technologies can handle simple dynamic HTML report functionality similiar to Access reports This will be my ultimate objective.
Once again, Scott, thanks for helping me out. You've been a tremendous resource!
May 6, 2008 at 6:16 pm
It only captures tables (and the associated defaults, constraints, indexes, etc.) and queries. You can get set it to create the links if you include both the front end (forms and reports) as well as the backend in your migration project. What I mean by 'not count on' is that it may fail to pull all (or any) data for certain tables. This may be due to defective data or other issues. If a wizard (Access or SQL) succeeds, great. However, they provide next to no information about why they are only partially successful.
SSMA, on the other hand, provides 'info', 'warnings' and 'errors', documenting the entire process. Therefore, IF something is problematical, you've got some clues, at least, about what to fix in the source data.
--------------------------------
For data entry using Visual Studio languages, you will probably use winforms. There are probably some 3rd party libraries to leverage. This is not something I'm involved in (yet) and (from my biased perspective) I'd only suggest abandoning Access if you must. If it is not up to you, well, never mind.
Some people use Access for prototyping and requirements validation. Then, as needed, they implement on a different platform. This saves a lot of time since the time cost of feature implementation is so much lower in Access.
Some use a hybrid system: data entry for LOTS of users using asp, for example, and data management tools in Access. MS itself does this. I mentioned somewhere weeks ago (maybe this thread) that one of the Access Program Managers built a commercial website using Access forms and reports for backend management of a sql server. He claimed that he built the functionality for about 20 percent of what would have been required to use VB or C#. These tools reduced time costs for posting new items (for sale) by a factor of two or three. This competitive edge effectively forced his competition to move their inventory to his site to remain viable.
There are lots of 3rd party apps and libraries to leverage for reporting. Also, you should look at the Sql Server Reporting Services (SSRS) for reports. This is integrated into both Sql Express and higher. It is also possible to use Access for report prototyping for SSRS. It can import simple access report designs. You can then enhance or recreate if necessary.
Exploitating business intelligence features is a key reason some move to SQL. These features (Analysis Services, Integration Services, and Reporting Services) are developed in the Business Intelligence Development Studio (aka BIDS). This is a variant of Visual Studio which is installed on the client IF any of those components are installed on the server. It is a different world than SSMS in many ways. Not much carry-over learning.
Viewing 15 posts - 1 through 15 (of 15 total)
You must be logged in to reply to this topic. Login to reply