To SQL or not to SQL : SQL Server

SQL 2000 post SP4 Hotfix 8.00.2162

Mike Epprecht — Fri, 16 Dec 2005 00:45:00 GMT

A SQL Server 2000 Post SP4 hotfix has just been released to the public. Bringing the SQL Server 2000 version number to 8.00.2162

This hotfix is a cumulative hotfix containing:

Hotfixes that were built since SP4 shipped
Hotfixes that were done for SP3 but did not make it into SP4 (because of the cutoff date for fixes that made it into service pack SP4)

This build has undergone more testing than a typical hotfix build but not as much as say a service pack.

The build is available at the following download locations:

Ia64 version - http://www.microsoft.com/downloads/details.aspx?FamilyId=920707DE-AAF0-412F-8B26-1074E91E494D&displaylang=en

X86 x64 version - http://www.microsoft.com/downloads/details.aspx?FamilyId=4773BF7E-21AE-4F1E-AD48-6CA739E10217&displaylang=en

The following KB articles have been published to help answer questions about this hotfix build:

http://support.microsoft.com/?kbid=904660

http://support.microsoft.com/?kbid=894905

Microsoft SQL Server Management Studio Express - CTP - November 2005

Mike Epprecht — Wed, 09 Nov 2005 05:13:00 GMT

Microsoft have been working on a free management tool for SQL Server 2005 Express, and what they were working on during the betas was mostly tossed away in the July timeframe.

Now, the CTP for the new tool, Microsoft SQL Server Management Studio Express is available.

Something that looks and works like the full Microsoft SQL Server Management Studio is really the right choice.

http://www.microsoft.com/downloads/details.aspx?FamilyId=82AFBD59-57A4-455E-A2D6-1D4C98D40F6E&displaylang=en

Scalable shared databases are supported by SQL Server 2005

Mike Epprecht — Wed, 09 Nov 2005 05:04:00 GMT

Something that seems to have been added to SQL Server 2005 Enterprise Edition, very late in the development cycle, seems to be on of it's best features.

The ability to have one DB, located on SAN, available to multiple SQL Servers at the same time, as a read only DB is just incredible.

You can really scale out reporting, and might save you from having to flatten the data so that it is report friendly. Just throw more servers at the problem now. You need a bit of effort to update it, but it is really worth the while for those scenarios where data is loaded on a daily or weekly basis.

As long as your SAN can handle the throughput, it think it is a great feature.

The documentation in not in the SQL Server 2005 RTM BOL, but is available as a KB article: http://support.microsoft.com/?kbid=910378

Well done Microsoft!

SQL Server 2005 RTM Released to MSDN

Mike Epprecht — Fri, 28 Oct 2005 03:11:00 GMT

The files just went up for RTM SQL Server 2005 and Visual Studio 2005 on MSDN Subscribers Downloads.

A big 2747 MB download for SQL Server.

SQL Server 2005 row level security

Mike Epprecht — Mon, 10 Oct 2005 07:22:00 GMT

Ok, so Microsoft removed row level security from SQL Server 2005 early in the development cycle. Pity, but looks like it was just one of those trade-offs that needed to be made.

A bunch of guys from Microsoft Consulting has produced a paper on how to implement "Row and Cell" level security in SQL Server 2005.

"Implementing Row and Cell Level Security in Classified Databases Using SQL Server 2005" http://www.microsoft.com/technet/prodtechnol/sql/2005/multisec.mspx

I like the idea of keeping it simple, yet still powerful though roles. It is a bit of work, but compared to what we have in SQL Server 2000, it is a workable and maintainable solution.

Some basic rules have to be followed, but once everyone on the development team understand them, it gets very easy. Developing with security as a base, and doing most of the DB architecture up front in still a foreign concept for most developers, even those in corporates. With the added complexity of the new features in SQL Server 2005, you can really end up in trouble if the implementation is not though through first.

SQL Server 2005 - Database Mirroring off the cards for RTM.

Mike Epprecht — Fri, 16 Sep 2005 02:47:00 GMT

So, Database Mirroring will not be "on" in SQL Server 2005 when it releases.

From what I have read, it looks like it is not performing up to expectations. You can still enable it, but it's probably going to be one of those "we told you so" scenarios.

I do not doubt that it is robust and won't trash your data, but I do think it is a bit too slow for production use.

I just thought it was the June CTP that made it slow. I have not been able to crash it yet.

Paragraph 5.1.2 from the September CTP shows how to enable it

