Welcome to SqlAdvice Sign in | Join | Help

In SQL Server 2000, if you use Enterprise Mangler to script your tables, you need to watch out that your constraints don't get turned off. For example, here's a snippet generated from the Northwind.dbo.Employees table (just using the right click > All Tasks > Generate SQL Script wizard).

ALTER TABLE [dbo].[Employees] WITH NOCHECK ADD 
CONSTRAINT [PK_Employees] PRIMARY KEY CLUSTERED
(
[EmployeeID]
) ON [PRIMARY]
GO

I find that that little injection of 'goodness' bites people all the time with SQL Server - so much so that I consider it a bug. Well, actually, I just consider it a bug merely because it's a horrible practice. (I can that you might want to create the table this way - so that you could load BAD data into it, but that should never be the default). MS obviously felt similarly about the problem, because it's fixed in SQL Server 2005. Actually, SQL Server 2005 generates the CONSTRAINTS with the WITH NOCHECK option in place, but then turns the constraints on as part of the object's definition - as follows:

ALTER TABLE [dbo].[Employees]  WITH NOCHECK ADD 
CONSTRAINT [CK_Birthdate] CHECK (([BirthDate] < getdate()))
GO
ALTER TABLE [dbo].[Employees] CHECK CONSTRAINT [CK_Birthdate]

Problem solved. (You may also notice that it creates PKs without the WITH NOCHECK clause - something that's in stark contrast to how SS 2000 did it...) Oh, and if you're curious to find out if you've got useless constraints in place, just run the following script which will tell you which, if any, constraints are not currently turned on:

SELECT
OBJECT_NAME(id) [Table],
OBJECT_NAME(constid) [Constraint]
FROM
sysconstraints
WHERE
OBJECTPROPERTY(constid, 'CnstIsDisabled') = 1
Sponsor
14 Comments
Filed under: ,
I've just stumbled upon some very good news, in the form of two books:
1) Inside Microsoft SQL Server 2005: The Storage Engine - Kalen Delaney
2) Inside Microsoft SQL Server 2005: T-SQL Programming -Itzik Ben-Gan

Kalen Delany's Inside Microsoft SQL Server 2000 is the best SQL Server book that I own - the only thing I would change about it: make it twice as big. My only hope is that that's what's going on here with these two tomes.

The only bad news associated with these books is that the first won't be published until Sept, and the second won't be published until May. (But hey, quality takes time).

Sponsor
0 Comments
Filed under:

Dan Wahlin just posted a message to one of the lists on aspadvice about some interesting behavior that he noticed in VS 2005 - executing a sproc (against SQL Server 2000) from within VS 2005 wasn't returning the entire result set that he was expecting. He could execute the sproc in QA, and it worked fine. Executing in VS 2005 would show him how many rows should be there (i.e. x rows affected), but wouldn't show the rows.

Intrigued, I fired up my own little test. The first sproc I tested worked perfectly - everything came back as expected. Then I took Dan's sproc, modified it a little bit to pull data from the local systables as 'filler' instead of pulling from the tables Dan was pulling from and tested it. Sure enough - I saw the behavior he posted.

A quick scan of the differences instantly showed that my working sproc did a SET NOCOUNT ON - his didn't. A quick tweak, and ensuing test, and his sproc was working as expected. SET NOCOUNT ON is used to tell SQL Server to be less chatty - i.e. stop telling the client the step by step, and blow by blow execution of every line of code. It also has the added benefit of allowing multiple result sets to be returned to clients that tend to BAIL as soon as they a result returned from the server - such as ADO 2.x and apparently the client used by VS 2005.

Sponsor
0 Comments
Filed under:

So, your application was a success. You're getting oodles and oodles of traffic. Only, it's building up faster than you had anticipated, and after a bit of calculation, you've determined that if the present trends persist, you'll have 24 TB of data within 1 year. Only, you really don't need data > 3 months old in the main OLTP tables.

Question: How do you archive, or nuke that older data without impacting performance? Trying to delete vast sums of data all at once will lock your table, which will block threads, which will stall your application, and cause you all sorts of grief.

Enter the 'nibbling' delete - a trick I came up with to archive oodles (0.3-10 million rows/day) of 'traffic' data on a very busy server. In my situation, a clustered index didn't exist along the 'date' domain of the entity in question, so trying to handle that many locks on a row-level basis (for a ranged, date-based, delete) would have taken the server to its knees (well, would have hiccuped it for a few seconds at least - and I couldn't afford ANY interuptions).

The Main Concept: Sql Server can, and will, do a fine job of just getting rid of a few rows at a time. It's when you try to remove 40k or 4 million at a gulp that it gets cranky. What we need is a simple way to 'spoon feed' it a few rows to nibble on at a time. When it's done with those rows, it can have a few more. Happily, SQL Server provides a way to set up your own 'nibbling' operation, with the use of some simple logic and the assistance of the WAITFOR operator. As follows:

DECLARE @count int
SET @count = 2000

SET ROWCOUNT 2000

WHILE @count = 2000 BEGIN

