Email Article To a Friend View Printable Version

Limitations of SQL Server 2005 Express Edition

SQL Server 2005

The free version of SQL Server 2005 is the "Express" edition. Its a cut down version of SQL Server 2005 and is ideal as an upgrade from Access, for desktop use or small multi-user databases. Its not designed for large scale production use, and comes with a number of limitations. The most notable limitations are:

SQL Server 2005 Express Edition Limitations
CPU 1 CPU
RAM Maximum 1 GB
Database Size Maximum 4 GB
Operating Systems Windows Server 2003 SP1 / 2000 SP4 / XP SP2 / Vista (Requires SQL Server 2005 SP2)

While Express edition will install on a machine with multiple CPUs and more than 1GB of RAM it will not use the additional hardware. To increase the performance of express through additional CPUs and RAM you need to upgrade to Workstation, Standard or Enterprise edition.

Additional features of SQL Server 2005 that are limited or not available in the Express edition:

SQL Server 2005 Express Edition
Full Text Search Only in Express Advanced Services
Reporting Services Only in Express Advanced Services
Management Studio

Only in Express Advanced Services

SQL Server Integration Services (SSIS) No
Partitioned Tables No
Indexed Views No
Database Mirroring No
Profiler No
Database Tuning Advisor No
SQL Agent Job Scheduling No
Notification Services No
Web Services (HTTP Endpoints) No
Service Broker May be a Client only
Transational Replication May be a Subscriber only
Merge Replication May be a Subscriber only

More information on Microsoft SQL Server 2005 Express Edition can be found in the product page.

For a full list of SQL Server 2005 Express Features.

See how Express compares to other versions of SQL Server 2005.

See how the Express and Express Advanced Services compare.



Share
  • Facebook
  • Google Bookmarks
  • Ask
  • LinkedIn
  • Socialogs
  • Wikio
  • Digg
  • Twitter
  • SlashDot
  • Reddit
  • MySpace
  • Del.icio.us
  • Blogter
  • BlogMemes
  • Yahoo Buzz




Email Article To a Friend View Printable Version

SQL Server 2005 New Feature: Scalable Shared Databases

SQL Server 2005

Microsoft have released details of a new feature available in SQL Server 2005 Enterprise Edition: Scalable Shared Databases.

What is it?

A read-only database stored on a SAN than can be attached to multiple SQL Server instances. It will allow you to have multiple instances of SQL Server host the same database, with the same results guaranteed, so that you can scale up the number of SQL Servers without having to duplicate the database files.

As database design progresses towards even larger databases with reporting, web service and OLAP requirements this feature will help with scaling load on SQL Server instances, without adding to storage costs. If currently you have a dedicated instance of SQL Server for each of these requirements you would also have a dedicated copy of the database files. With Scalable Shared Databases you would only have one copy of the files.

More details on Scalable Shared Databases will be released with a Books Online update expected in December.



Share
  • Facebook
  • Google Bookmarks
  • Ask
  • LinkedIn
  • Socialogs
  • Wikio
  • Digg
  • Twitter
  • SlashDot
  • Reddit
  • MySpace
  • Del.icio.us
  • Blogter
  • BlogMemes
  • Yahoo Buzz






Email Article To a Friend View Printable Version

Davide Mauri's SQL Server 2005 Index Usage and Health Scripts

SQL Server 2005

Davide Mauri has published a number of SQL Server 2005 scripts he uses as part of his DBA toolkit to analyse indexes including their usage and health. They're a great short cut to building your own after wading through BOL.

So far there are four UDFs he's published that show the indexes on a table, the size of indexes, index fragmentation, and usage.

Download the scripts from here.

In the next few weeks I plan to publish my set of dba scripts for SQL Server 2000 and 2005. Subscribe to my rss feed to make sure you get updates.



Share
  • Facebook
  • Google Bookmarks
  • Ask
  • LinkedIn
  • Socialogs
  • Wikio
  • Digg
  • Twitter
  • SlashDot
  • Reddit
  • MySpace
  • Del.icio.us
  • Blogter
  • BlogMemes
  • Yahoo Buzz






Email Article To a Friend View Printable Version

Capturing STDERR output from SQLCMD.exe

SQL Server 2005

