Your Questions Answered - how SQL Server 2005 works with Office 2007

Grant Paisley - Monday, January 01, 2007
Greg Linwood and Grant Paisley team up to answer your questions about how SQL Server 2005 works with Office 2007.

This article first appeared in ZDNet Australia's Microsoft MVP Series Site

Can you point me to cost saving stats which I can take to my boss to convince him that an upgrade to SQL Server 05 is a must-do from our current ss2000 environ?

I'm not aware of any published cost savings statistics, but one very clear cost saving advantage is that SQL Server 2005 Standard Edition has removed the previous memory constraints imposed by SQL Server 2000 Standard Edition (2Gb). This means that a version upgrade from SQL Server 2000 Standard Edition to SQL Server 2005 Standard Edition is much cheaper than an edition upgrade to SQL Server 2000 Enterprise Edition. Another way of looking at this is that SQL Server 2005 Standard Edition can handle vastly larger workloads than SQL Server 2000 Standard Edition, simply because of it's significantly improved memory addressability. There are many other cost saving manageability aspects with SQL Server 2005, but being able to address significantly more memory is a real winner on its own!

Is SQL 2005 going to be intergrated into Exchange 2007 nativelly.

Not that I'm aware of. I'm a SQL Server specialist, but I believe Exchange 2007 still uses its own DBengine.

Is is true that you can use Excel as a reporting tool now and get data from different data sources, cubes, etc.?

Excel has always supported getting data from multiple data sources via ODBC and OLEDB - what is new in Excel 2007 is it’s richer graphical capabilities. For instance it now has Conditional Formatting options such as

  • "Data bars" - overlays transparent bar on data value
  • "Color Scales" - heat map cell coloring
  • "Icons" - traditional KPI type flags

It also natively supports SQL Analysis Server 2005 including displaying KPI’s. Even better with Excel Server you can publish Excel 2007 workbooks up onto a server where users can access the Excel/Excel Report with just a browser.

What do you need to have installed on your desktop PC to use Office with SQL Server 2005? Can you use previous versions of Office as well?

I must admit that I'm still using Office 2003 with SQL Server 2000 & 2005 on my notebook, so I can confirm this works (as far as I'm aware, there are no special installation requirements -- you just install Office, connect to SQL Server 2005 & start work!

You just need a standard install of Office XP, 2003 or 2007 as they will all play with SQL 2005 fine. If you want to interrogate SQL Server Analysis Server 2005 (SSAS2005) then any version of Excel will work, though only Office 2007 will best utilize the SSAS 2005 features. Where things get exciting is when utilizing Office 2007 with Office SharePoint Server 2007 - this combination gives true enterprise functionality, including document libraries, versioning, work flow, thin client Excel publication, enterprise search...

I want to run SQL Server 2000 and SQL Server 2005 on the same server until I can get another machine -- is this possible?

Absolutely. It's possible to run a few instances of both actually, using named instances. I personally keep one SQL Server 2000 named instances & one SQL Server 2005 named instance running on my notebook for support purposes, although I tend to turn the SQL Server 2000 instance off via the Windows Services applet (under Administrative Tools) to save CPU & memory resources whilst I'm not using it.

How much does it cost to upgrade to SQL Server 2005?

Licensing questions such as these are very tricky as there are many possible answers. It depends on which existing edition & version of SQL Server you currently have, whether you have software assurance, possibly an enterprise agreement, whether you're licensing per seat / CPU or via SPLA. So unfortunately, I have to refer to your reseller or Microsoft for this.

Is it true you can use Excel as a reporting tool?

This is not new -- Excel 2003 can look at Analysis Server 2000 and 2005 "cubes" but user interface is challenging for the user with Office 2003 looking at SQL 2005 Analysis Server -- eg Office 2007 gives much better support to Analysis Server now. Excel as a reporting tool is especially useful when combined with Office SharePoint Server 2007. So power users have Excel 2007 and publish up into a SharePoint document library. Other users only require a browser to display the Excel worksheet/report.

I am looking to build a scorecarding application using Excel as the front-end and want to use SQL Server 2005. Can I use Excel XP to get started or do I have to install Office 2007 first to get it to work with SQL Server.

Assuming you are talking about building a scorecard against Analysis Server, I would recommend moving straight to Excel 2007 as it directly supports KPIs, and has richer graphical capabilities. For instance it now has Conditional Formatting options such as

  • "Data bars" - overlays transparent bar on data value
  • "Color Scales" - heat map cell coloring
  • "Icons" - traditional KPI type flags

Even better with Excel Server (part of Office SharePoint Server) you can publish Excel 2007 workbooks up onto a server where users can access the Excel/Excel Report with just a browser. You could build it with Excel XP but you would get very frustrated...

Excel 2007 would be the best choice for building a scorecard as it natively includes indicators and data bars.

Note: you can install Excel 2007 and keep you existing version of Excel without any problems. So you *could* start on Excel XP.

Can I still use Microsoft Access as a front-end to SQL Server 2005? Will I need to upgrade to the latest version of Office? If that is the case, I probably won't upgrade to SQL Server 2000 as we have deployed Office 2000 at the moment.

I'm not sure about Access 2000, but Access 2003 certainly works well with SQL Server 2005 (we use this combination extensively at work). Given that Access's pass-through query feature works simply over ODBC & SQL Server 2005 still supports ODBC, you shouldn't expect any problems in this area.

Are there any plans to update the query tool in Excel 2007 or future versions? We use it extensively for pulling data from various databases/sources and probably won't upgrade until we see what we can do with Excel 2007.

Query building tools really only generate SQL statements, so there's no reason why you can't use another tool if one doesn't cut it for you. As SQL is my primary language, I haven't learned the finer points of difference between the various tools, but I know that Visual Studio, MS Access & SQL Management Studio all have their own flavours of these tools, so it might be worth trying a few others out to see which you like best.

I'd like to find out more about the query mechanism in Excel 2007. Does Microsoft have any plans to update it?

I can't say whether Microsoft have any further plans to upgrade query mechanisms in Excel 2007, but as with the previous answer, I'd suggest you shop around some of the other query tools and see if any other do the job better for you.

What is the policy regarding upgrades for SQL? If just bought SQL Server 2000 but I want to use 2005 now. Is there someplace I can download an updater or something? I looked on the M$FT site and they only had a trial version of SQL Server 2005.

Naturally, you'd need to purchase an upgrade licence from a reseller, unless you have software assurance.. I wish I could set policy otherwise.

SQL Servants... is that spelled correctly?

Yep -- we're just humble little SQL Server Servants here.

Can you install SQL Server 2005 on a 64x box? We are getting a new 64x box for Exchange (which I am still fuming over) and want to put SQL on the same platform.

Yes, SQL Server 2005 runs great on x64. I'd recommend thinking twice about running SQL Server & Exchange side-by-side though as they're both really designed as stand-alone server solutions & will compete with each other for resournces (memory, CPU etc).

Are indexes any better in SQL 2005? I have queries from Excel that run like a dog (no offense to the dog).

I've met a couple of slow dogs that couldn't get away from me but that's another story! As for indexes, there are some MAJOR improvements in SQL Server 2005. By far my favourite improvement is online index rebuilds. I've lost a lot of sleep over the years managing index rebuilds on transaction processing systems which can't be taken offline until late in the evening / early morning. With SQL Server 2000, you had to rebuild your indexes either late at night or block transactions if you did it during the day. SQL Server 2005's ability to rebuild indexes online means you have much more flexibility about when you rebuild indexes, which translates indirectly to more personal sleep! (not with dogs though). Included columns can help "cover" more queries, improving performance & being able to disable indexes can also help make smaller copies of databases too.

How would you reccomend going about upgrading from MSDE 2000 to SQL Server 2005? We have a VB front-end that we use for reporting on a number of small databases, but we would like to move this to Excel 2007 if given the chance.

There are a very small number of potential gotchas in upgrading between MSDE 2000 to SQL Server 2005. As MSDE didn't have DTS, by far the biggest gotcha area in SQL 2000 > SQL 2005 upgrades is right out of the picture. There ar a few deprecated language statements, but it's very unlikely you're using those unless you've got some very old code. So, I suggest you simply download a trial version of SQL Server 2005, restore a backup of your MSDE 2000 DB to your new SQL Server 2005 instance (this should just work) & test for any errors, which should be very unlikely. As for whether migrating to Excel 2007 works better for you than VB.NET, this will be very application specific, but one thing I still love about VB.NET over Excel is the richer .NET language & tools.

I have downloaded the evaluation copy of Office 2007 and installed it but I don't see where you get into SQL Server.

Go to www.microsoft.com/sql & pull down a copy of SQL Server 2005 Express Edition from the left hand "Downloads" link. It's free & you should be able to connect direct to it from Office 2007 once you've got it set up.

What is AS 2005?

[A]nalysis [S]ervices 2005 (AS 2005) is SQL Server's multi-dimensional (OLAP) engine. The main relational engine (RDBMS) works with two dimensional tables (rows & columns) & is typically used for transaction processing via it's Transact-SQL (TSQL) language. AS 2005 works with multi-dimensional "cubes" via its Multi-Dimensional Expressions (MDX) language. Cubes are typically used for reporting & analytical purposes where running queries against the base transaction processing RDBMS databases would be too cumbersome or inefficient. Data is typically extracted from relational databases & transformed (aggregated) into multi-dimensional structures, representing summary views of information which needs to be quickly & easily analysed (eg sales data).

What are the advantages of SQL Server over open-source alternatives such as MySQL and PostgreSQL? Is the cost justified?

Who knows how well MySQL and PostgreSQL can perform when they don't post benchmarked performance results to TPC.org? Sure, claims can be made but TPC is the participant agreed industry standard for measuring performance & I've not seen any audited benchmarks from those guys so its impossible to really say how well they can perform. Don't forget that it's usually the hardware vendors who are trying to boast about hardware performance in these benchmarks, so if there were any advantages in choosing mySQL / PostgreSQL, they'd be sure to choose them. The benchmarks are cost / performance oriented, so it's hard for them to even make the cost saving argument plausible.. Like so many things - you get what you pay for with database management systems.