DELETE FROM myBigTable
WHERE someCondition = true

SELECT @count = @@ROWCOUNT
WAITFOR DELAY '000:00:00.200'

END

Simple huh? The where clause can be as simple as something like WHERE recordedDate > @30DaysAgo. However, you may find that spooling all of those results is too expensive, so you could do something like:

DECLARE @count int
SET @count = 2000

SET ROWCOUNT 2000

WHILE @count = 2000 BEGIN

DELETE FROM myBigTable
WHERE targetID IN
(SELECT TOP 2000 targetID
FROM myBigTable WITH(NOLOCK)
WHERE something = somethingElse)

SELECT @count = @@ROWCOUNT
WAITFOR DELAY '000:00:00.200'

END

And that's it. Forcing SQL Server to limit the number of 'row operations' (via setting the ROWCOUNT) stops it from gobbling up oodles and oodles of locks, but allows SQL Server to just keep churning and churning until all of the targeted rows are deleted. Giving SQL Server 2000 .2 seconds to 'think' between operation will give it enough time to make sure that any operations that queue against your nibbling delete will pass through without much of a wait (on a heavily trafficed system some operations WILL queue behind yours, but the calling application can't really tell the difference as they never take more than .4 seconds to complete.

Of course, in SQL Server 2005, the SET ROWCOUNT operator has been retired. Instead you'll just need to the TOP operator, which now accepts variables:

DECLARE @target int
SET @target = 2000
DECLARE @count int
SET @count = 2000

WHILE @count = 2000 BEGIN

DELETE FROM myBigTable
WHERE targetID IN
(SELECT TOP @target targetID
FROM myBigTable WITH(NOLOCK)
WHERE something = somethingElse)

SELECT @count = @@ROWCOUNT
WAITFOR DELAY '000:00:00.200'

END

And frankly, that's a bit cleaner anyhow (i.e. WHERE mainTable.id IN (SELECT TOP # of ids from the mainTable)).

I'll post a bit more about this later, specifically on some other tips and tricks, and ways to handle tables with FK constraints (specifically with CASCADES enabled).

Sponsor

Clustering is just plain fun. Being able walk around with a clustered system on my laptop (and trusty, portable, external USB HD sidekick) is a total hoot.

To get an entire cluster working on VMWare 5.0 can be a bit of a bear though, because there's really a paucity of documentation out there that explains how to do it. The biggest challenge, of course, being how to simulate shared drive resources usable by the cluster (i.e. how do you simulate something like fiber attached to HBAs, or a SAN, etc?).

Happily VMWare does support simulating shared IO/drive resources though you'll need access to some powerful vodoo.

Build instructions, including the Vodoo, are listed below:

1) Build a Base Machine. Build a base Machine which you can use as an image for your domain controller, and two (or more) Custered SQL Servers. (Yeah, I went with the option of making the DC a non-clustered resource. If you want to, you could just dcpromo both of your nodes and try it that way....). To do this:

  • Just create a brand new VMWare server - specifying Windows Server 2003 Enterprise Edition (or better) as the guest OS.
  • Give it about 4 GB of non-fixed disk space.
  • Some Ram,
  • SCSI drives
  • and a bunch of clicks on the Next button.
  • Then ensure that the Windows 2003 Enterprise Server Installation Media is either captured as an .ISO for your VM, or slap the disk into your CD/DVD-ROM drive.
  • Start the VPC, and install Windows 2003 Enterprise..
  • During installation, give the box a bland name, something like WIN2K3BASE, or whatever. (This box won't really be a part of your cluster - you're just paving it to use as the 'base' for your other boxes.)
  • Once the Install is complete. Patch the box.
  • Then drop the new SysPrep Tool on it from Microsoft: http://support.microsoft.com/?kbid=838080
  • Extract the files, and then just run sysprep.exe.
  • It will warn you that it can mess up boxes - click yes, you're TRYING to mess up your box ;)
  • Select Reseal, and click OK. In case you don't know what you're doing, and somehow just managed to put SysPrep on your workstation because you thought the NAME was cool, Windows warns you that you are about to.... PREP your machine. Click Yes (you're mature enough).
  • The VM powers down.
  • Once it powers down, from the VMWare menu, select the VM's Settings. Click into the Options Tab, and select Advanced. Then put a check mark in the Enable Template Mode. OK your way out of there.
  • From the VM menu, take a new snapshot. Give it a happy, cuddly name, like "momma," cuz this is what you'll run home to if everything ends up barfing on you later on.
  • Now, from the VM menu, select Clone. Make sure that you create a full clone, and not just a linked or forked version of the server (you want a FULL copy/clone).
  • Repeat the cloning process until you have 3 'boxes': A Domain Controller, SQLServerNode1 and SQLServerNode2. (I prefer Simpson's characters for server names, and Marge's sisters work out perfectly; Patty and Selma are names that are uniquely suited to clustering <g>).

2) Build a Domain Controller.

  • Fire up the box that will be your DC, run through the happy, abbreviated setup wizard and give your box a network name you can live with -- Quimby is pretty authoritative for a DC.
  • Once it reboots, give it a static IP address. I prefer to just bind my boxes to the VMnet2 network. And then do something imaginative like set the DC to 192.168.235.1, with 255.255.255.0 as the subnet. Add a gateway if you've got one... etc. (though, if you're like me, you don't care much about your clustered SQL Server 'domain' being able to chat with external boxes).
  • Then, either run DC Promo on it, or use the Add Server Role do-hicky from manage your server.
  • Just 'Next' your way through the wizard, stopping only to tell it to take care of the DNS (unless you're a glutton for punishment).

3) Cluster and SQL Server Service Accounts. Create a user account to use for the cluster and for SQL Server

  • While you are still on the DC, go to Start | Administrative Tools | Active Directory Computers and Users and create a new user in your domain for the cluster account.
  • You can also create another user for the SQL Server account (which is a good idea in the real world).
  • You'll want to make sure that both of these users don't allow the user to set the password, that the password never expires, and OF COURSE, uncheck that dumb: User must change password at next logon.

