• Multiple SQL instances on one box.
• As many 2000 instances as you like (MS are testing 20), plus one 7.0 or 6.5.
• Separate copy of the binaries each – so watch for disk space. Can apply service packs separately to each instance.
• New ServerProperty –"InstanceName" – lets you see which one you are running under. Select serverproperty("InstanceName").
• One instance will be the "default instance" and will allow connectivity from any legacy client. The other instances will be "named instances" and will require an MDAC 2.6 or above client library to connect. Multiple socket numbers are needed for this, so SQL Server listens on 1434 as an arbitrator that then tells the client which port the relevant named-instance is on.
• Desktop edition has a limit of 16 installed instances.
• Multi-instance runs separate copies of SQL Server and SQL Agent.
• Only a single copy of FTS, MSDTC and the SQL tools. Also only one copy of the MDAC stack/code, COM and net-libs.
• Used for fail-over clustering to make this work a lot better.
• Good use is for security.
• Installs to \Program Files\Microsoft SQL Server\MSSQL (default instance). For a named instance it is \Program Files\Microsoft SQL Server\MSSQL$<instancename>
• Tools are installed to \Program Files\Microsoft SQL Server\80\Tools
• NET START MSSQL$<instancename> for starting a named instance
• Registry keys for Named instance are HKLM\Software\Microsoft\Microsoft SQL Server\<instancename>
• Instance name must be 16 chars or less. Start with a letter. Can’t include a colon, comma, backslash or @. Can’t be "default" or "mssqlserver"
• For a named-instance called fred it will listen on \\.\pipe\mssql$fred\sql\query
• To start a named instance can run sqlservr.exe –s<instancename>
• Eventlog entries now say which instance they come from. I.e. MSSQL$<instancename> rather than MSSQLSERVER
• To connect db-lib to a non-default instance then setup an alias to point to the named-pipe.
• Multiprotocol, Banyan Vines and Appletalk net-libs won’t talk to a named-instance.
• Should make installing MSDE much easier.
• To move a database from the default instance to a named-instance can use the "Copy Database Wizard"

OLAP

• Data Mining tools. OLE DB extensions for "training" and "prediction" functions. Helps analyze data in SQL/OLAP and suggest dimensions. New service called "Analysis Server". Uses clustering and classification algorithms).
• Larger Dimension member support. 10M members using virtual memory caching. 100M members using ROLAP (i.e. SQL tables)
• New dimension types – ragged, un-balanced, custom roll-up and slowly changing.
• Virtual dimensions now much faster – can have hierarchy of them. Now indexed for faster querying.
• No reprocessing of a cube required for modifying slowly changing dimensions, changing the number of levels, adding virtual dimensions.
• Distinct count support
• Max number of dimensions doubled to 128. 128 levels per dimension, 256 per cube.
• UI for Cell Security (security added in 7.0 SP1 but no UI)
• Dimension member security. Hide parts of the dimension.
• Security based on MDX expressions.
• Authenticate with NT auth or IIS (NT challenge/response, Anonymous, SSL)
• Support for actions on browse – run com, dll, http etc. e.g. display map of region automatically.
• Drill-though – can get rowset or SQL command back
• Direct connect via HTTP to OLAP
• Better packaging of PTS (Pivot Table Services). Smaller download without MDAC available.
• GUI for setting up partitions on multiple machines
• No more 2GB limit for archive
• TerraCube being developed on Unisys/EMC hardware using supermarket data
• Direct HTTP access (uses IIS for security/auth). Easier deployment of client – light version for short downloads. Automatic setup via asp. Smart setup doesn’t redownload existing components.
• Fast synchronization with RDBMS using ROLAP dimensions.
• Write-back to dimensions.
• Custom roll-ups.
• Linked cubes – can connect them via http.
• Calculated members can be defined anywhere – any dimension, cube (regular or virtual), level. MDX editor always available.
• MDX supports new functions – Allocation, inter-cube lookup.

SQL Server 2000 Preview 1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19