SQL server, SQLServerPedia Syndication

SQLCAT whitepapers.

SQLCAT stands for SQL Customer Advisory Team. The SQLCAT blog contains amazing posts by Microsoft’s SQL engineers back at Redmond. Reading this blog will give you a lot of  intrinsic information about SQL server.

One thing I love reading in particular is the whitepapers released by the team. They contain best practices and real life user scenarios. The steps and settings outlined in these whitepapers are offer deep insights and point to ponder about.

Just reading the abstracts gives be goose bumps, for eg: This white paper explains how Microsoft® adCenter implemented a Microsoft SQL Server® 2008 Analysis ServicesScalable Shared Database on EMC® Symmetrix VMAX™ storage. Leveraging TimeFinder® clones and Enterprise Flash Drives with the read-only feature of SQLServer 2008 Analysis Services allowed adCenter to dramatically scale out OLAP while maintaining SLAs and decreasing system outages. Note that  adCentre is what powers the Ads on Bing search.

Do visit the blog and enjoy. Here is a link to a consolidated index of all the SQLCAL whitepapers.

 

 

Standard
SQL server, SQLServerPedia Syndication

SQL 2005 installation oddity on multicore processors.

There is an issue with installing SQL 2005 on systems that have an odd (as in not even) number of cores/processors.  The conditions are as follows:

  • The ratio between logical processors and physical sockets is not a power of 2. For example, the computer has a single socket together with a triple-core processor.
  • The number of physical cores is not a power of 2.
  • The total number of CPUs is an odd number. For example, there are seven or nine CPUs.

Note that these issues are fixed in SP2, but the catch 22 is that SQL 2005 setup (non express editions) is not integrated. That is you need to install SQL 2005 first and then upgrade to SP2. So how will you get to SP2 if you cannot install SQL 2005 at all? There are three methods mentioned by Microsoft:

  1. Copy the SQL 2005 setup and SP2 files to local folders. Expand the SP2 package. Point the SQL 2005 setup binary to the expanded package and run setup. Run the SP2 patch. Link.
  2. Temporarily change the number of logical processors to one. Start>Run>Msconfig>BOOT.INI>Advanced options>/NUMPROC=1. Install SQL 2005 and upgrade to SP2. Change back processor count. Link.
  3. Run SQL 2005 setup. When the setup fails, you get a ‘Retry’ option. Replace the Sqlservr.exe and Sqlos.dll files in the BINN folder with the same files from the SP2 package. Retry. Link.

Note: I am not sure if the author meant ‘power of 2′ (1,2,4,8,16,32) or ‘multiple of 2′ (2,4,6,8,10). The reasoning being that I have installed SQL 2005 on a 24 core (physical) machine.

Standard
Other topics

IT infrastructure.

Infrastructure is important. In todays age, IT infrastructure is the backbone of any company. Proper infrastructure saves time, saves money, improves communication, eliminates redundancies, scales properly when required, and the list goes on.
Anyways, the reason of this post is because I am starting a new tag on infrastructure. I may also have to add this tag to some of my older posts.
To kick off, here is a link that was a treat for me. Courtesy of Obama’s AWS team.
Standard
SQL server

SSRS not working properly after upgrade to IE10

I was doing some work on SSRS (SQL Server Reporting Services) recently, and found that some of the features were not working as they should. I use IE (Internet Explorer) to do my report management functions, but for some reason, I was not able to see drop down lists or certain menu items.

I was checking my settings and then I suddenly remembered that an automatic update had kicked off on my laptop. I checked and found that this auto update upgraded IE from version 9 to 10. Now IE 10 for some reason does not support all the features of SSRS! I was using 2008 R2.

The good news is that there is an easy fix to this situation. Open up IE and hit the F12 key to open up the developer tools (also available under the tools menu). A developer window will open up on the bottom of the IE screen. Click on the Browser Mode: IE10 menu item and select IE 9. Your browser window will be refreshed and you should have all the features of SSRS back to how they were.

 

Standard
SQL server, SQLServerPedia Syndication

SQL backup mirroring (and splitting).

Did you know that you can mirror your SQL backups? This is good for those times when you need multiple backup copies. Like one local copy and another one on a network share.

You can even split backups over multiple files. If you have an IO bottleneck during backup, or if you are low on disk space, this backup splitting feature will come in handy. Below you can see my tests.

The backup file is 5.2 GB in size.

1)     Doing a normal backup:

backup database backupdb_dev to disk=’c:\dba\db_normal_bakup.bak’

Processed 656248 pages for database 'backupdb_dev', file 'Backupdb' on file 1.
Processed 2 pages for database 'backupdb_dev', file 'Backupdb_log' on file 1.
BACKUP DATABASE successfully processed 656250 pages in 67.109 seconds (76.397 MB/sec).

 

2)     Backuping up to two files on the same spindle:

backup database backupdb_dev

to disk=’c:\dba\db_bakup_spindle1_file1.bak’,

disk=’c:\dba\db_bakup_spindle1_file2.bak’

Processed 656248 pages for database ‘backupdb_dev’, file ‘Backupdb’ on file 1.

Processed 1 pages for database 'backupdb_dev', file 'Backupdb_log' on file 1.
BACKUP DATABASE successfully processed 656249 pages in 63.322 seconds (80.966 MB/sec).
Each backup file was 2.6 GB in size.

 

3)     Backing up to two files on two different spindles:

backup database backupdb_dev

to disk=’c:\dba\db_bakup_spindle1_file1.bak’,

disk=’f:\dba\db_bakup_spindle2_file1.bak’

Processed 656248 pages for database 'backupdb_dev', file 'Backupdb' on file 1.
Processed 1 pages for database 'backupdb_dev', file 'Backupdb_log' on file 1.
BACKUP DATABASE successfully processed 656249 pages in 62.775 seconds (81.671 MB/sec).

 

Theoratically, the backups

4)     Doing a backup and mirroring to the same spindle.

backup database backupdb_dev

to disk=’c:\dba\db_backup_mirror1.bak’

mirror to disk=’c:\dba\db_backup_mirror2.bak’

with format

Processed 656248 pages for database 'backupdb_dev', file 'Backupdb' on file 1.
Processed 1 pages for database 'backupdb_dev', file 'Backupdb_log' on file 1.
BACKUP DATABASE successfully processed 656249 pages in 95.500 seconds (53.685 MB/sec).

Both files were 5.2 GB in size. No surprise here. The backup took only 50% longer.

5)      Doing a backup and mirroring to different spindles.

backup database backupdb_dev

to disk=’c:\dba\db_backup_mirror1.bak’

mirror to disk=’f:\dba\db_backup_mirror2.bak’

with format

Processed 656248 pages for database ‘backupdb_dev’, file ‘Backupdb’ on file 1.

Processed 1 pages for database 'backupdb_dev', file 'Backupdb_log' on file 1.
BACKUP DATABASE successfully processed 656249 pages in 97.538 seconds (52.563 MB/sec).
Standard
SQL server, SQLServerPedia Syndication

SQL server edition comparisions.

I had created an old post about Enterprise only features of SQL server. As I found out interesting features, I added it to that old post. Today, I found out an MSDN link that has a feature comparison table which summarizes everything I was trying to capture in that post.

The link above is for the 2008 R2 version. You can switch between 2008, 2008 R2, and 2012.

As of 2012, the different editions of SQL server are:

  • Enterprise
  • Business Intelligence
  • Standard
  • Web
  • Express with Advanced Services
  • Express with Tools
  • Express

I thought the PDW was like the top most edition, but apparently, it is marketed as an appliance.

Standard