4) Build Out your SQL Server Nodes

  • For each of your SQL Server Nodes, you'll want to add a new NIC from the VM Hardware manager (just select Settings on the VM, and the use the Add button to launch the Add Hardware wizard).
  • Bind the newly added NICs to a custom VMWare Network (I keep all of my VNICs bound to the VMnet2 network).
  • Boot server1. Once it's booted, navigate to the network connections section of the control panel in Windows Explorer. Right click and rename your Area Connections to External and Heartbeat.
  • Change the IP addy for External to something fixed on your network (like 192.168.235.11 or .12 for server2) with the appropriate subnet mask (255.255.255.0). Then specify the fixed IP of your Domain Controller as the Preferred DNS IP Address (i.e. 192.168.235.1).
  • Now change the IP Address for your HeartBeat NIC. Set it to something like 10.1.1.1 (or 10.1.1.2 for Server2). Leave the Gateway empty. Leave DNS blank. Click the Advanced button, and from the WINS tab, click Disable netbios over tcpip.
  • Do the same for Server2 (using different IP addresses, of course).
  • Add both servers to your domain (Right Click My Computer | Properties, and then from the Network Identification tab click Change. Specify the credentials you provided when you created your Domain Controller in the previous steps as the credentials you need to add the box to the domain.)
  • Joining to the Domain switched your External NICS to DHCP. Jump back in there, and slap them back to the way you had them before. If you get grumped at by Windows (telling you that there may be a collision, just ignore it (I think you have to click No to make the nag screen go away)).
  • Check out your IP Addresses by running ipconfig /all from the command line -- just to make sure. Also make sure that you can see that the heartbeat nic has netbios turned off.
  • Power off both boxes once they've had their nics configured and have been added to the domain.
  • Now would be a really good time to snapshot both boxes incase you do something dumb down the road.

5) Configure Shared Disk Resources. Now comes the juicy part. The way to spoof your virtual machines into believing that they have access to shared disk resources. In my quest for clustering I found two resources on this, one was hideously outdated - but contained enough theory to help me out, The other was for version 4.5x and was missing one critical piece of data that is apparently now needed in VMWare Workstation 5.0. The key concepts here are that you just need to create SCSI controllers on both machines, and then provide directives that tell VMWare NOT to lock the disks when they are connected. This lets machines share disks, as long as all of the SCSI connection info is configured correctly. From there you just need to, obviously, make sure that the disks aren't busy trying to dynamically allocate size; i.e. the disks have to be fixed or each VM will see a different size, state, etc. 

The first thing you need to do is create some drives that you'll hook up to your machines. The best way to do this is to just create them with the wizard by 'adding' them to one of your machines, and then immediately removing them. Think of it as a virtual-hard-drive-egg-laying-chicken (or just think of it as a way to make virtual hard drives, if that's easier). To Proceed:

  • Open up one of your SQL Servers and Select VM | Settings from the menu. To add the drives just click Add and use the Wizard.
  • The first drive will be your Quorum drive, and just needs to be a few hundred MB (200 MB will work fine - or .2GB).
  • The wizard steps are as follows: Create a new virtual disk. Next. SCSI. Next. Disk Size = .x GB Then ensure that Allocate all disk space now is checked. Click Next. Browse out and drop the disks in a directory called SharedDisks (or something). And click the Advanced button. Make sure that Independent is checked. Then click Finish.
  • Make sure you create two disks (your Quorum drive, and then a Resource drive (or more)).
  • Then select each drive, and Remove it in the Hardware management thingy. We just needed to MAKE Hard Drives, we don't want to add them just now. (You'll add them by hand to the machines in a second.)

6) Attaching Shared Disks. Add your virtual shared drives to the boxes by hand. Now that the drives are sized and created, it's time to head to the virtual server rack and hook up some virtual SCSI controllers.

  • Navigate to the directories where your Virtual Machines are kept, and for Server 1 open the .vmx file in NotePad.
  • First add some instructions for disk control, and to make sure that the VM won't attempt to lock the drives it connects to:
    # Shared Disk Config Info:
    diskLib.dataCacheMaxSize = " 0"
    diskLib.dataCacheMaxReadAheadSize = " 0"
    diskLib.dataCacheMinReadAheadSize = "0" 
    diskLib.dataCachePageSize = "4096"
    diskLib.maxUnsyncedWrites = "0" 
    disk.locking = "FALSE"
    
  • Then add a new SCSI controller:
    scsi1.present = "TRUE"
    scsi1.virtualDev = "lsilogic"
    scsi1.sharedBus = "virtua" 
    
  • Once that's done, add your Quorum Drive (making sure to specify that the drive itself uses the lsilogic bus (this was the big missing component between Karl's post for 4.5x and 5.0. I found this out by trial and error):
    scsi1:1.present = "TRUE"
    scsi1:1.fileName = "\Quorum.vmdk"
    scsi1:1.redo = ""
    scsi1:1.mode = "independent-persistent"
    scsi1:1.deviceType = "disk"
    scsi1:1.virtualDev = "lsilogic"
    
  • Once the first controller and drive is added, just add the second SCSI controller and disk (making sure to change your paths, etc.:
    scsi1:2.present = "TRUE"
    scsi1:2.fileName = "\Resource.vmdk"
    scsi1:2.virtualDev = "lsilogic"
    scsi1:2.redo = ""
    scsi1:2.mode = "independent-persistent"
    scsi1:2.deviceType = "disk"
    
  • The entire 'snippet' to copy/paste is here:
    # Shared Disk Config Info:
    diskLib.dataCacheMaxSize = "0"
    diskLib.dataCacheMaxReadAheadSize = "0"
    diskLib.dataCacheMinReadAheadSize = "0"
    diskLib.dataCachePageSize = "4096"
    diskLib.maxUnsyncedWrites = "0"
    disk.locking = "FALSE"
    
    scsi1.present = "TRUE"
    scsi1.virtualDev = "lsilogic"
    scsi1.sharedBus = "virtual"
    scsi1:1.present = "TRUE"
    scsi1:1.fileName = "\Quorum.vmdk"
    scsi1:1.redo = ""
    scsi1:1.mode = "independent-persistent"
    scsi1:1.deviceType = "disk"
    scsi1:1.virtualDev = "lsilogic"
    
    scsi1:2.present = "TRUE"
    scsi1:2.fileName = "\Resource.vmdk"
    scsi1:2.virtualDev = "lsilogic"
    scsi1:2.redo = ""
    scsi1:2.mode = "independent-persistent"
    scsi1:2.deviceType = "disk"
    
  • Make sure, of course, that you specify the full path to your shared Drives directory (i.e. M:\VirtualMachines\Shared Drives\).

And now you've got shared drive resources! If you're a geek like me, you're pumped.

7) Ensure Drive connectivity from both Servers.

  • Power down BOTH of your server nodes.
  • Boot up Server 1.
  • Right click My Computer | Manage. Go to the Disk Management node.
  • You should see your two new drives available. (And the Welcome and convert wizard will probably start up.)
  • Make sure both disks are Basic Volumes (Dynamic disks == bad). Quick Format them and bind them to Drive Letters (I like Q and R (quorum and resource) myself).
  • Test that you can access the drives from within Windows. (Try creating a .txt file and fill it with gibberish - then move it to the other drive, etc.)
  • Once everything is peachy, shut the box down.
  • Then boot Server 2. From the Disk Managemt node in the Management Console, you should see both disks. Just 'change' their drive letters (you'll have to add them AFTER clicking change...). Give them the same drive letters that you gave them for Server 1.
  • Test read/write functionality.
  • Shut down the Server.

8) Time to start Clustering. It's also probably a good time to take a snapshot about now (with both boxes shut down).

  • Fire up Server 1. Once it finishes booting, go to Start | Administrative Tools | Cluster Administrator.
  • Select create a new cluster from the dropdown.
  • Walk through the wizard which scans your configuration, makes sure that shareable resources exist, etc.
  • Specify a name for the virtual/clustered server, as well as an IP. 192.168.235.3 works well, and Server3 works well as a name if you are going the bland route. (Note that the cluster itself ends up being represented as a virtual machine on the network, with an IP Address, a name, and 'resources' at its disposal. If you put SQL Server (or Exchange for that matter) then that 'server' will have its own name and IP address (and resources) in addition to the name and IP of the cluster. (In this manner, a clustered SQL Server with 2 nodes consumes 6 IP Addresses, and 4 Network names: 2 HeartBeat addresses (on a private networks). 2 IP addresses for use by the servers (upon which the cluster is built) along with 2 network names, and 1 name and IP for the cluster, as well as 1 name and IP for the Clustered SQL Server (or virtual server as it is called).
  • When it comes time specify an account for the cluster service to run under, use the account you created up in step 3.
  • At the final, summary, page of the wizard, there's a Quorum button. It should be its own screen, but it's just a button squirreled away on the very last page. Pay attention for it. Click it, and make  sure that it is trying to use your Quorum drive for the Quorum drive (it's NOT smart enough to figure it out on its own).
  • Once the wizard is complete, the cluster service installs.
  • Once that's complete, bring Server 2 online. You can then either open the Cluster Administrator on Server 2 and Add Server 2 to an existing node, or return to Server 1 (now also called Server 3 - or the Active node in the cluster) and Add a new server to the Node (Server 2). The wizard is pretty similar, only there aren't as many steps.

Once the second wizard is done, you've successfully created a cluster. Woot! If you're a true geek, you'll fail it over a few times just to see it in action (go to the Groups node in Cluster Admin and right click the Cluster Group, then select Move Group (i.e. move the resource group to another cluster)). Note too, that as you do this, the Group 0 resource (your remaining shared HD) stays with the original node (though you can move it too).

9) SQL Pre Configuration

  • You'll want to install MSDTC on both servers in the cluster. Move the Cluster Resources to Server1. (Fail the resources back to Server1 if you need to.)
  • Start | Control Panel | Add/Remove Programs | Add/Remove Windows Components.
  • Select Application Server, and then click Details. Then select Enable network DTC access and click OK (boy is this tons easier than configuring it for Windows 2000) a few times to load in MSDTC.
  • Once Server1 is configured, fail over to Server 2, and do the same.

10) Install SQL Server

  • You'll want NEED to copy the contents of your SQL Server 2000 Enterprise Installation CD onto the local harddrive for Server 1. (Otherwise you'll most likely encounter a read error during the middle of installation. Recovery at this point is just UGLY.)
  • Make sure cluster resources are on Server1. (Otherwise your installation won't get far...)
  • Change the name of the Group 0 resource to SQL Server Resource.
  • Run the autorun.exe from your VM's hard drive. (i.e. start the 'CD' up from your Hard Drive).
  • From the splash screen select SQL Server 2000 Components, and the pick Install Database Server.
  • Walk your way through the wizard. You'll be creating a Virtual SQL Server. And you'll need to provide a network name for it. SQLServer is a good name if you're going the bland route, otherwise it has to either be Marge, or Barney <g>. You'll also need to specify an IP Address as well. When you do so, make sure you are binding the IP address to the External NIC, not to the heartbeat NIC.
  • Finish up the installation as needed (putting your database files on the R Drive).

When Installation completes, you'll have a watered down, not-terribly-secured BUT clustered SQL Server instance in a completely virtual environment. (You may then want to set dependencies and other things on cluster resources in the SQL Server Resource Group... check out the Patterns and Practices Library for details (though remember this info is old and for Windows 2000 - though it's not that different).

 

