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
2.
Verify SQL Server Management Studio Version
3.
Verify Azure Firewall Settings and Service Availability
4.
Verify That You Can Reach the Server IP
5.
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
2.
Enable Protocols
3.
Test TCP/IP Connectivity
4.
Test Local Connection
5.
Open a Port in the Firewall
6.
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 (http://technet.microsoft.com/en-us/library/ms189858.aspx)

MSDN Gallery: http://code.msdn.microsoft.com/SQL-Server-Index-6c12e7d4

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 
    DEFAULT, NULL, LIMITED, SAMPLED, or DETAILED. */
    @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
)
AS
BEGIN
    SET NOCOUNT ON

    DEClARE @Rows BIGINT = 0
    DEClARE @Counter BIGINT = 1
    DECLARE @TSQLStatement NVARCHAR(MAX)

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

                    OR
                    CAST(SERVERPROPERTY('Edition') AS VARCHAR) LIKE 'Evaluation%'
                )
                THEN 'REBUILD WITH (ONLINE = ON, FILLFACTOR = ' 
                    + CAST(@FillFactor AS VARCHAR(10)) + ') '
            ELSE 'REBUILD WITH (FILLFACTOR = ' 
                + CAST(@FillFactor AS VARCHAR(10)) + ') '
        END
        ELSE 'REORGANIZE '
    END +
    CASE
        WHEN pc.partition_count > 1 
            THEN ' PARTITION = ' + CAST(ps.partition_number AS NVARCHAR(MAX))
        ELSE ''
    END,
    PS.AVG_FRAGMENTATION_IN_PERCENT, GETDATE(), SUSER_NAME()
    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, 
                @Index_physical_stats_mode) 
            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 ix.name IS NOT NULL
    ORDER BY  ps.avg_fragmentation_in_percent DESC

    SET @Rows = @@ROWCOUNT

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

            EXECUTE sp_executesql @TSQLStatement

            SET @Counter += 1
            END
    END
    
  SET NOCOUNT OFF
END

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.

image

image

image

What you need

What you do not need

  • Azure knowledge
  • Azure account

Give SQL Azure Compatibility Assessment Service a go today.

Follow

Get every new post delivered to your Inbox.

Join 600 other followers

%d bloggers like this: