Random Rants & Raves

A recent project I am involved in requires us to demonstrate a bi-directional replication setup between SQL Server 2000 and SQL Server 2008. There are a few gotchas one needs to be aware of while doing something like this.

First off, make sure that the SQL Server 2000 server has the latest service pack installed. Next, on the SQL Server 2008 server, open up the Distributor properties and add the name of the SQL Server 2000 server. Give a strong administrative link password when prompted.

Now, on the SQL Server 2000 server, configure distribution. Make sure you specify that the Distributor is running on the SQL Server 2008 server. (Note: if the server is already configured as a distributor and has a number of publications, read this MSDN article to see how it can be changed.)

Next comes an important part before you add publications from a SQL Server 2000 database to a SQL Server 2008 database. For each article (read: table) that you wish to publish on 2000 and subscribe on 2008, you need to have 3 stored procedures on the subscriber database – one each for INSERT, UPDATE and DELETE.

That is, if you have a table tbl2000 on SQL Server 2000 that needs to be published to a subscriber database called tbl2008 on SQL Server 2008, the SQL Server 2008 database needs 3 stored procedures called say, sp_ins_tbl2008, sp_upd_tbl2008 & sp_del_tbl2008. (Note: normally, the publication script can create these automatically when you give the schema_option=0x02 in the sp_addarticle procedure. But for some reason, it doesn’t seem to work across 2000 and 2008 scenarios that I tried.) These stored procedures basically insert, update and delete the records in the subscriber (on SQL2008) database.

So, to create a publication, you will need to NOT use the publication wizard that you get in SQL 2008. Instead, use a generated publication script and for each sp_addarticle line that you have in it, make sure that the @ins_cmd, @upd_cmd & @del_cmd parameters point to the appropriate stored procedures and run it on the SQL Server 2000 server. Once this is done, go ahead and create a subscription to SQL Server 2008’s database normally.

For the other side (that is, SQL Server 2008 to SQL Server 2000), normal publish-subscribe rules apply and work fine.

Tags:
Categories: SQL Server | Tips | Development

As is usual to a slightly long post, I start with a bit of historical context. In the days back when I was a student in school, computers were a fairly new thing. I was lucky to have started early on BBC Micros and having access to the Internet waaaay before most people had heard of it thanks to growing up in one of India’s premier universities. In school, our PCs, PC-XTs and PC-ATs were major drool points for us geeks.

However, software was another issue. A few of us loved programming – but access to software was fairly impossible. Getting Turbo Pascal and Turbo C++ was quite difficult and it was usually from a (friend-of-a-friend)^n from where we would (ahem) make copies to try out our pieces of code. I remember writing (literally) entire programs on sheets of paper with dry run notations on the side for each variable for each pass and then go type it out in school to try out some new stuff that I had “created”.

These days however, access to software is much easier. Unfortunately, professional quality software is usually quite expensive and for a student in India, typically out of their budget. This is where Microsoft’s recently announced DreamSpark initiative comes to their rescue.

DreamSpark allows students in India to download or get a DVD of most of the Microsoft programming and designing stack for FREE! All a student needs to do is register at the site, get their student status verified and they can get access to the free download and go and simply pick up the free DVD from any of the over 200 locations in India and start using it.

And what is it that they get for free? The list includes:

• Windows Server 2003 Standard
• Visual Studio 2008 Professional
• Visual Studio 2005 Professional
• SQL Server 2005 Developer
• Expression Studio
• XNA Game Studio 2.0

Remember that these are FULL versions of these products, without any restrictions (time or uses) for FREE.

This means that computer science students can now start working with professional level tools right from the start. Also, if anybody uses the “Open Source is best for learning programming” argument, this is no longer true. The Microsoft programming stack is so much more productive and now is accessible by any student in India and all for free to. I wish this was available to me when I was in school too.

Tags: rave, microsoft
Categories: Development | Internet | Microsoft | Rave | SQL Server

One of the new things that happens in SQL Server 2008 is that it prevents saving table structure changes that require the table to be dropped and re-created. While this is a great feature to prevent accidents from occurring, on a developer machine it can be quite frustrating. This is the dialog you get when trying to make changes in a table design.

Unfortunately, this dialog doesn’t tell you where to turn this feature off! Clicking on the small “?” on the title bar does get you to a help page that tells you how to do it.

Anyway, the place to do it is Tools > Options > Designers > Table and Database Designers > Prevent saving changes that require table re-creation. Turn this option off and you will be able to save the tables again.

Tags: sql server, development, tips
Categories: SQL Server | Tips | Development

I'm right now at Redmond in the Microsoft HQ attending a SQL Server 2008 TPrep. There are a bunch of new things for developers, DBAs and Business Intelligence. One of the biggest changes in the BI space is in Reporting Services. There are a huge bunch of changes that allows you to create much more flexible & richer reports easier and faster. Here are some of them:

• A new Report Designer tool that is aimed at end / office users with a new interface with the Office 2007 Ribbon and other UI changes. This allows non-technical users to quickly create reports without delving into the technical stuff.
• A new data formatting control called the Tablix. This is something that designers the ability to use the best features of both the Table and Matrix controls.
• Better rendering support for Excel and CSV export types
• A new MS Word export rending option
• LOTS of new charting options - including:
  • Stepped Line, Range Area, Range Bar/Column, Funnel, Pie Callout, Pyramid, Polar, Error and Radar
  • Secondary X- and Y- axes
  • WYSIWYG chart design control (think Excel charts)
  • Multi Charts
  • Scale Breaks
  • Great new Gauge controls
• Dependency on IIS has been removed - the service uses HTTP.SYS directly

It's great to see the cool, new stuff in the Reporting Services. I'd have liked a couple of other stuff in it as well.

• An auto-format option for the Tablix would have been nice. After all the ASP.NET GridView has them and so do Word and Excel tables.
• An option to allow alternate row formatting for easier reading of reports
• Ability to deploy/install the new Report Designer as a ClickOnce application from the Report manager site
• The Web service endpoint for Reporting Services still remains as ReportServer2005! That's a little nutty I'd say. The name should have been ReportServer2008. Especially since there is a ReportServer2006 that is meant to connect to servers in SharePoint integrated mode. If backward compatibility was such a big deal it would have been trivial to create the ReportServer2008 endpoint and create a wrapper endpoint called ReportServer2005. Explaining this to clients is gonna feel a little stupid! :)

Tags: sql server
Categories: SQL Server