Sponsor
155 Comments
Filed under:

One thing I forgot to cover in my previous 'cheet sheet' post of MSDE functionality was any mention of the price.

I bring that up because it bit me in the hiney today. On aspadvice I said that MSDE wasn't free, only free-distributable by owners of certain products (like Visual Studio, etc.) BOY was I wrong. Only, no I wasn't. Which just goes to confirm that MSDE is probably one of the most confusing products in existance.

As of this posting, the MSDE homepage said:
"Microsoft SQL Server 2000 Desktop Engine (MSDE 2000) is the free, redistributable version of SQL Server that's ideal for client applications that require an embedded database, new developers learning how to build data-driven applications, and Web sites serving up to 25 concurrent users."
Furthermore, the Appropriate Use FAQ made it clear that the application IS free:
Q. Can I obtain MSDE for use with my applications without purchasing any Microsoft products or services?  
A. Yes. MSDE is available for
download at no charge.

However, there are a few quick twists to the whole 'free' thing. And they're WEIRD twists.

The Appropriate Use FAQ has a number of questions (who would have thought?). Summarized, they effectively paint this portrait:

  1. MSDE is free to download and use.
  2. You can even redistribute it freely if you are licensed (licensing appears to be automatic if you have licenses of logical applications such as Visual Studio, Office Developer Edition, SQL Server, etc.). You can also redistribute if you register.
  3. If you somehow get a distributed copy of MSDE with an application that you deploy, you are only allowed to use that MSDE copy as intended, and only, apparently, for the app that distributed it.
  4. Here's the clincher: If you want to distribute MSDE within your organization, you must own a product such as MSDN, SQL Server, Visual Studio, or Visual Fox Pro. (But if you can place a copy of MSDE on any client that has MS Access on it.)

See the confusion? You can download it and use it free. But you can't distribute it in your organization, unless you have licensed products. In other words, let's say you just have developers using NotePad (a licensed MS product, but not in the approved list <g>), and one copy of MS Access on Computer A. Can you put a copy of MSDE on Computer B? I don't think so. At least you can't deploy to it. (But apparently you could download it and use it free on computer B?)

*scratches head*

Sponsor

I've got a new project coming up that Involves SQL Server 2005 reporting services. I've got a good handle on the basics, but the project will be on a tight deadline, and I'd like to have as much 'backup' as possible, so I went out to Amazon.com to find some books that may prove handy.

To be honest, projects are always a great excuse to buy books in my mind, only this time I really want these books here ASAP. The four books I found on Amazon.com were $114. Shipping, 2 Day, was going to be $25 or so extra - only there was something goofy flagged, telling me that one of my books was going to hold up the order (only it showed that ALL 4 books would ship on July 1st. (and grouped them into two groups)). I scratched my head, and chose the option to ship them as they became ready. Shipping went up to $65. The last thing I wanted was to spend that much on shipping, but I also WANTED the books right away.

So I went to BookPool.com, and within a few minutes found all of the books. They were all in stock, and the books with 2nd Day Air (the fastest option available) came to $117. That's right, $3 more than JUST the price of the books on Amazon.com, and the price included 2nd Day Shipping.

I just checked, the books are on their way. I just KNOW that had I ordered from Amazon stuff would still be sitting on a shelf somewhere...

BookPool.com roxors.

Sponsor
1 Comments
Filed under: ,

Microsoft's Paul Flessner just sent out an email to all of the official Yukon Beta Testers: SQL Server 2005 will ship the week of November 7, 2005.

Very cool news. One of my favorite new features: The Oracle Conversion Wizard... muhahahah.

Sponsor
0 Comments
Filed under: ,

Following up on my previous MSDE Facts post, I wanted to provide the same coverage for SQL Server 2000 Personal, because it frequently gets confused, in many circles, with MSDE -- probably because it, like MSDE, is capable of being installed on non-server OSes, and is also susceptible to the Workload Governor. But, it does have some different functionality, and it also isn't free like MSDE is.

P H Y S I C A L   C A P A C I T I E S / L I M I T A T I O N S
(NOTE: These capacities are the exact same as those of MSDE)

Max Supported Processors: 2 (all applicable OSes, except Win 98/XP which only allows 1 proc)
Total Supported RAM: 2 GB (all applicable OSes)
Max Database Size: 2 GB
Max Number of Connections: 32,767 (just like non MSDE versions)
Max Number of Databases: 32,767 (just like non MSDE versions)
Max Number of Files per Database: 32,767 (ditto)
Max Number of Objects Per Database: 2,147,483,647 (ditto)

F U N C T I O N A L   L I M I T A T I O N S
Allowed: Multiple Instances of MSDE per physical box (up to 16 instances, just like 'normal' SQL Server), GUI Tools, Full Text Search (except on Win98, no idea about XP), SQL Mail.
Not Supported: Failover Clustering, Log Shipping, Parallel DBCC, Parallel Create Index, Enhanced Read Ahead and Scan, Indexed Views, Federated Databases, and SAN Support.

R E P L I C A T I O N
SQL Server Personal Edition behaves just like MSDE, it can participate in all forms of Replication -- but, like MSDE, can only be a subscriber in Transactional Replication. It's also subject to the same requirement for CALS, so make sure you address that if needed. 

A N A L Y S I S   S E R V I C E S   F U N C T I O N A L I T Y
Unlike MSDE, SQL Server Personal Edition supports SOME Analysis Services Functionality, including: Analysis Service, Custom Rollups, Actions, and Data Mining (all of the features that STANDARD SQL Server supports).

O T H E R   F U N C T I O N A L I T Y
SQL Server Personal Edition also fully supports: Data Transformation Services, Data Mining, and English Query.

Q U E R Y   G O V E R N O R
Like MSDE, SQL Server 2000 Personal Edition is subject to the Workload Governor, which means that it too is limited to 8 simulataneous batches/operations.

U P   N E X T :
The Workload Governor: How it works, and how to tell when you've outgrown MSDE.

Sponsor
1 Comments
Filed under:

Despite how great MSDE is, I find myself constantly forgetting just what exactly its limitations are. So, without further ado, MSDE Facts:

N A M E
When initially released, MSDE stood for Microsoft Data Engine - back in the days when it was SQL Server 7.0 based. Now, MSDE stands for Microsoft Desktop Engine, or, more precicely: Microsoft SQL Server 2000 Desktop Engine (MSDE) -- which is what the facts detail.

P H Y S I C A L   C A P A C I T I E S / L I M I T A T I O N S
Max Supported Processors: 2 (all applicable OSes, except Win 98/XP which only allows 1 proc)
Total Supported RAM: 2 GB (all applicable OSes)
Max Database Size: 2 GB
Max Number of Connections: 32,767 (just like non MSDE versions)
Max Number of Databases: 32,767 (just like non MSDE versions)
Max Number of Files per Database: 32,767 (ditto)
Max Number of Objects Per Database: 2,147,483,647 (ditto)

F U N C T I O N A L   L I M I T A T I O N S
Allowed: Multiple Instances of MSDE per physical box (up to 16 instances, just like 'normal' SQL Server).
Not Supported: Failover Clustering, Log Shipping, Parallel DBCC, Parallel Create Index, Enhanced Read Ahead and Scan, Indexed Views, Federated Databases, SAN Support, GUI Tools, Full Text Search, and SQL Mail