http://download.microsoft.com/download/9/7/e/97e4c6e8-18e6-4c61-b1df-bdf4a66b146b/ReadmeSQL2005.htm

The updated letter from Paul Flessner's really raises the point

http://www.microsoft.com/sql/2005/productinfo/letter.mspx

Pity, my presentation for this Wednesday at SQLCon 05 in Germany has just been blown out the water.

Windows Task Scheduler (a.k.a Task Scheduler) and SQL Server 2000/2005

Mike Epprecht — Sun, 28 Aug 2005 05:09:00 GMT

After the problems on the SQL Server 2000 cluster with the new hotfix installer, a case was opened with Microsoft Support engineer allocated to us (see article prior to this).

Well, after some more looking, SQL Server 2005 will use Windows Task Scheduler for setup on Clusters too.

OK, but Microsoft recommends that you disable Task Scheduler as a security precaution. Great.

According to the SQL Development team, the use of Task Scheduler is here to stay. In some respect I see where they are coming from as they can then use the "Run As" on the remote host to launch the process under the correct account.

Currently, if things fail, the task is left on the remote machine, but the directory the task refers to is deleted.

We have now opened a DCR (Change request) that Microsoft either documents this feature and process very well, including updating their security lockdown documentation, or make the hotfix installer/Setup clever enough to start the task scheduler on the remote hosts, run the processes required and shut it down again.

SQL Server 2000 post SP4 hotfixes don't install on all nodes of a cluster

Mike Epprecht — Wed, 24 Aug 2005 22:59:00 GMT

I recently installed a new Windows 2003, SQL Server 2000 cluster.

Then added SQL Server 2000 SP4 and applied hotfix 2040 (KB 899761 FIX: Not all memory is available when AWE is enabled on a computer that is running a 32-bit version of SQL Server 2000 SP4)

By chance I looked at the sqlservr.exe file and noticed it was still 2039 on all non-active nodes for the instance, whilst the node I installed the hotfix from was at 2040.

I looked at the SQL2000-KB8997618.00.2040-x86-ENU.log log file that 2040 creates.

3.781: SQL DLL: Successfully transferred payload to remote product instance target \\NodeB
3.781: SQL DLL: Successfully transferred payload to remote product instance target \\NodeB
3.797: SQL DLL: Successfully created new scheduled task for product instance target \\NodeB
3.828: SQL DLL: Error, failed to save new scheduled task for product instance target \\NodeB
3.828: SQL DLL: Error, failed to create scheduled task for product instance target \\NodeB
4.000: SQL DLL: Successfully removed remote folder for product instance target \\NodeB
4.000: SQL DLL: Error, remote process failed for product instance target NodeB
5.031: SQL DLL: All remote clustered nodes have completed patch installation
5.031: SQL DLL: Checking that at least one remote clustered node was successful
5.047: SQL DLL: Remote clustered node NodeB was successfully patched
5.047: SQL DLL: One or more remote clustered nodes were successfully patched

What?
On step 3.828 it fails, but then still claims on 5.047 to have checked that the hotfix was there.

Not the case, it failed and 2040 is not on the other nodes.

As you may nor may not know, Microsoft has changed the hotfix installer for SQL Server. What they did not tell us is that it now relies on Windows Task Scheduler to do it's work on the other nodes of the cluster.

What it boils down to is that if Windows Task Scheduler is disabled or not running on the remote nodes, the hotfix install on those nodes fails, but fails very silently.
Most corporates disable Windows Task Scheduler as it is a potential security risk.

So, before you apply any SQL Server 2000 post SP 4 hotfixes on a cluster, enable and start Windows Task Scheduler on all the nodes of the cluster (stop and disable them once you are finished before your security team come and get you).

See you at SQLCON 05

Mike Epprecht — Wed, 13 Jul 2005 03:52:00 GMT

I will be presenting at SQLCON 05 in Mainz, Germany, 20 and 21 September 2005.

2 sessions, “SQL Server 2005 High Availability“ and “SQL Server 2005 in a Secure Financial Environment“.

The first one is really focusing on Clustering, Mirroring and Log Shipping and the 2nd one on how to setup SQL Server 2005 so that it meets the stringent laws I need to adhere to...Swiss Banking Law. If I can adhere to those laws, it is good enough for anyone.

Lots of good sessions.....so hope to see you there.

The hoxfix is here for AWE!

Mike Epprecht — Sun, 19 Jun 2005 20:57:00 GMT

