Free “Introducing Microsoft SQL Server 2012” eBook (2nd Draft)

The second draft of the free Introducing Microsoft SQL Server 2012 eBook will be released on 1st February 2012 and will contain the following chapters:

PART I   Database Administration
2. High Availability and Disaster Recovery Enhancements
3. Scalability and Performance
4. Security Enhancements
PART II   Business Intelligence Development
6. Integration Services
7. Data Quality Services
8. Master Data Services

The complete free eBook (containing all 10 chapters) will be released in March 2012.

More information about this free eBook can be found at @MicrosoftPress and on the Microsoft Press blog

Troubleshooting SQL Azure Connection Issues

Thanks to Paras Doshi for providing details on the following article relating to troubleshooting SQL Azure connection issues.

Larry Franks has put together a TechNet Wiki article on SQL Azure Connectivity Troubleshooting which covers:

1. Common Connectivity Errors
Verify SQL Server Management Studio Version
Verify Azure Firewall Settings and Service Availability
Verify That You Can Reach the Server IP
Isolating Network Problems

Given the uptake of SQL Azure, it won’t be long before the forums are flooded with requests for assistance so bookmark this link today.

UPDATE: If you’re looking for help with troubleshooting SQL Server connection issues, have a read of this.

Troubleshooting SQL Server Connection Issues

For those of us who spend time on the SQL Server Forums, you’ll probably agree that one of the most common calls for assistance relates to troubleshooting SQL Server connections.  This is why Rick Byham’s TechNet Wiki article on “How to Troubleshoot Connecting to the SQL Server Database Engine” is a keeper.

Rick’s article covers both basic and complex connection issues and provides concise steps required to:

1. Gather Information about an Instance of SQL Server
Enable Protocols
Test TCP/IP Connectivity
Test Local Connection
Open a Port in the Firewall
Test the Connection

If you haven’t already bookmarked this article, do so today Smile

UPDATE: If you’re looking for help on troubleshooting SQL Azure connection issues, have a read of this.

Microsoft Virtual Academy

The Microsoft Virtual Academy (MVA) is a free cloud-based learning environment focusing on Microsoft Technologies (Although the site states that it focuses on cloud-based technologies, I have found content in some countries not specific to the cloud).

The concept behind the MVA is similar to that of a “Virtual University”; students select a course(s) to study and then do a self-assessment exam to gauge how well they’ve understood the study material. Students also accrue points for material studied and courses completed which are used to promote them through a recognition system (Bronze, Silver, Gold or Platinum Level) and provide them with a comparison on how they rank against other students in their country and around the world.

As it currently stands, there is only 1 SQL Server related course (Introduction To SQL Azure) currently available in Australia (and the United States and United Kingdom) however I have been advised by the Dean that new material will become available in the near future. I also mentioned earlier that there is additional content (SQL 2008/R2/Azure, .Net, Sharepoint, Silverlight, Windows Phone) available in other countries (mainly Spain and Colombia) with some of this content being in English and some in the native language of the selected country (the Microsoft study links are good as all you need to do is change the language setting of the site to a language of your choice and the content is translated. You can also use Google Translate on some of the content).

More information about the MVA can be found in the FAQ.

Retired SQL Server Certifications

The following SQL Server exams are due to retire on 31 July 2012:

  • Exam 70-453 – Upgrade: Transition Your MCITP SQL Server 2005 DBA to MCITP SQL Server 2008
  • Exam 70-454 – Upgrade: Transition Your MCITP SQL Server 2005 DBD to MCITP SQL Server 2008 DBD
  • Exam 70-455 – Upgrade: Transition Your MCITP SQL Server 2005 BI Developer to MCITP SQL Server 2008 BI Developer

This is in addition to exams that were retired on 30 June 2011

  • Exam 70-431 – TS: Microsoft SQL Server 2005 – Implementation and Maintenance
  • Exam 70-441 – PRO: Designing Database Solutions by Using Microsoft SQL Server 2005
  • Exam 70-442 – PRO: Designing and Optimizing Data Access by Using Microsoft SQL Server 2005
  • Exam 70-443 – PRO: Designing a Database Server Infrastructure by Using Microsoft SQL Server 2005
  • Exam 70-444 – PRO: Optimizing and Maintaining a Database Administration Solution by Using Microsoft SQL Server 2005
  • Exam 70-445 – TS: Microsoft SQL Server 2005, Business Intelligence ‒ Development and Maintenance
  • Exam 70-446 – PRO: Designing a Business Intelligence Solution by Using Microsoft SQL Server 2005
  • Exam 70-447 – UPGRADE: MCDBA Skills to MCITP Database Administrator by Using Microsoft SQL Server 2005

    Although these exams have, or will be, retired, the certifications relating to these exams are still relevant and will not disappear from your Microsoft Transcript. 

SQL Server Index Fragmentation Script

Just sharing a cool script I stumbled across while searching for SQL Server index fragmentation scripts (Omer van Kloeten’s Blog). I’ve cleaned it up a bit and turned it into a Stored Procedure so that I can execute it as a SQL Server Agent Job (Not sure if this is a good idea or not so feel free to leave comments).