R E P L I C A T I O N
MSDEcan participate in all forms of Replication, though they can only be subscribers (including updating subscribers) in Transactional Replication. A big key though, is that Microsoft says that MSDE requires CALs to participate in Replication, so make sure you address this issue if needed.

A N A L Y S I S   S E R V I C E S   F U N C T I O N A L I T Y
MSDE doesn't support ANY Analysis Services functionality.

O T H E R   F U N C T I O N A L I T Y
Supported: Limited DTS Suport - (Deployment Only)
Not Supported: Data Mining, English Query

Q U E R Y   G O V E R N O R
In addition to the above capacities and limitiations, MSDE has the infamous Workload Governor. There are lots of misconceptions about this beast, and I'll cover them in the next post. For now, there are two KEY things to remember about the Workload Governor:
1) It doesn't limit connections.
2) It limits the number of concurrent batches. So, if too much WORK is done on the server instance, the Workload Governor imposes a small penalty. The beauty is that the penalty is so small that for smaller applications and work loads it is barely noticeable. But for larger applications that really need to scale, it imposes quite a hit.

S O U R C E S
More on specifications, limitations, and the likes can be found at the following Microsoft Links:
Features / Functionality by Edition: http://msdn.microsoft.com/library/default.asp?url=/library/en-us/architec/8_ar_ts_1cdv.asp
SQL Server Capacities (check the footnotes): http://msdn.microsoft.com/library/default.asp?url=/library/en-us/architec/8_ar_ts_8dbn.asp
An almost useless link: http://msdn.microsoft.com/vstudio/downloads/addins/msde/examining.aspx

U P   N E X T :
Microsoft SQL Server Personal Facts..

Sponsor
4 Comments
Filed under:

I'm about to do a couple of posts comparing MSDE against SQL Server Express (or, as I like to call it, SQLE).

In these posts I'll cover the facts about MSDE -- in an easy to digest form (I have such a hard time keeping the details straight myself -- I'm sure that others do as well -- so having a simple list of what MSDE does and doesn't do will be handy).

I'll also cover a bit of what the Workload Governor does, and how to tell when it's time to pony up $$ and pay for your SQL Server habit.

Finally, I'll cover the limitations in SQLE against those in MSDE -- suffice it to say that SQLE blows MSDE out of the water (but you already knew that).

However, before I start, I have to cover:

MSDE Workload Governor: Eight Concurrent Operations, or Five?
For the longest time, Books Online, and other supporting documentation stated that the workload governor in MSDE and SQL Server Personal Edition limited concurrent batches to five before kicking in.
Docs updated in January of 2004, out on the MS site state that the number is eight batches.
The newer docs, are of course right. Moreover, it looks like this WASN'T a change that got slipped in with service packs anywhere -- I did a fresh install of SQL Server 2000 (without service packs), ran a few looping queries and managed to throw concurrency violations, and then checked my Application Log. Sure enough, I was told:
"This SQL Server has been optimized for 8 concurrent queries. This limit has been exceeded by 3 queries and performance may be adversely affected."

So the moral of the story, always trust the docs -- when they are correct.

Sponsor

One of the intriguing things about newborns is their propensity to attack themselves. After a few weeks their little hands begin to take on a life of their own: flexing, probing, grabbing. Since their stubby little arms are so short, there isn't much territory to explore for those little hands, and they invariably end up by the head, ears, face, etc.

There's nothing quite like seeing an infant's little hands grab an ear. It's like watching body parts from two, unconnected, people. The little hand finds the ear, and then GRABS on to it. The child, who has been minding its own business, realizes that something isn't right. The pain that their little hand inflicts upon them causes them to tense up. The sad part is that where their hand was, previously, acting as a seperate entity, it too now tenses up. Within a matter of a second, a happy, cuddly little newborn goes from a world of innocence to one where they are being attacked with unmerciful cruelty. (And trying to get them to let go can be a chore too.)

Sadly, newborns aren't the only people susceptible to such self-inflicted unmerciful cruelty. DBAs occasionally attack themselves in the same way.

Background: Once upon a time in the land of IT there lived a huge, hideous, beast of a website. Like Frankenstien, it had been cobbled together from various 'parts,' both living and dead. It had, however, been cursed with a fair amount of success -- to the point where a complete reinvention would have been out of the question from a business standpoint. So, this frankenweb was therefore given yearly sacrifices of developers. Entire teams would be built up around this monster, only to be gobbled up every few years. Only rarely would someone escape, by running, screaming out of the building (at which point they would be stuck with massive bills from their shrink as they tried to cope with the futility that was theirs while they tried to subdue the beast).

As it turns out, a modification needed to be made to the site one day. Modifications were par for the course; they could be sewn directly on to the outer hide of this frankenweb, further increasing technical debt, but giving business an empty, and pointless, feeling of accomplishment.

There was, for the longest time, no suitable dev/testing/integration environment, and as it happened, a sproc needed to be changed out in production. A time window was selected, when the site was only making about $40k/hour, and the change was to go in to place.