At last! The hotfix for SQL Server 2000's 32 bit SP4 AWE issue has been released.

http://support.microsoft.com/?kbid=899761

It takes the build to 2040.

Microsoft SQL Server 2000 - 8.00.2040 (Intel X86)
May 13 2005 18:33:17
Copyright (c) 1988-2003 Microsoft Corporation

I like the new installer. It will do multiple instances on a server all together.

The week of 7 November 2005

Mike Epprecht — Wed, 08 Jun 2005 01:55:00 GMT

Yes, it is now public, the week of 7th November 2005 will be the launch of SQL Server 2005 (with Visual Studio .NET 2005).

5 Months of beta testing......and with the June 2005 CTP of SQL Server 2005 on MSDN and Betaplace, if we don't find the bugs now....we have to live with them in production.

http://www.microsoft.com/presspass/misc/06-07flessner-teched05.mspx

Will you be at SQL PASS Community Summit in September?

Mike Epprecht — Mon, 16 May 2005 04:05:00 GMT

http://www.sqlpass.org/events/summit05/index.cfm

Make sure you get to THE SQL Server conference of the year.

Even CELKO will be there, a very rare occurrence, presenting a session. If you don't know who CELKO is, Google for his name in the newsgroups. He has a very, very strong opinion on ANSI standards, and does tell users who shame the standards where to go.

The cycle of SQL Server 2005

Mike Epprecht — Mon, 16 May 2005 03:59:00 GMT

PASS European Conference last week in Munich brought some clarity.

The jist of the release cycle is that Microosft can not pass up Tech Ed in June to do a release of a CTP. And after that, there will be another CTP, and then, if everything goes right, RTM. CTP's are generally on a 2 month cycle, so June plus 4 months?

Time will only tell.

Time to write a book

Mike Epprecht — Mon, 16 May 2005 03:48:00 GMT

I have been wondering, should I write a book on SQL Server 2005?

Not one of those that go on-and-on-and-on about how to learn to read 1'200 pages, with a bit of SQL Server thrown in to make the title valid..

Well, Jens Süßmeyer and I have begun the long road to wrist injuries and late nights. We seem to have the same ideas, with the plain writing, and honesty, as shown at the SQL PASS 2005 European Conference keynote by Gert Drapers.

Topic? Do we need one? Well yes. Think of it as being a lesson for DBA's on how to embrace the new SQL Server 2005. Too many things have changed to sit back, and it does not help to call SQL Server 2005 names like CLR Server 2005.

SQL Server 2000 SP4 problems with AWE

Mike Epprecht — Sat, 14 May 2005 07:40:00 GMT

Looks to be a bit of a wild one.

SQL Server 2000 SP4 introduced a bug where only 1/2 the RAM can be accessed by SQL Server when AWE is enabled.

So, it you have 16GB, SQL Server will only use 8GB.

There is a Microsoft KB article # 899761 that describes the problem.

It's advice is to un-install SP4 and call PSS. A Hotfix for this is in progress.

The SP4 download site has now a warning about not installing SP4 on systems with AWE enabled. http://www.microsoft.com/sql/downloads/2000/sp4.asp

Now I wonder how SP4 got out the door with that problem!

Update 1: KB link is now available: http://support.microsoft.com/?kbid=899761

Update 2 (16 May): The hotfix is in testing, and a release is expected in about 7 days. For the moment, it will be a hotfix and not a SP re-release.

SQL Server 2000 SP4

Mike Epprecht — Sat, 07 May 2005 04:11:00 GMT

Service Pack 4 for SQL Server 2000 is now available.

It has been a long time in beta, but before you install it (especially on production), read the readme that comes with it. There are changes that may affect the way some of your applications behave.

If you used undocumented commands, Microsoft may have changed them or the security has been tightened. As they are un-documented, they will not be mentioned as having changed in the readme. They are undocumented for a reason, so Microsoft can change them at will. Read and Writing to/from the Registry is affected.

Get it here http://www.microsoft.com/sql/downloads/2000/sp4.asp

32 bit, 64 bit Itanium, MSDE and Analysis Server

SELECT @@VERSION returns 8.00.2039

A bit on x64 support:

With Microsoft Windows Server 2003 x64 Edition, SP4 provides support for 32-bit SQL Server applications on 64-bit architectures using the Windows on Windows emulator (WOW64). For more information on specific 64-bit platforms supported, see the Windows Server 2003 x64 Editions Product Overview.

