Friday, January 29, 2010

DBA Check List

Below is an outline of the items that need to be considered for a DBA role. Being a DB Developer/DBA I find this list to be a great starting point. Depending on the type of environment you have some of these items may be overkill or just not needed.

Link to full pdf version
  • General DBA Best Practices
    • Best Practices for Becoming an Exceptional SQL Server DBA
    • Day-to-Day
    • Installation
    • Upgrading
    • Security
    • Job Maintenance
      SQL Server Configuration Settings
    • Database Settings
    • Replication

  • High Availability Best Practices
    • General High Availability
    • Disaster Recovery
    • Backup
    • Clustering
      SQL Server Mirroring
    • Log Shipping

  • Performance Tuning Best Practices
    • Performance Monitoring
    • Hardware Performance Tuning
    • Indexing
    • SQL Server 2008 Compression Best Practices
    • SQL Server 2008 Data Collector Best Practices
      Best Practices for Resource Governor

  • Application Design and Coding Best Practices
    • Database Design
    • Queries and Stored Procedures
    • Transact-SQL
    • SQL Server CLR
    • XML

  • SQL Server Component Best Practices
    • SSIS
    • Reporting Services
    • Analysis Services
    • Service Broker

Source article

Monday, January 25, 2010

XML Web Part/ RSS / Content Query Web Part Replacement in Windows SharePoint Services

Part 1
  • Add an XML Web Part to a page.
  • Edit the XML Web Parts properties
  • In the XML Link field, enter the URL of the RSS feed.
  • In the XSL Link field, enter the URL of an XSL file that you've written to transform the feed, or alternatively, in the “XSL Editor“, enter the following XSL code:

<?xml version="1.0"?>

<xsl:stylesheet xmlns:xsl="http://www.w3.org/1999/XSL/Transform" version="1.0">

<xsl:template match="/">
<table width="100%" border="0" cellspacing="0" cellpadding="1">
<tr>
<td class="tablebg"><table border="0" cellpadding="3" cellspacing="0" bgcolor="#FFFFFF">
<tr>
<td><img src="images/spcacer.gif" width="163" height="1" /></td>
</tr>
<tr valign="middle">
<td><h2>Recent Articles</h2>
</td>
</tr>
<xsl:for-each select="rss/channel/item">
<tr>
<td><a href="{link}"><xsl:value-of select="title"/></a></td>
</tr>
</xsl:for-each>
<tr>
<td><a href="/allarticles.aspx">More...</a></td>
</tr>
</table>
</td>
</tr>
</table>
</xsl:template>
</xsl:stylesheet>

Part 2
It wasn't too long ago that I was trying to get my favorite Web Part (Content Query Web Part) to pull an RSS feed form a SharePoint list. Web Part was giving me a lot of grief, repeatedly giving me the error:

Cannot retrieve the URL specified in the XML Link property. For more assistance, contact your site administrator.

It took me a while to piece together that this Web Part does NOT run in the context of my given SharePoint credential when it comes to fetching this RSS. In fact I don't know of ANY RSS able Web Parts that support authenticated feeds.

As a work around I had to enable Anonymous Access for the given list. I was quite surprised when after some research I discovered that SharePoint allows you to turn on annonymous access for any of the following:

  • Everything on a site (ie. 3 sites out of a site collection)
  • Any combination of Lists and Libraries on a given site
  • No anonymous access at all

For my needs I wanted to turn it on for just ONE list (the one I wanted to expose and consume the RSS feed for). Here's how to do it.

Enabling Anonymous Access for a Single List

  1. The first thing you need to do is turn on anonymous access for the web application, this is done through the Central Administration UI. Open up the Central Administration web page, go to the Application Management tab and click on Authentication Providers under Application Security (see below).
  2. Now that this application allows anonymous access we need to turn it on for the given site. Go to the site you want to turn on anonymous access on, navigate to Site Actions->Site Settings->Modify All Site Settings. From here click on Advanced Permissions. Open up the Settings menu on the tool bar and then click Anonymous Access. Finally choose (for this example) "Lists and libraries". We're going to turn on anonymous access for exactly one list.
  3. Finally go to the list you want to turn on anonymous access for and in the List Settings, Permissions for this List and then expand the Settings menu and click Anonymous Access. From here you simply select the rights you want to give anonymous users for this list. In this case we simply choose View Items.
  4. That's it, that RSS feed should now be working!

Friday, January 22, 2010

Synchronise SQL table with SharePoint List

I wanted to share how I was able to get synchronization of a SharePoint List through
SSIS. Below I have provided an outline of the steps I took.

  • First, I had an OLE DB Source, then a Data Conversion to convert those strings and
    then Sort it on its key.
  • Second, I had the SharePoint List Source that has to be synchronized, Data Conversion
    to match the key type and then Sort it on the key.
  • Third, Merge Join this 2 sorted data using Full Outer Join.
  • Fourth, add a Conditional Split with the ff output:
    • Output Name: Create Condition: ISNULL( ID )
    • Output Name: Delete Condition: ISNULL([Your Key])
    • Default output name: Update
  • Lastly, Add 3 SharePoint List Destination for Create Delete and Update with the
    ff note:
    • For Create, do not map the column "ID" and Batch Type should be "Modification"
    • For Update, map the column ID to the ID you got from merging, Batch Type should
      be "Modification"
    • For Delete, you only need to map the ID, and Batch Type should be "Deletion"

    How the layout looks, sometimes a pictures can help:


Free Tools for the SQL Server DBA

This article has come across my desk serveral times so I want to make sure everyone is aware of it. The article outlines some key tools that can help perform a DBA/Developer role.

http://www.sqlservercentral.com/articles/Tools/64908/

Wednesday, January 20, 2010

Excel 2007 connection to SSAS 2008: "The message or signature supplied for verification has been altered

I have seen some issues with Vista/Office2007/Server2008/Sql2008/SSAS cubes connections. An error message when trying to connect to a SSAS cube. The error message reads, " The message or signature supplied for verification has been altered" It seems that "AES algorithm" is not a default in Windows Server 2003 but is a default in Windows Server 2008.

Installed the hotfix and the issue has been resolved:
http://support.microsoft.com/kb/969083

Link to similar issues:
http://social.msdn.microsoft.com/forums/en-US/sqlanalysisservices/thread/f285e2dd-bd3f-455c-b8b3-808d73a25a75/

Friday, January 15, 2010

Applied Microsoft SQL Server 2008 Reporting Services

Applied Microsoft SQL Server 2008 Reporting Services

Repeat Header / Keep Header Visible in Tables in RS 2008

You selected "Repeat header rows on each page" or "Keep header rows visible while scrolling" in the tablix properties dialog, but it doesn't seem to work as expected? You might want to try the following four steps if you are using a "table"-style layout:


  1. in the grouping pane, click on the small triangle and select "Advanced Mode" to show static members:
    Grouping pane in advanced mode
  2. verify that you have the properties grid turned on (in BI Development Studio use F4, in Report Builder go to the "View" tab and select "Properties")
  3. select the corresponding (static) item in the row group hierarchy
  4. in the properties grid:
    - set KeepWithGroup to After
    - set RepeatOnNewPage to true for repeating headers
    - set FixedData to true for keeping headers visible

Reference link:

Thursday, January 14, 2010

Automated Index Maintenance

I recently went to the OPASS meeting and watched a demo on Automated Index Maintenance. I am currently in the process of testing the logic on a dev box but wanted to share the link. I will follow up with updates through the testing process.

Link