Today while working with a .bat file full of SQLCMDs with really verbose output, I wanted just to capture error messages that were being sent to STDERR without changing the .sql files. I know how to capture/redirect handles in a variety of programming languages, but I haven't tried to do this in a windows command prompt. A quick search located a neat page on Using Command Redirection Operators in Windows XP.

The most common redirect used is > which can write the output (STDOUT) from a command to a file instead of displaying it onscreen. Place the number 2 before it ( 2> ) and you are now getting the errors (STDERR) handle to write to a file.

So using cmd.exe in Windows you can redirect a command output from STDERR to a file using

cmd.exe command-to-run 2> errors.txt

If you are trying to capture the STDERR with SQLCMD, you will need to use the -r switch. Use -r0 to capture error messages of severity level 11 or higher. Use -r1 to capture all error and PRINT messages.

Capture Severity 11 or Higher Error Messages to install-err.log

sqlcmd -E -i"install.sql" -r0 2> install-err.log

Capture all Error Messages and Print Messages to install-err.log

sqlcmd -E -i"install.sql" -r1 2> install-err.log

Capture STDOUT to install.log and Error Messages to install-err.log

sqlcmd -E -i"install.sql" -r1 2> install-err.log 1> install.log

 

Incidentally I found these pages while giving SearchMash a go. Apparently its a feedback site for interface changes at Google. It uses a different branding to try to get honest feedback from users on search results. Interesting.



Share
  • Facebook
  • Google Bookmarks
  • Ask
  • LinkedIn
  • Socialogs
  • Wikio
  • Digg
  • Twitter
  • SlashDot
  • Reddit
  • MySpace
  • Del.icio.us
  • Blogter
  • BlogMemes
  • Yahoo Buzz






Email Article To a Friend View Printable Version

"SQL Server 2005 Everywhere Edition" Now Called "SQL Server 2005 Compact Edition"

SQL Server 2005

Ahead of the final release in December 2006, Microsoft have released SQL Server Compact Edition RC1.

SQL Server 2005 Compact Edition is a very lightweight, in process relational database engine for use in desktop applications and mobile devices. It has an extremely small footprint (~3MB) and minimal overhead. It won't use any where near as much memory as Express so its suitable for memory pressured systems. It works on Windows 2000 SP4, Windows Server 2003 SP1, and Windows XP SP2.

The main SQL Server Compact page has yet to be fully rebranded. The SQL Server Compact Edition RC1 readme is available, as are RC1 Books Online.

I first heard about this on Steve Lasker's blog. There's more on the SQL Server Everywhere Blog.



Share
  • Facebook
  • Google Bookmarks
  • Ask
  • LinkedIn
  • Socialogs
  • Wikio
  • Digg
  • Twitter
  • SlashDot
  • Reddit
  • MySpace
  • Del.icio.us
  • Blogter
  • BlogMemes
  • Yahoo Buzz






Email Article To a Friend View Printable Version

Architecting Service Broker Applications

SQL Server 2005

Microsoft have released a whitepaper on Architecting SQL Server 2005 Service Broker Applications. The whitepaper focuses on when its appropriate to use Service Broker, design aspects for message flow and processing, availability, and infrastructure.

I think its an excellent view of Service Broker implementation from a medium-high level, with discussion of the methods used, without going in the fine points of "how to". Its an great read for dbas/developers/architects and helps readers have a better understanding of both the design theory and basics of Service Broker applications.

If you are trying to get your head around Service Broker and are finding books-online to be a bit of a hard read, this whitepaper will really help.



Share
  • Facebook
  • Google Bookmarks
  • Ask
  • LinkedIn
  • Socialogs
  • Wikio
  • Digg
  • Twitter
  • SlashDot
  • Reddit
  • MySpace
  • Del.icio.us
  • Blogter
  • BlogMemes
  • Yahoo Buzz






Email Article To a Friend View Printable Version

SQLIOSim Tool Released

SQL Server 2005

Microsoft have released their SQLIOSim Tool for stress testing IO subsystems. Different from the SQLIO tool which is tests IO throughput.

The tool is designed to simulate the effects SQL Server would have on disk subsystems. It includes options for testing multiple files; random OLTP style queries; read aheads; bulk inserts; checkpoints; rollbacks; backup; sort; DBCC SHRINKFILE; and DBCC CHECKDB operations.

Stress testing of IO subsystems can be useful for diagnosing potential issues before they are realised in a deployed production environment. As stated by the SQL Server Storage Engine Team:

There are many components involved with reading and writing data to files. Starting from an application (SQL Server or SQLIOSim) the IO request is handed over to the Operating system via an API call. Once in the hands of the OS the request will travel through levels of filter drivers installed by things like antivirus software, backup utilities and finally find its way to a driver that will hand the actual data over to a disk controller, and eventually find its way to a disk or array of disks. There may be caching on the disks, and in the case of high end arrays there may also be logic to determine whether or not to service the request immediately or defer. If even one of these pieces get it wrong the results for your data would be disastrous.

A number of sample configuration files are included for testing multiple scenarios such as multiple files, sequential writes, and hardware caching.

A Knowledgebase article on how to use SQLIOSim has been published.

Downloads for SQLIOSim:

SQLIOSim utility for x86

SQLIOSim utility for x64

SQLIOSim utility for Itanium



Share
  • Facebook
  • Google Bookmarks
  • Ask
  • LinkedIn
  • Socialogs
  • Wikio
  • Digg
  • Twitter
  • SlashDot
  • Reddit
  • MySpace
  • Del.icio.us
  • Blogter
  • BlogMemes
  • Yahoo Buzz






Email Article To a Friend View Printable Version

Count the Occurrence of a Phrase or Word in a Column (SQL Server 2005)

SQL Server 2005

Simple little bit of code that will show you the number of times the value of @PhraseOrWordToCount is found in TextColumn.

SELECT (LEN(TextColumn) - LEN(REPLACE(TextColumn, @PhraseOrWordToCount, '')) / LEN(@PhraseOrWordToCount)) AS Occurrence
FROM dbo.MyTable

I noticed this on Simon Sabin's blog, I think it has more applications than just FullText searches so I thought I'd do a modified re-post.



Share
  • Facebook
  • Google Bookmarks
  • Ask
  • LinkedIn
  • Socialogs
  • Wikio
  • Digg
  • Twitter
  • SlashDot
  • Reddit
  • MySpace
  • Del.icio.us
  • Blogter
  • BlogMemes
  • Yahoo Buzz






Email Article To a Friend View Printable Version

Moving a TFS V1.0 Sharepoint Site

SQL Server 2005

Continuing on with Team Foundation Server High Availability with SQL Server 2005 Mirroring. If you needed to make changes to your TFS Sharepoint deployment, v1.0 options are very limited. Microsoft do have a TechNote on configuring TFS to use a Remote SharePointServer.

The good news is that the next version should have a lot more configuration options.



Share
  • Facebook
  • Google Bookmarks
  • Ask
  • LinkedIn
  • Socialogs
  • Wikio
  • Digg
  • Twitter
  • SlashDot
  • Reddit
  • MySpace
  • Del.icio.us
  • Blogter
  • BlogMemes
  • Yahoo Buzz






Email Article To a Friend View Printable Version

Team Foundation Server High Availability with SQL Server 2005 Mirroring

SQL Server 2005

As Team Foundation Server (TFS) becomes the source code repository for more and more teams, SQL Server DBAs are ultimately responsible for the backup and availability of these databases.

Sudhir Hasbe the Program Manager for Team Foundation Server has noted that SQL Server 2005 Mirroring will provide a warm standby but TFS will not automatically failover. After a failover of the database mirroring (manual or automatic) the database settings in TFS will need to be change to point to the now Primary database server.

Update 6th Nov: Microsoft have published four draft whitepapers on Mirroring TFS with SQL Server:



Share
  • Facebook
  • Google Bookmarks
  • Ask
  • LinkedIn
  • Socialogs
  • Wikio
  • Digg
  • Twitter
  • SlashDot
  • Reddit
  • MySpace
  • Del.icio.us
  • Blogter
  • BlogMemes
  • Yahoo Buzz







Gold Coast Aquarium Maintenance | Gold Coast Marine Fish | Gold Coast Tropical Fish
Jewel Jones - Counselling Service - Penrith, Richmond
 
     
 Copyright © 2017 Julian Kuiters
 All trademarks and copyrights on this page are owned by their respective owners.