SQL Server 32-bit applications, including SQL Server client tools, are still not supported on WOW64 for IA64. Also, currently 32-bit Reporting Services is not supported to run on WOW64 on IA64 and x64 platforms running Windows Server 2003 x64 Edition.

SQL Server 2000 on x64

Mike Epprecht — Wed, 27 Apr 2005 04:33:00 GMT

The question has come up a few times in the newsgroups.

Does SQL Server 2000 run on x64?

SQL Serrver 2005 supports it already, but SQL Server 2000 does not.

Service Pack 4 for SQL Server 2000 was mooted to support it.

The answer I got from Microsoft today: “Microsoft is still finalizing this.” So, once they have made a decision, there will be a public statement on this.

April CTP is 'feature complete'

Mike Epprecht — Wed, 20 Apr 2005 15:16:00 GMT

It is official that what is in the April CTP, is what will, with a 99.999% probability, be in the RTM of SQL Server 2005.

Microsoft is saying that it is 'feature complete', so from here onwards, it is bug fixes and performance enhancements only.

The letter from Paul Flessner, Senior Vice President of the Server Applications at Microsoft: http://www.microsoft.com/sql/2005/productinfo/letter.asp

Since I has such a nice evening last night fighting installation problems, I will only really get time tonight to dig into the product and see what is 'missing'. I did notice that the SQL Server Management Studio did behave better and seemed to have more features.

The version number of the April CTP (internally referred to as IDW14) is v9.00.1116.08

During setup, I did notice that if Integrated Security was selected, no default password for the sa account was required. This changed from IDW 13 where it forced you to enter one. I think it is a bug so I will file it as one.

Also, once the client tools were installed, it is not possible to add the Sample Databases and Sample Code by re-running setup. So, if you forget it, you can't have it. Bugged it.

So I get to file 3 bugs for 6 hours work.......success.

UPDATE:

The sa issue is a bug and will be fixed.

The client tools issue, well, once the client tools are installed, you have to maintain them though Add/Remove Programs, then you can add these additional options. Not too clear during setup, but one for the books as I think it will be a popular question around RTM.

OK, so I got to file 2 bugs. Still good.

April CTP install is failing

Mike Epprecht — Wed, 20 Apr 2005 04:54:00 GMT

Well, the SQL Server 2005 April CTP is failing on installing the database components.

In the Beta newsgroups, more people are popping up with the same issue, mostly Express Editions.

Some of the error:

Product : SQL Server Database Services
Error : SQL Server Setup could not connect to the database service
for server configuration. The error was: [Microsoft][SQL Native Client]SQL
Network Interfaces: Error Locating Server/Instance Specified [xFFFFFFFF].
Refer to server error logs and setup logs for more information. For details
on how to view setup logs, see "How to View Setup Log Files" in SQL Server
Books Online.

The SQL Error log shows a clean startup and a clean shutdown at request from the installer.
The instance is running at the time of the failure and I have verified it is running in Services and process is there in Task Manager..

Looking at
*_SQL.log, it shows "Login timeout expired" as being the reason.

I will persist and get the issue......

UPDATE:

I seem to have nailed it.

When the dialogue pops up with the connection failure message, I ran "SQLServerManager.msc", the SQL Server Configuration Manager, and enabled for the SQL Native Client Configuration, under Client Protocols, Named Pipes and Shared Memory (TCP/IP is enabled by default).

Once I did this, I could press retry and the installation completed successfully.

sqlcmd and sqlcmd90 both retuned the same errors as the installation routine when they could no connect.

UPDATE 2:

"SQLServerManager.msc", the SQL Server Configuration Manager, is not installed by the time the failure occurs. What I did was to first install the client tools only, then re-run setup and install the Database Engine.

UPDATE 3:

Some people have hinted that this problem might be caused by SNAC importing the SQL Server 2000 client configuration settings. For me, TCP/IP and Named Pipes was enabled, and SNAC only chose to enable TCP/IP on the install. I don't think this is the cause.

SQL Server 2005 Beta 2, April CTP Ships

Mike Epprecht — Tue, 19 Apr 2005 05:51:00 GMT

So, the long awaited April CTP for SQL Server 2005 went up on MSDN downloads today.

VS.NET 2005 Beta 2 went up on Friday.

The SQL Server install requires Windows Installer v3.0 (Windows XP SP2 and Windows Server 2003 SP1 have it already, other platforms do not). It is documented in the ReadMe file, and where to get it too.

Download is in progress...hope to have it by the morning.