Tony's Microsoft Access Blog : SQL Server

"Slacker DBs" Vs. Old-Guard DBs

Tony — Tue, 24 Mar 2009 20:06:52 GMT

A very interesting summary at Slashdot - "Slacker DBs" Vs. Old-Guard DBs on an InfoWorld article Test Center: Slacker databases break all the old rules

I saw this problem from a different perspective in 2000 or so when I purchased my Palm Vx. I was taken aback to only see room for exactly five phone numbers and/or email addresses. But when I thought about it I realized that was a reasonable limitation given that the concept of Joins almost certainly isn't built into the Palm OS and program. I quite liked the Palm desktop interface and so that's where I entered much of my information.

A while later my sister purchased a Palm but was using the contact manager built into Outlook. She occasionally had more than the five contact means in Outlook and was rather ticked that the sixth or seventh wouldn't import.

But getting back to cloud computing.

Microsoft is joining the cloud as well with SQL Server under the name Microsoft SQL Data Services as part of the Azure Services Platform. This is a good thing as I really, really like relational data systems. With all the other utilities and safeguards available when MS comes at this problem from the SQL Server perspective.

The perspective that folks with no relational database experience bring rather concerns me.

An example of this is MS bringing in Dave Cutler to lead the Windows NT 3.1 team with their multi user, multi tasking experience. The DOS based folks at MS would've stumbled badly and, in my not so humble opinion, could've done a better job in the Windows 95, 98 and ME family.

For example, formatting a floppy in Windows 95/98 or ME took over the entire system. You couldn't do anything else. And yet within OS/2, a true multi tasking system, you didn't notice a floppy being formatted and the task manager showed it only took a few percentages of the CPU.

That all said I can understand why Facebook or other huge non critical sites with hundreds of millions of records would deliberately denormalize their data. For example denormalizing the contact info or website's would drastically reduce CPU and disk access times thus improving response times.

On the fourth hand all my apps are going to be properly normalized!

Internals viewer for SQL Server

Tony — Fri, 02 Jan 2009 02:32:57 GMT

Internals viewer for SQL Server I always enjoy peeking under the covers. Hey, computer software covers. Get your mind out of the gutter. This blog posting mentions how to poke about under the covers of SQL Server.

Similar to MDB Tools - A set of libraries and utilities for reading Microsoft Access database (MDB) files.

Download the file and open the HACKING file.

Yo momma jokes

Tony — Sat, 01 Nov 2008 18:49:45 GMT

SQL Server yo momma geek jokes on twitter

You won't get these unless you know SQL Server.

SQL Server 2008 RTM

Tony — Wed, 06 Aug 2008 23:52:09 GMT

Microsoft Releases SQL Server 2008 It's downloadable via MSDN Subscriber Downloads and TechNet Subscriber Downloads.

Top Tips for Effective [SQL Server] Database Maintenance

Tony — Fri, 11 Jul 2008 22:43:54 GMT

"Several times a week I'm asked for advice on how to effectively maintain a production [SQL Server] database. Sometimes the questions come from DBAs who are implementing new solutions and want help fine-tuning maintenance practices to fit their new databases' characteristics. More frequently, however, the questions come from people who are not professional DBAs but for one reason or another have been given ownership of and responsibility for a [SQL Server] database. I like to call this role the "involuntary DBA." The focus of this article is to provide a primer of database maintenance best-practices for all the involuntary DBAs out there."

Top Tips for Effective Database Maintenance

Note: I added [SQL Server] as appropriate.

Bug in ADO, SQL Server and latest OS SPs

Tony — Sun, 11 May 2008 04:56:55 GMT

On a computer that is running Windows Vista, Windows Server 2008, or Windows XP, an incorrect value is returned when an application queries the identity column value of a newly inserted row in various versions of SQL Server 2005 and of SQL Server 2000

Now I don't know that this affects Access but the KB article states "Applications that use the ActiveX Data Objects (ADO) interface ..."

Thanks to fellow MVP Steve Foster for mentioning this KB article.

SQL Server 2005 Best Practices Analyzer (January 2008)

Tony — Sun, 20 Jan 2008 23:54:46 GMT

"The SQL Server 2005 Best Practices Analyzer (BPA) gathers data from Microsoft Windows and SQL Server configuration settings. BPA uses a predefined list of SQL Server 2005 recommendations and best practices to determine if there are potential issues in the database environment."

SQL Server 2005 Best Practices Analyzer (January 2008)

SQL Server White Papers: Migration from ... Microsoft Access to Microsoft SQL Server

Tony — Fri, 14 Dec 2007 18:58:01 GMT

SQL Server White Papers: Migration from Oracle Sybase, or Microsoft Access to Microsoft SQL Server

Access 2003 SP3 bug on bit data type field in a linked SQL Server table

Tony — Wed, 07 Nov 2007 23:13:00 GMT

We've been seeing reports of this in the newsgroups. Here's Microsoft's KB article: After you install Office 2003 SP3, you encounter various problems in an Access project when you work with a Bit data type column in a linked SQL Server table

Optimizing Microsoft Office Access Applications Linked to SQL Server

Tony — Fri, 19 Oct 2007 04:14:34 GMT

Tom van Stiphout, fellow Access newsgroup denizen, alerted me to the following MSDN article: Optimizing Microsoft Office Access Applications Linked to SQL Server. A link was also added to the Access Performance FAQ.

Note the following paragraph:

"Developers often migrate data to SQL Server expecting an improvement in application performance. Although performance does often improve, there are many cases where it remains the same or even degrades. In some cases, performance of certain queries degrades to an unacceptable level."

Norton Antivirus strikes again - ODBC to SQL Server 2005

Tony — Tue, 19 Jun 2007 18:56:00 GMT

From a recent newgroup posting

> I'm trying to link an Access 2003 project to a SQL 2005 database on a
> network server. Everything seems to go okay until I select ODBC as the type
> of source I want to link to and them nothing happens. If I select any of
> the other types of files such as Excel I get the appriiate dialog box. What
> is going on??? I'm running VISTA 64 Bit on the wokstation and Windows 2003
> Server on the database server.
>
I had the same problem - felt a bit dishearted when I saw there was no
response on your question. But - I found the solution!

Van T. Dinh (MVP (Access) wrote in one of the other groups:
"If you use Norton AntiVirus, try disabling the Office Plug-in. You'll
find the Office Plug-in under 'Miscellaneous' in the Norton AntiVirus
options."

Update 2007-06-21. Fellow MVP Jens Schilling mentioned this problem back in 2006-10-03 in this Google posting titled Connecting to data sources

New version of SQL Server Migration Assistant for Access available for download

Tony — Sat, 26 May 2007 17:42:00 GMT

SQL Server Migration Assistant for Access 1.2 - Microsoft SQL Server Migration Assistant (SSMA) for Access is a tool for migrating databases from Microsoft Access to Microsoft SQL Server 2005. SSMA for Access converts Access database objects to SQL Server database objects, loads those objects into SQL Server, and then migrates data from Access to SQL Server.