Benjamin Wright-Jones

Exploring Information, Infrastructure and Solution Architecture

May, 2010

Posts
  • Benjamin Wright-Jones

    SQL Azure vs. SQL Server

    • 1 Comments

    I’ve just read the SQL Azure vs. SQL Server whitepaper http://www.microsoft.com/downloads/details.aspx?displaylang=en&FamilyID=86f12b41-1eba-4567-9ac8-02eaa7d12034 its only a few pages so one of the lighter documents.

    Some interesting and important points which are summarised in the paper e.g. you cannot control where your database files are placed. and also referenced in the Books Online Transact-SQL Support (SQL Azure Database) http://msdn.microsoft.com/en-us/library/ee336250.aspx

    The following Transact-SQL features are not supported by SQL Azure:

    • Common Language Runtime (CLR)
    • Database file placement
    • Database mirroring
    • Distributed queries
    • Distributed transactions
    • Filegroup management
    • Global temporary tables
    • Spatial data and indexes
    • SQL Server configuration options
    • SQL Server Service Broker
    • System tables
    • Trace Flags

    50GB databases will also be here soon http://blogs.msdn.com/b/sqlazure/archive/2010/04/16/9997517.aspx

  • Benjamin Wright-Jones

    Backing up to NUL(L)?

    • 1 Comments

    Some of you may be wondering why you would want to do this.  We had a real requirement in a performance benchmark to use FULL recovery and backup the transaction log but we did not want to retain the log changes.  We already had a gold backup which we restored after each test.

    I was wondering if it was still possible to BACKUP to NULL in SQL Server 2008 R2, and the answer is yes.  Warning: don’t ever do this in a production environment.

    BACKUP LOG sandbox TO DISK = 'NUL'
    
    I am unsure why the NUL keyword is missing a ‘L’.

    Interesting, if I try to backup the log using the NUL keyword then this fails

    BACKUP LOG sandbox TO DISK = 'NUL.bak'
    

     

    Msg 3201, Level 16, State 1, Line 1
    Cannot open backup device '…\Backup\NUL.bak'.

    Operating system error 2(failed to retrieve text for this error. Reason: 15105).
    Msg 3013, Level 16, State 1, Line 1
    BACKUP DATABASE is terminating abnormally

     

    But, this works and persists the backup file in the default backup directory (look in msdb.dbo.backupmediafamily)

     

    BACKUP LOG sandbox TO DISK = 'NULL.bak'
    
  • Benjamin Wright-Jones

    SQL Server 2008 R2 Cumulative Update 1

    • 0 Comments

    Hot on the heels of the RTM release of SQL Server 2008 R2, we have just announced the first cumulative update.  The knowledge base article is here http://support.microsoft.com/kb/981355 which lists all the relevant fixes, which are essentially fixes from SQL Server 2008 Service Pack 1 Cumulative Update 5 to 7.

    As always, please review the knowledge base article and list of fixes before you consider deploying this update.

  • Benjamin Wright-Jones

    Hotfix Alert: Identifying poor I/O

    • 0 Comments

    We have just released a hotfix to help identify poor I/O issues on Windows Server 2008 and Windows Server 2008 R2. This is particularly relevant for SQL Server environments as it will the administrator to determine if I/O performance issues are caused by the SAN.

    Whilst the output is not particularly easy to consume, it is a good step towards identifying I/O issues.

    The knowledge base articles:

    Add a fix to improve the logging capabilities of the Storport.sys driver to troubleshoot poor disk I/O performance in Windows Server 2008 R2 http://support.microsoft.com/kb/978000

    A hotfix is available that improves the logging capabilities of the Storport.sys driver to troubleshoot poor performance issues for the disk I/O in Windows Server 2008 http://support.microsoft.com/kb/978000

    The full details are available here http://blogs.msdn.com/ntdebugging/archive/2010/04/22/etw-storport.aspx

Page 1 of 1 (4 items)