Gridding .NET

good SSIS (aka DTS)/ETL SQL Server 2005 stuff

from Donald Farmer, Group Product Manager of SSIS (aka DTS) Subscribed !!

a few posts

http://jaynathan.com/blog/archive/2004/12/02/299.aspx
http://sqljunkies.com/WebLog/simons/archive/2005/06.aspx
http://blogs.msdn.com/ashvinis/archive/2005/08/15/451964.aspx
http://blogs.conchango.com/jamiethomson/archive/category/23.aspx
http://blog.magenic.com/andym/category/48.aspx
http://sqljunkies.com/WebLog/donald%farmer/archive/2005/03/13/8819.aspx
http://sqljunkies.com/WebLog/donald%farmer/archive/2005/03/06/8627.aspx
http://www.developerfusion.co.uk/blogs/detail/348

SQL Server scalability perception

SQL Server scalability perception

In the last serveral consulting gigs I always came across people stating " SQL Server does not scale".
Now, I' ve tried to analyze these "opinions":

Hardware:
SQL Server does only run on Windows, which does not support proprietary CPUs like Sparc, Power or the mainframe ones.

Big Iron hardware(Midrange and Enterprise):
32 bit
> 4 way : some vendors have > 4 way support (but Dell does not anymore)
> 8 way : only IBM, Unisys and Fujitsu have servers running > 8 way (and BTW, shame on you HP, you threw away all of Compaq's technical excellence)

64 bit (still not proven despite 100 + casestudies)
AMD does not have > 8 way yet
Itaniums have excellent scalability (see HP's 64 way boxes), but are not mainstream

Software
W2K and W2k3 have 32-way 32 bit support, but are relegated to expensive Datacenter above 8-way.
W2K and W2k3 have a decent Itanium 64 bit track record, but haven't been able to wink the market, because of lack of a Standard version of Windows Server, but also because of a lack of
software and general purpose programming environments(like .NET).

What can MS do?
- push customers towards hardware vendors which "deliver" the right kind of hardware
- push 64 bit and Itanium in particular
- change the licensing/pricing to allow servers to use up to 16-way boxes with Advanced Server
- demonstrate the right kind of case studies (> 8 CPUs, 1 TB + implementations)

Now, back to those infidels not believing in SQL Server scalability:
with the right kind of hardware it can be done.

Some of my other posts:

http://dotnetjunkies.com/WebLog/stefandemetz/archive/2004/11/16/32249.aspx
http://dotnetjunkies.com/WebLog/stefandemetz/archive/2004/11/07/31275.aspx
http://dotnetjunkies.com/WebLog/stefandemetz/archive/2004/05/16/13724.aspx
http://dotnetjunkies.com/WebLog/stefandemetz/archive/2004/03/04/8474.aspx

Yukon and the 64 bit story

Besides my personal interest in SQL Server 2005 , at work I am asked to plan ahead for deployment of Yukon. As the company itself is in the midst of a technology transition, including an investment and future proof database platform, I am was considering 64 bit Yukon. 

But from MS the 64 bit story is not very good in regards to Yukon (and Itanium in particlar). And I am a bit worried. With all the BI goodness in Yukon, without a good 64 bit story, how can one take THE right decision?   

I know that 64 bitness will be included in beta 3, but is it not a bit late?

some links:

http://www.microsoft.com/sql/64bit/default.asp
http://www.informit.com/articles/article.asp?p=170820
http://www.ftponline.com/vsm/2004_en/magazine/features/rjennings/
http://weblogs.asp.net/tims/archive/2004/06/28/167675.aspx
http://www.microsoft.com/seminar/events/series/msdn64bitwin.mspx
http://www.lemanix.com/nick/archive/2004/09/11/1261.aspx
http://blogs.sqlxml.org/vinodkumar/archive/2004/01/16/328.aspx
http://www.thespoke.net/MyBlog/Hirantha/MyBlog.aspx
http://www.bsdg.org/2004/09/get-ready-for-net-20-with-danny-thorpe.shtml

favorite classes in .NET 2.0

to insert lots of stuff fast into sql server use (using some funnel pattern):

SqlBulkCopy
SqlBulkCopyColumnMapping
SqlBulkCopyColumnMappingCollection

World's largest known commercial database > 500 TB

read on

http://www.eweek.com/article2/0,1759,1677521,00.asp

SQL SERVER 2005 runs MS 1.7 Terabyte SAP in production

read all about it at http://www.microsoft.com/presspass/features/2004/sep04/09-30SQLServer05.asp

SQL Server 2005 Yukon DTS

5 articles on SQL Server 2005 Yukon DTS

yukon dts links
http://blogs.msdn.com/mthalman/archive/2004/03/11/88148.aspx

some SQL Server 2005 DTS/SSIS stuff
http://dotnetjunkies.com/WebLog/stefandemetz/archive/2005/03/13/60215.aspx

SQL Server 2005 Yukon clustering

Despite all my love for SQL Server and Yukon(for it's BI features) I am frustrated by it less then optimal clustering features. Now,
since the launch of the SQL Server 2005 Yukon Beta 2 was yesterday, I don't want to be a party pooper or risk a future "red pill" offer.
But, historically Oracle and DB2 have been more advanced in high availability and I hoped that SQL Server would catch up on the clustering side. But even in it's 2005 incarnation it didn't. MySQL seems to have done it, albeit I don't have the details ready.

"My" sort of clustering would have meant a sort of load balancing, at least for read-only operations. Considering reporting or applications like .Text, where reads make up at least 95% of the total load. Probably more than 80% of applications do m ore than 80% of reads. That means that you could cluster 5 boxes in load balancing, of which 1 does the writing - CRUD stuff - and the other 4 do the reading. Of the 4 one could even do the admin stuff(index building, DBCC checking), so that the 1 is free of admin work. If the "writing" box failed, the cluster would just shift the "write" duty to a "read" box and/ot eventually shift the responsability of  "admin" work onto another box.

Pretty simple really. One of the the many instances would hold a "write lock", the others a "read-only" lock. No need for super cmplex DLM (distributed lock management) on the database files, no need to duplicate database files, do partitioned views, implement shared disk or other quirky linked server stuff. Done cleanly it would be easy to switch the "write" capabilities off during forced or unforeseen maintainance, letting users view their data, but not modify. Design and testing would could also be done much easier. Downtime, patching would not be a problem on a live server anymore. Consolidation would even be a lot easier.

I blame the possessive DB instance not sharing the database files, nobody else.

SQL SERVER Yukon Beta 2 is out

Whidbey Yukon beta update !!

SQL SERVER Yukon Beta 2 coming soon

Update SQL SERVER Yukon Beta 2 coming soon !!! is out !!

from
http://sqljunkies.com/WebLog/kamalh/archive/2004/05/20/2612.aspx

other links:

yukon roundup(Tech Ed)http://weblogs.asp.net/pandrew/archive/2004/05/26/142003.aspx

http://weblogs.asp.net/dstone/archive/2003/08/01/22266.aspx
http://sqljunkies.com/WebLog/tom%20rizzo/archive/2004/03/17/1628.aspx
http://www.iamnotageek.com/archive/index.php/t-52774.html
http://ea.3leaf.com/2004/05/tidbits_on_xml_.html

http://coltkwong.com/blogs/see/posts/603.aspx
http://www.dotnetindia.com/2004/02/yukon_beta_2_sc.html
http://dotnetjunkies.com/WebLog/dougseven/archive/2003/11/13/3536.aspx
http://sqljunkies.com/WebLog/nielsb/archive/2003/11/18/470.aspx
http://weblogs.asp.net/mspedding/archive/2003/07/17/10175.aspx
http://staff.develop.com/nielsb/PermaLink.aspx?guid=5072e9a6-b080-4b00-8c42-9498333e59ee
http://www.cnblogs.com/ccboy/archive/2004/04/15/6166.aspx
http://blog.lotas-smartman.net/archives/2004/03/31/1682/top-30-features-of-yukon/
http://sswug.org/blogging/dnajac/posts/436.aspx
http://sswug.org/blogging/swynk/posts/580.aspx
http://sql.publog.de/
http://blog.svencipido.be/Blog/CommentView,guid,1f0f1f62-96ad-419d-b00d-f8e2b2c76bd6.aspx
http://blog.informationweek.com/windows/archives/2004_03.html

SQL Server scalability (64 bit)

SQL Server scalability casestudies for IT managers:

http://www.microsoft.com/sql/64bit/productinfo/casestudies.asp

other
http://www.enertia-software.com/item.asp?item=622
http://www.intel.com/business/casestudies/raymond_james.pdf
http://www.intel.com/business/bss/products/server/itanium2/testimonials.htm
http://www.intel.com/business/casestudies/koehler.pdf
http://www.intel.com/business/bss/swapps/server2003/testimonials.htm?showAll=true&selected=0
http://www.intel.com/business/bss/swapps/server2003/testimonials.htm?showAll=true&selected=1
http://www.intel.com/business/bss/swapps/server2003/testimonials.htm?showAll=true&selected=2
http://www.intel.com/business/bss/swapps/server2003/testimonials.htm?showAll=true&selected=3
http://www.intel.com/business/bss/swapps/server2003/testimonials.htm?showAll=true&selected=6
http://www.intel.com/business/bss/swapps/server2003/testimonials.htm?showAll=true&selected=8
http://www.intel.com/business/bss/swapps/server2003/testimonials.htm?showAll=true&selected=9
http://www.intel.com/business/bss/swapps/server2003/testimonials.htm?showAll=true&selected=11

http://www.unisys.com/products/es7000__servers/news_a_events/all__news/01218375.htm

http://www.hp.com/products1/itanium/testimonials/banca.html
http://www.hp.com/products1/itanium/testimonials/comp_usa.html
http://www.hp.com/products1/itanium/testimonials/finnish.html
http://www.hp.com/products1/itanium/testimonials/denizbank.html

some posts:

http://blogs.msdn.com/brada/archive/2004/03/10/87635.aspx
http://weblogs.asp.net/rwlodarc/archive/2003/04/24/6006.aspx
http://stupidevilbastard.com/archives/2004/02/18/intel_admits_64_bit_is_next_big_thing.php
http://weblogs.asp.net/jdennany/posts/31691.aspx
http://weblogs.asp.net/volkerw/archive/2004/02/25/80175.aspx
http://weblogs.asp.net/oldnewthing/archive/2003/11/19/55757.aspx
http://weblogs.asp.net/frankarr/archive/2004/03/25/95564.aspx
http://weblogs.asp.net/mdavey/archive/2004/03/19/92568.aspx
http://weblogs.asp.net/wallym/archive/2004/01/29/64749.aspx
http://weblogs.asp.net/pleloup/archive/2003/10/29/34389.aspx
http://weblogs.asp.net/volkerw/archive/2004/04/12/111860.aspx
http://sqljunkies.com/WebLog/jt_kane/archive/2003/09/29/247.aspx

SQL Server 2005 Yukon management and administration tools

read this SQL Server 2005 Yukon management and administration tools

http://dotnetjunkies.com/WebLog/stefandemetz/archive/2004/05/07/13091.aspx
http://dotnetjunkies.com/WebLog/stefandemetz/archive/2004/03/04/8474.aspx
http://dotnetjunkies.com/WebLog/stefandemetz/archive/2004/03/02/8225.aspx
http://dotnetjunkies.com/WebLog/stefandemetz/archive/2004/02/01/6416.aspx
http://dotnetjunkies.com/WebLog/stefandemetz/archive/2004/01/31/6382.aspx
http://dotnetjunkies.com/WebLog/stefandemetz/archive/2004/01/21/5853.aspx

SQL SERVER 2005 aka Yukon - first case study

After this -despite having seen a demo by MS about Yukon - MS SQL Server 2005 - I felt a bit worried about Yukon and it's feature set.

Now I am a bit more relieved after reading MS SQL Server 2005 -Yukon's first case study at

http://www.informit.com/articles/article.asp?p=170820&seqNum=1

other posts about SQL Server 2005 Yukon :

http://weblogs.asp.net/pwilson/posts/8187.aspx
http://sqljunkies.com/WebLog/enigma/archive/2004/03/05/1444.aspx
http://sqljunkies.com/WebLog/mrys/archive/2004/04/15/2075.aspx
http://dotnetjunkies.com/weblog/stefandemetz/posts/6416.aspx
http://dotnetjunkies.com/weblog/stefandemetz/posts/6382.aspx
http://dotnetjunkies.com/weblog/stefandemetz/posts/5789.aspx

http://sqljunkies.com/WebLog/ktegels/archive/2004/05/26/te04dat317.aspx
http://techedbloggers.net/Tracks/DAT/DAT313/2800.item
http://sqljunkies.com/WebLog/ktegels/archive/2004/05/17/2536.aspx

Database scalability - How scalable is MS SQL Server ?

Today I got news: a joint venture of my employer will merge it's IT operations into ours. What does it mean to me?My largest DB table goes from 100 million to 500 million rows. What can I make to improve MS SQL Server database scalability?

Good things for database scalability:

• do the right indexes(avoiding autocounters if possible)
• partition tables' data
• put old data ointo separate table (archive)
• distribute DB, Temps DB and Transaction logs to different filegroups 
• distribute above to hard drive partitions
• use nolock, readpast and/or READUNCOMMITTED where possible
• shift processing of some data to out of business hours times

To avoid:

• triggers
• cursors
• PK-FK relations (CHECK constraints are cheaper)

for SQL Server 64 bit database scalability check out:

http://dotnetjunkies.com/WebLog/stefandemetz/archive/2004/01/21/5853.aspx
http://dotnetjunkies.com/WebLog/stefandemetz/archive/2004/05/16/13724.aspx

Will Yukon be slower than SQL Server 2000?

Update: read SQL Server 2005 first case study

http://dotnetjunkies.com/WebLog/stefandemetz/archive/2004/05/07/13091.aspx

http://searchdatabase.techtarget.com/qna/0,289202,sid13_gci952955,00.html

Will Yukon be idiot proof ? A tale of a forgotten DB

In one of the last gigs I stumbled upon an orphaned SQL Server db.Everybody used it, but nobody managed it. It had hummed along non stop over a full year on a cluster, without giving problems. A minimal maintainance plan had been setup  when it was created, then it was "forgotten" by the admins. It had reached the size of about 70 GB without anybody noticing, filling up 4x36 GB RAID disks. Now diskspace was no more. And it started throwing fits.
The users noticed, the technical people were called. And then they noticed.Nobody had tenderly cared this "little" app since it was born, not even to notify by mail, pager or net send of incumbing problems.

The question: will Yukon force the creator of a db to create some minimal monitoring alerts/auto-partitioning/auto-indexing?

new Yukon site

Yukon licensing a la mainframe

At a SQL Server Yukon presentation I asked if there was a possibility for licensing a la mainframe or on demand. No answer was given.
This would help the very large deployments for things like HR and Accounting were over 50% of the monthly data crunching is done in 4-5 days. It would also help MS compete against DB2(IBM) and Sybase in high end scenarios.

SQL Server scalability on 64 bit windows

sql server scalability casestudies

http://dotnetjunkies.com/WebLog/stefandemetz/archive/2004/05/16/13724.aspx

http://www.unisys.com/about__unisys/news_a_events/01218375.htm