So why do we need to worry about Index Fragmentation?

*The SQL Server Database Engine automatically maintains indexes whenever insert, update, or delete operations are made to the underlying data. Over time these modifications can cause the information in the index to become scattered in the database (fragmented). Fragmentation exists when indexes have pages in which the logical ordering, based on the key value, does not match the physical ordering inside the data file. Heavily fragmented indexes can degrade query performance and cause your application to respond slowly. For more information, see this Microsoft Web site.

Please Note: Defragmentation does not yield performance gains in every case. Every scenario is different. Similarly, determining when you should run the defragmentation statements requires analysis.

*Reorganizing and Rebuilding Indexes (

MSDN Gallery:

CREATE PROC usp_FixIndexFragmentation
    /* Use online rebuild if detected  edition is Enterprise, Developer or Evaluation.*/
    @Online BIT = 1,

    /* Log recommendations Only */    
    @LogOnly BIT = 1,

    /* Specifies a percentage that indicates how full the Database Engine should 
    make the leaf level of each index page during index creation or alteration. 
    Fillfactor must be an integer value from 1 to 100. The default is 0. */
    @FillFactor TINYINT = 50,

   /* Specifies the scan level that is used to obtain statistics. Valid inputs are 
    @Index_physical_stats_mode VARCHAR(20) = 'Limited',

    /* Total number of index or data pages. */
    @Index_physical_stats_page_count BIGINT = 500,

    /* Logical fragmentation for indexes, or extent fragmentation for heaps in the 
    IN_ROW_DATA allocation unit. The value is measured as a percentage and 
    takes into account multiple files */
    @Avg_fragmentation_percent TINYINT = 40

    DEClARE @Rows BIGINT = 0
    DEClARE @Counter BIGINT = 1

    IF OBJECT_ID('tempdb..#TmpTable') IS NOT NULL 
        DROP TABLE #TmpTable 
    CREATE TABLE #TmpTable 
        RowNo BIGINT IDENTITY(1,1), TSQLStatement VARCHAR(MAX)
        SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[LogTable]') 
        AND type in (N'U')
        CREATE TABLE [dbo].[LogTable]
            [TSQLStatement] [varchar](max) NULL,
            [FRAGMENTATION] [float] NULL,
            [Executed] [datetime] NULL,
            [ExecutedBy] [varchar](100) NULL
        ) ON [PRIMARY]
    INSERT INTO LogTable
    SELECT    'ALTER INDEX [' + + '] ON [' + + '].[' + + '] ' +
        WHEN ps.avg_fragmentation_in_percent > @Avg_fragmentation_percent THEN
            WHEN @Online = 1 AND 
                    CAST(SERVERPROPERTY('Edition') AS VARCHAR) LIKE 'Enterprise%'
                    CAST(SERVERPROPERTY('Edition') AS VARCHAR) LIKE 'Developer%'

                    CAST(SERVERPROPERTY('Edition') AS VARCHAR) LIKE 'Evaluation%'
                    + CAST(@FillFactor AS VARCHAR(10)) + ') '
                + CAST(@FillFactor AS VARCHAR(10)) + ') '
    END +
        WHEN pc.partition_count > 1 
            THEN ' PARTITION = ' + CAST(ps.partition_number AS NVARCHAR(MAX))
        ELSE ''
    FROM sys.indexes AS ix INNER JOIN sys.tables t
        ON t.object_id = ix.object_id     INNER JOIN sys.schemas s
        ON t.schema_id = s.schema_id INNER JOIN 
            SELECT object_id, index_id, avg_fragmentation_in_percent, partition_number
            FROM sys.dm_db_index_physical_stats (DB_ID(), NULL, NULL, NULL, 
            WHERE page_count > @Index_physical_stats_page_count
        ) ps
            ON t.object_id = ps.object_id AND ix.index_id = ps.index_id INNER JOIN 
        SELECT object_id, index_id, COUNT(DISTINCT partition_number) AS partition_count
        FROM sys.partitions
        GROUP BY  object_id, index_id
    ) pc
            ON t.object_id = pc.object_id AND ix.index_id = pc.index_id
    WHERE ps.avg_fragmentation_in_percent > 10 AND IS NOT NULL
    ORDER BY  ps.avg_fragmentation_in_percent DESC

    SET @Rows = @@ROWCOUNT

    IF @LogOnly = 0
        WHILE (@Counter <= @Rows)
            SELECT @TSQLStatement = TSQLStatement
            FROM #TmpTable
            WHERE RowNo = @Counter

            EXECUTE sp_executesql @TSQLStatement

            SET @Counter += 1

SQL Azure Compatibility Assessment Service

This experimental online service provides the ability for users to analyse their SQL Server databases to see whether they’re compatible with SQL Azure.

The service provides the user with a report listing the database objects not supported on SQL Azure, and the objects that need fixing.




What you need

What you do not need

  • Azure knowledge
  • Azure account

Give SQL Azure Compatibility Assessment Service a go today.

%d bloggers like this: