Blog Home  Sign In RSS 2.0 Atom 1.0 CDF  

  def Softwaremaker() :
         return "William Tay", "<Challenging Conventions />"

  knownType_Serialize, about = Softwaremaker()
 

 Tuesday, August 16, 2011

Time to do my sporadic updates on this blog again. I have been making quite a few presentations on Business Intelligence recently and my next presentation will be together with Panoroma on the 17th August 2011 in Microsoft Singapore. Title of the presentation will be "Generate Actionable Insights and Increase Efficiency with Intuitive Business Intelligence"

Necto BI

With my usual presentation style of decorating the business of analytics with an interactive storyboard, I will be dotting the presentation with some bits on SQL Server "Denali" as well, highlighting on Project Cresent and PowerPivot v2. It should be a fun event. Come on down if you are around.

Monday, August 15, 2011 11:53:58 PM (Malay Peninsula Standard Time, UTC+08:00)  #    Disclaimer 
  • Blog reactions

  •  Tuesday, May 18, 2010
    Gosh, it has really been long since this blog saw some action and probably need some attention (weeding, trimming, re-planting, etc)

    After a long hiatus, I will be speaking again at a big event in Marina Bay Sands Singapore for our Future Of Productivity Launch on the 26th May 2010, keynoted by none other than Mr CEO - Steve Ballmer himself. Incidentally, this is also to celebrate our Microsoft Singapore 20th Anniversary. Agenda is here.

    I will be touching base on SQL Business Intelligence (BI), focusing on our latest and neatest trick in Microsoft Office 2010 / SQL Server 2008 R2 thus far - The Microsoft PowerPivot. I have no doubts that PowerPivot will further propel our SQL BI story further ahead in the very competitive BI landscape.

    If you have any doubts, do come by and I hope that we can set those to rest. The Business Intelligence breakout is in Track 3.

    It will be a very visible, high impact event, well attended by many C-levels, media and press and in a spanking shiny brand new venue that has only just opened its doors.

    Register early. See you there.

    Monday, May 17, 2010 9:37:22 PM (Malay Peninsula Standard Time, UTC+08:00)  #    Disclaimer 
  • Blog reactions

  •  Friday, January 23, 2009

    Yes, really. No fluff. C'mon - has there ever been on this blog.

    SQL Server 2008 really does go well with our Windows Server 2008, especially when you are replications across Wide Area Networks (WAN). Microsoft's internal IT, whom is managing global MSDN sites, did this project and concluded with this study: Geo-Replication Performance Gains with Microsoft SQL Server 2008 Running on Windows Server 2008.

    This is a highly-used reference since being published a couple of months back. Note the case study numbers shows at least 100x improvement in a few cases. I quote some key content from the source.

    openquotes.png

    The Publisher and Distributor databases for the MSDN applications are located in a data center in Tukwila, Washington. The Subscriber databases are hosted in separate facilities in Washington and California. As shown in Table 1, the team calculated baseline latency of four milliseconds between the MSCOM Ops data centers in Washington; a maximum of 23 milliseconds between facilities in Washington and California; and 150 milliseconds from Tukwila, Washington to Dublin, Ireland.

    Data Center

    Tukwila

    Quincy

    Santa Clara

    Blueridge

    Dublin

    Tukwila, Washington

    1 ms

    4 ms

    19 ms

    77 ms

    150 ms

    Quincy, Washington

    4 ms

    1 ms

    23 ms

    68 ms

    146 ms

    Santa Clara, California

    19 ms

    23 ms

    1 ms

    79 ms

    156 ms

    Blueridge, Virginia

    77 ms

    68 ms

    79 ms

    1 ms

    84 ms

    Dublin, Ireland

    150 ms

    146 ms

    156 ms

    84 ms

    1 ms

    SQL Server 2005 Running on Windows Server 2003
    The team’s previous attempts to replicate data between Microsoft data centers located in Redmond, Washington, and Virginia by using SQL Server 2005 running on Windows Server 2003 helped reduce latency to a certain extent. But, to account for the real-world demands of the data-centric applications targeted for geographic redundancy, the team decided to conduct further tests on this platform by using the same data center locations.

    The following describes the methodology that the MSCOM Ops team used to evaluate replication performance of SQL Server 2005 running on Windows Server 2003.

    • Tests were conducted between data centers located in Redmond and Virginia—a distance of approximately 3,000 miles.
    • Baseline testing was conducted for transactional replication of both push and pull subscriptions.
    • Push subscription model was used for initial test and pull subscription model was used for all subsequent testing.
    • Rows in database tables were 1K in length.
    • Rows were inserted in increments of 1,000, 10,000, and 100,000.
    • Each test pass was run three times, and run times were averaged.
    • The distribution database was housed on the Publisher in the replication topology for all tests.
    • Tests included “live” data from the MSDN database, including two tables that had schemas identical to those used in production. 
    • Based on a scenario in which the database system was separated into reads and writes, latency was determined during testing to be as high as four seconds.

    Windows Server 2008 and SQL Server 2008
    In late summer of 2008, the MSCOM Ops team initiated testing of the updates to the Database Engine in SQL Server 2008 alongside the improved TCP/IP stack in Windows Server 2008.

    The following describes the methodology that the MSCOM Ops team used to evaluate replication performance of SQL Server 2008 on Windows Server 2008.

    • Tests were conducted between data centers located in Redmond and Virginia—a distance of approximately 3,000 miles.
    • Baseline testing was conducted for both push and pull subscriptions with transactional replication.
    • Push subscription model was used for initial test and pull subscription model was used for all subsequent testing.
    • Rows in database tables were 1K in length.
    • Rows were inserted in increments of 1,000, 10,000, 100,000 and 1,000,000.
    • Each test pass was run three times, and run times were averaged together.
    • The distribution database was housed on the Publisher in the replication topology for all tests.
    • Live data from the MSDN database, including two tables with identical schemas used in production, were included in tests. 
    • The agent profile changes for testing on SQL Server 2008 and Windows Server 2008 were made by using 1,000,000 records, equal to 15.5 gigabytes (GB) of data transfer.

    Test Results Comparison
    Testing showed that using transactional replication with SQL Server 2008 running on Windows Server 2008 dramatically outperformed SQL Server 2005 running on Windows Server 2003. As illustrated in Table 2, the most substantial performance gains occurred when the Publisher and Subscriber were both running SQL Server 2008 on Windows Server 2008. 

    Testing also showed that the scope of the performance gains correlated with the type of replication and the type of data. Push subscription replication of character data with SQL Server 2008 running on Windows Server 2008 yielded a 104 percent increase over SQL Server 2005 running on Windows Server 2003, and pull subscription replication of the same data yielded a 1,298 percent gain. The team noted that changing the PacketSize or ReadBatchSize parameters in the Replication Distribution Agent (http://msdn.microsoft.com/en-us/library/ms147328.aspx) profile during testing did not significantly affect performance; these changes resulted in a savings of less than 60 seconds for replicating 1,000,000 rows of varbinary (max) data equal to 15.5 GB of data moved.

    It should be noted that not all of the disk performance counters were relevant during testing, as the partitioning of the disks to support dual boot and two instances of SQL Server on each partition rendered the disk performance counters questionable. The key take-away from the disk counters is that the distribution database files are “hot spots” during replication, and that the process is input and output intensive. Disk-queue length averaged 150 for the Publisher with the test hardware.

    MSCOM Ops is continuing to drill down into various parameters and variables in subsequent testing to further develop guidance and best practices. However, based on the substantial performance gains witnessed in the initial round of testing, the team believes it is possible to build a geographically redundant, fault-tolerant database system, including a high read and write version.

    Performance Indicators

    Test Scenarios

    SQL Server 2005 on Windows Server 2003 (A)

    SQL Server 2008 on Windows Server 2008 (B)

    Performance Gains or Losses

    [(A-B)/B]*100

    CPU Utilization (%)

    All

    15%

    15%

    0%

    Memory

    All

    99%

    99%

    0%

    Push Replication

    1-GB

    1,000,000 1k character records

    226.12 (minutes)

    110.42 (minutes)

    104.78%

    Pull Replication

    1-GB

    1,000,000 1k character records

    174.87 (minutes)

    12.5 (minutes)

    1298.96%

    Linked Server

    10-MB

    10,000 1k character records

    107.6 (minutes)

    113.6 (minutes)

    –5.28%

    Push Replication

    112-MB

    100,000 varbinary (max) records

    247.07 (minutes)

    59.13 (minutes)

    317.84%

    Pull Replication Records

    112-MB

    100,000 varbinary (max) records

    223.18 (minutes)

    1.95 (minutes)

    11345.13%

    Snapshot Replication

    11.3-GB 10,100,000 1k records

    Not tested

    22.75 (minutes)

    Comparison not available

    Friday, January 23, 2009 12:59:40 AM (Malay Peninsula Standard Time, UTC+08:00)  #    Disclaimer 
  • Blog reactions