Access 2007 And Web Services, Part 2

Microsoft Office Access Program Manager Ric Lewis takes his Web Services with Microsoft Office Access 2007 example a bit further with a managed Add-in to work with Amazon.com's Web Service to gather book information into the Access 2007 database application. Ensure that you download the code project, since the example code on the Web page is only a partial listing.

Access 2007 And Web Services, Part 1

If you're interested in how to use Web Services with Access, Microsoft Office Access Program Manager Ric Lewis put together a sample book database application that connects to Amazon.com's Web services to collect book information from the ISBN number. His sample requires the .Net 3.0 runtime, so if you don't have Vista installed, you'll need to download it for Windows XP SP-2 or Windows 2003 server. It won't work on earlier versions of Windows.

Time To Rethink Autoshrink

SQL Server has had the autoshrink capability since version 7.0, and DBA's have been using it ever since to automatically maintain data file size and transaction log file size. Under certain circumstances this works well, especially for the many organizations who don't have a DBA available to shrink these files manually when necessary. However, there can be serious performance and disk space issues when the database engine controls the shrinking and subsequent growing of the files when more space is needed for the data.

Microsoft SQL Server MVP Tibor Karaszi wrote an excellent article explaining the autoshrink problem and provides test evidence to help convince SQL Server DBA's who may have already decided to turn on the autoshrink database option, because it's a "no-brainer."

Why That Access Database Is So Slow

Ever wonder why an Access database on your own computer runs so fast, but when it's put on the network and other users access it, it becomes as slow as molasses in wintertime? Here's an explanation of why an Access database is so slow in "really simple terms."

For plenty of easy solutions to this common database performance problem, please read Microsoft Access MVP Tom Wickerath's article, "Implementing a Successful Multiuser Access/JET Application."

Oracle Migration Workbench

The Oracle Migration Workbench (OMW) is a handy tool for migrating data and database objects from other database engines to Oracle 9i or Oracle 10g databases. The OMW supports migrations from SQL Server 6.5, 7, and 2000 on Windows platforms, Microsoft Access 2.0, 95, 97, 2000, 2002, and 2003 on Windows platforms, Sybase Adaptive Server 11 and 12 on Windows and Linux platforms, Informix Dynamic Server 7.3, 9.1, 9.2, 9.3, and 9.4 on Windows and Linux platforms, MySQL 3.22, 3.23, and 4.X (or 5.0 with a special plug-in) on Windows and Linux platforms, IBM DB2/400 V4R3 and V4R5 on Windows platforms, and IBM DB2 UDB V6, V7.1, and V7.2 (Beta) on Windows platforms.

Of special note is the fact that the XML Exporter allows the simultaneous export of multiple Jet MDB files into either a single schema or multiple schemas. The OMW also supports migration of Jet queries to Oracle views.

Handy Linux Commands For Oracle Professionals

Arup Nanda, an Oracle Ace, Oracle Magazine's DBA of the Year in 2003, and co-author of PL/SQL For DBA's, wrote two handy Linux guides for both Oracle developers and DBA's. (It should be noted that most of these Linux commands work in the many flavors of both Linux and Unix, and depending upon the shell being used, these commands rarely need any modification at all.) Part 1 covers basic file commands, while part 2 covers more advanced file commands.

Migrating To SQL Server From Oracle, Part 4

If you're migrating an Oracle database on a Unix server to SQL Server on a Windows server, Microsoft has a TechNet guide to help the migration. This guide is specifically for SQL Server 2000 on Windows 2003 Server or Windows 2000 Server. The guide hasn't been updated for SQL Server 2005 or Windows Vista or Longhorn. There's no word yet on when it will be updated, so if you plan to migrate to SQL Server 2005 or to Windows Vista or Longhorn, it may pay to hold off a little longer so that all the issues can be covered in the guide after thorough testing with someone else's guinea pig.

Migrating To SQL Server From Oracle, Part 3

If you're migrating data from an Oracle database to SQL Server 2005, use Microsoft's new tool, the SQL Server Migration Assistant for Oracle (SSMA Oracle). As I mentioned in my previous post for the SSMA for Access tool for migrating data from Access to SQL Server 2005, SSMA for Oracle is very similar in that it converts Oracle's database objects to SQL Server database objects, loads those objects into a SQL Server database, and then migrates the data from the Oracle database to the SQL Server database. If you've ever migrated from one database engine to another, you know that converting data types is difficult enough, but converting database objects can be that much more frustrating. Microsoft has helped ease the pain a little by developing this tool to automate as much as possible.