Never one to just wing it, I had done my best to create a suitable test environment at the last minute, and had tested the functionality. It all worked fine in my test environment (that wasn't an exact match of the production environment.)

As the production DBA it was my responsibililty to push the change to the live site's database. Four or Five coworkers sat at the ready (at their consolses) as I made the change -- and they quickly confirmed my worst fear: "The site is broken, I can't add things to the cart." said one, and then another...

My fingers had just found the soft lobe of my right ear...

Damn. Well. No biggie. Stay Calm.
There was no way I was going to let this hinder me -- I was better than this... I had a rollback script. I fired it against the DB/Site. It executed perfectly.

"It's still down. I still can't add to the cart." reported a coworker.
"I've got problems too. No go." said another.

Sure enough. No dice. Functionality was broken.

I tensed up. The pain in my earlobe was excruciating. Someone was attacking me with unmerciful cruelty....

What followed were a few minutes of stark raving horror. About 12 minutes en toto. Eventually I was able to calm down and think (after I got my pacifier). The only thing that made sense was that when I had done my rollback script I had intentionally done a DROP and a CREATE on the sproc in question. I wanted to make sure that there was no way that older logic 'remained' and tainted the works.

Now the problem with doing a DROP/CREATE is that the old sproc goes away. All of it. Including permissions. I KNEW this. (When you do an ALTER, the sprocs objectID persists, and any perms on it remain.) I also 'knew' that it wasn't an issue -- because as frankenweb as the site was, I knew that the whole thing sadly ran as the sa.

Only, the only thing that made sense was that this was some sort of a permissions error. A few quick checks later, and I was able to confirm, to my continued horror -- although it quickly spread to relief, that the piece of functionality in question actually DID execute as a user other than sa.

Analysis of the problem afterward showed that: the initial change would have worked, the webservers should have been reset was all. Doing the rollback is what HOSED me.

Moral of the story:
1) Babies are cool.
2) Test in a REAL environment. Don't settle for unmerciful cruelty. (My second priority, after getting the site to run as non sysadmin, was to work on creating a perfect test/integration environment -- I never got there completely, but got close enough for comfort before I ran shrieking from the building.)
3) Rollback scripts are a MUST. Only make sure you script the permissions, or JUST use ALTERs (unless you want to cry like a baby when you attack yourself unmercifully.)

 

Sponsor
1 Comments
Filed under: ,

In a previous life (actually two previous lives), I was a production DBA. One of the environments that I inherited had some ... issues. There were a number of Clustered SQL Server installations -- oodles of hardware, all churning to keep product inventory, customer information, and catalog browsing going for a number of web farms for a complex web site. The site was constantly firing sprocs against the servers to keep real-time info in sync -- but there were also a number of complex, long-running, jobs, sprocs, and batches running constantly in the background which did things like update customer information from other channels, or keep inventory levels synchronized between servers, etc.

The DBA before me had encountered some problems during peak usage of the site: smaller queries would queue up behind the longer running queries when the boxes were getting pegged. If you've studied the User Mode Scheduler implemented by SQL Server.. this comes as no surprise. In an attempt to alleviate the problem, and in coordination with some moron (reportedly) from MS SQL Server Product Support, my predecessor was instructed to set the Processor affinity on each server down to 1 -- i.e. limit all queries on the servers to using one proc only. This was done to stop long running queries from gobbling up the CPU, and allowed the smaller queries access to the CPU to get off the box quicker. Of course, this made the longer/slower queries take MUCH longer to complete, but made a number of 'quicker' queries slower as they were no longer able to harness SQL Server's excellent ability to use multiple procs.

Too bad nobody knew about the ability to set 'processor affinity' on query by query basis -- i.e. nobody knew how to specify the Max Degree of Parallelism. Of course, when I switched the servers back to being able to use all 8 processors, instead of just 1, I was the hero -- so it worked out great in the end. <grin>

The key lies in the OPTION query hint. Just leave your 'quick' queries as they are. And for longer running statements just specify like so:

SELECT blah,bletch,blunk 
FROM someTable
    INNER JOIN someOtherTables ON aBunchOf.Fields = aBunchOfOther.Fields
WHERE someCriteria = hideous 
OPTION (MAXDOP 2) -- ONLY use UP TO 2 processors
Sponsor

I'm no math junkie, but I am a Geek.

My hunch is that any geeks reading this won't be able to withstand answering a simple question; one that I thought up a while ago to help explain the 'value' of NULL to a colleague. So, here goes. What's the answer to:

NULL + =

In the database world, there's really only one acceptable answer... 

 

Sponsor

Here's a shout out to SqlAdvice.com's newest blogger: Bill Swartz.

SqlAdvice is happy to have Bill onboard.

Bill's been doing a great job of helping people on the lists, and sharing his insights with others. Today he posted an incredible insight to the lists on the perfect metaphor for SQL Server's "text in row" option. Happily he posted it to his blog as well.

Jump over to his blog, take a peek, subscribe to his RSS feed, and keep an eye out for more of his insights.

Welcome aboard Bill!

Sponsor
1 Comments
Filed under: ,