Migrating To SQL Server From Oracle, Part 2

If your data is stored in an Oracle 8i or 9i database, Microsoft wants you to compare the total cost of ownership of keeping the data in its current configuration with migrating to Oracle 10g or to SQL Server 2005. Microsoft has provided a white paper, "Should You Migrate from Oracle to SQL Server?" to help the decision making process in your organization.

It's not just the financial impact on keeping the status quo versus migrating, but also the database performance that may be impacted when migrating that needs consideration. Microsoft suggests solutions to the percieved barriers to data migration in this white paper, so it's worth a read if your organization is considering ways to cut costs for data storage.

This white paper is a 1.48 MB PDF file. If you don't have Adobe Acrobat Reader installed, Microsoft offers a link.

Migrating To SQL Server From Oracle, Part 1

If you're an Oracle professional and need to either work with SQL Server 2005 or are preparing to migrate data from an Oracle database to SQL Server 2005, read Microsoft's white paper, "SQL Server 2005 for Oracle Professionals," to help you better understand the key differences between the two. The two database engines have many similar concepts because both are relational databases, but implementation of these concepts can use different methodologies. Sometimes they both use the same name for different concepts, so don't let yourself get confused when jumping from one platform to the other.

This white paper is a 488 KB PDF file, so it's a quick download. If you don't have Adobe Acrobat Reader installed, Microsoft offers a link.

Upsizing To SQL Server, Part 2

If you're using the SQL Server Migration Assistant for Access (SSMA Access) to migrate your Microsoft Access database back end to SQL Server 2005 as I mentioned in my previous post, then you should read Andy Baron's white paper, "Optimizing Microsoft Office Access Applications Lnked to SQL Server." He offers excellent techniques for improving performance when using linked SQL Server tables.

Upsizing To SQL Server

The Upsizing Wizard has been available with numerous versions of Microsoft Access to migrate data in a Microsoft Access (Jet) back end to the tables in a SQL Server database. It did a good job for the most part, but if your table structures had any discrepancies, some of the columns wouldn't migrate to SQL Server. Even SELECT queries might not be converted to views in SQL Server. Migrating data to SQL Server and linking to the tables was not a seemless operation, because invariably some glitches would keep the newly migrated data in the linked tables from being accessed by the original queries.

For SQL Server 2005, Microsoft created a better data migration tool, the SQL Server Migration Assistant for Access (SSMA Access). It will migrate the data from Microsoft Access 97 through Access 2003 (and likely 2007, too, but I haven't tried it yet) to SQL Server 2005, including the SQL Server 2005 Express Edition. This tool converts the original database objects into SQL Server database objects, loads these objects in SQL Server, and then migrates the data from the original tables into the new SQL Server tables. This is done in a single step.

Microsoft Access developers should have a little easier time migrating data with this tool when compared to the additional "fix-it" work required with the Upsizing Wizard, since a number of the issues with the Upsizing Wizard are fixed in the SSMA Access tool. In addition, network scanning, conversion assessment reports, and other features are now available in the SSMA Access tool.

Introduction

My other blog is more generic in order to appeal to mainstream computer users, not just database developers and users. I decided to branch off with this blog on database tips because I realize that many of my readers (at least the ones from the newsgroups) are looking for database tips, but a database-oriented blog would bore the majority of my other readers. So if you're interested in computer software, the Internet, and security in general, as well as the free things I find on the Internet, please continue to read my other blog, Data Devil Dog. If you're interested in databases, specifically Microsoft Access and the things one can do with Access when connected to other database engines, please read this blog, Database Tips. But Microsoft Access isn't the only database out there, it's just the most popular, so I'll also add tips for working with other database engines, especially Oracle, SQL Server and MySQL, which are the other most popular databases out there.

I won't promise to write in this blog every day, or even every week, but I'll try to post new entries at least five or six times per month and update older entries as needed. I don't want to duplicate content that can be found elsewhere on the Internet, so I plan to start off with using this blog as a guidepost to point readers in the right direction of where to find "the good stuff," and add my own tips that I've found helpful as I go along.

If you have a blog (or even a Web site!) about databases, or computers, or the Internet, then please link to this blog on your blogroll, and I'll be happy to link to yours to help us all get more links.