The scope of this document is to show the process of mirroring a SQL Server 2008 r2 database without a witness server, perform a manual failover, and then recover the principal database. This document will not show how to install, manage, and setup basic SQL Server 2008 R2 services.

Post Installation Setup

After both the SQL Server 2008 R2 installations are completed set the “Log On As” accounts to your domain service account in the SQL Server Configuration Manager for both the Principal and Mirror Servers.  This account should the same for both installation’s, and should not be a domain administrator account. 

Pre-Mirror Configuration

In order for the principal database to update the mirror database the mirror database needs a full and transactional log restore of the principal database. The mirror database also needs to be restored the NO RECOVERY option.

*If you plan on using the Mirror server as a failover box be sure to set up any database, user accounts ahead of time. Be sure the mirror server is and their databases are in a working state before setting up the mirror.

Backup Principal

To perform a FULL backup of the principal database right click the database and select; Tasks > Backup. Be sure to select FULL under source >> backup Type.

Restore Mirror

When restoring the mirror database it’s important to restore the DB using the NO RECOVERY option.  If you don’t use the NO RECOVERY option the mirror will fail after clicking the start mirror button later in the document. To restore the DB right click the DB and select Tasks >> Restore >> Database.  After selecting your sources click the options tab and make sure the “RESTORE WITH NO RECOVERY” option is checked.

To restore the transaction log right click the DB and select Tasks >> Restore >> Transaction Logs.  After selecting your sources click the options tab and make sure the “RESTORE WITH NO RECOVERY” option is checked. 

Create the Mirror

Once the above pre-requisites are setup the mirror can be created fairly easily.  Right click the DB and select Tasks >> Mirror. This will open the Mirroring Security Wizard. Click the Configure Security button.   Select “No” when prompted to include a witness server.   Select the server instance, set an endpoint name, click Encrypt data through the end point and click next.  In the Mirror Server Instance click connect and use the familiar “Connect to Server” dialog box to connect to the Mirror server.   Then set an endpoint name, click Encrypt data through the end point and click next. In the service accounts text boxes use the service accounts we used in the post installation setup.  DOMAINNAME\USERNAME. Click next, finished, and the start mirroring.

Manual Failover

In the event of a failure to your principal server your mirror server is useless until you perform some basic functions, and run some scripts. The scripts below will put your DB in ready state.

Turn mirroring off on the mirror DB.

ALTER DATABASE <<DB NAME>> SET PARTNER OFF

Set the recover mod the DB to RECOVERY

RESTORE DATABASE <<DB NAME>> WITH RECOVERY

Fix and orphaned users if you are using SQL authentication

EXEC sp_change_users_login ‘Auto_Fix’ , ‘<<username>>’

Recovery

Once your Principal server is back online you will want to update that server with the latest transactional data, and then recreate the mirror.

Set the Mirror DB in read only mode.

ALTER DATABASE <<DB NAME>> SET READ_ONLY

Backup the Mirror DB and restore it to the Principal DB, and start the process of creating a mirror.

Living in Rochester NY the home of Eastman Kodak, the switch from Film to Digital Photography is quite apparent. However you don't have to live in my frozen tundra to notice the trend to digital. Who do you know that uses film anyway?

A few years ago I lost a lot of my son's baby, vacation, and birthday pictures due to disk failure. Of course I should have known to backup since I work in IT dammit! However I never got around to backing up my local disk drives on the family PC.

The next day I made some purchases!

• Used Compaq Proliant DL Server (800MHZ / 2GB RAM)
• Sentry Safe Fire-Safe Waterproof DATA STORAGE CHEST
• MX-1 Actively Cooled Hard Drive Enclosure with a 1 TB Seagate
• Maxtor One-Touch 4 Mini Hard Drive from Maxtor.

My plan was to install a server in my wiring closet to backup and share my audio, photo, and video files.

I decided to install Ubuntu 8.10 on the prolient server and share some folders using samba. This would allow my wife to download all her photos to a shared drive and allow me to share my audio and video files to my hacked xbox and tivos!

If you have any questions on setting up SAMBA on Ubuntu check out the documentation on their site here.

After setting up the shares using the smb.conf and mapping drives from the family pc's I tackled the redundancy problem. Since all my data was still one drive, in the event of a disk failure I would lose all my important data.

Initially I was going to purchase 2 MX-1 1 TB drives and mirror them, however I felt like that was bit overkill and pricey. I don't need real time redundancy for my home network; however a nightly backup would be perfect. I could live if I was out 1 days worth of backups if my drive failed.

For the backup device I choose a Sentry Safe Fire-Safe Waterproof DATA STORAGE CHEST. I chose this safe because my wiring closet is in my basement (water) and in the event of a house fire my disk drive would rremain intact...

I first plugged in the USB drive to the safe, and then into the server. I made an entry in the /etc/fstab so the new drives would mount on boot up.

I wrote a quick shell script using a copy command to copy my data nightly to the fireproof waterproof drive. I then added the script to crontab to run every day at midnight.Currently my only gripe is that if the cron job fails or doesn't run for some reason I am not notified. I think there is away to add notifications upon failure but I have not had the time to look into it.

There are a ton of technical details in the article that I just glossed over like;

• Add new physical usb drives to linux
• add the cron job
• copy arguments to allow to copy recursively
• copy arguments to only copy newer files
• copy arguments to handle any yes|no prompts

If you have any suggestions or related technical questions please send them via twitter to http://www.twitter.com/adesalvo

I recently attended the 7 Secrets to High Google Rankings seminar at the RMSC. The seminar was sponsored by the AMA Rochester chapter and was presented by Stephan Spencer, President of Netconcepts.

Stephen gave an excellent presentation of seven SEO concepts to better optimize a web site. One of the ones that really stuck out for me was the 301 Redirect. A 301 Redirect is a search engine friendly way to move pages without forfeiting hard earned search engine placement. Redirecting your Domain foo.com to www.foo.com is also a common use for a 301 redirection. What makes a 301 important is it merges all your searched and indexed pages into one domain, thus increasing your PageRank.

After the seminar I went back and tested one of the corporate sites I manage using a 301 redirect checker only to find out that I was not using this SEO method.

Below is the code I used to implement the 301 Redirect. I know there are many ways to do this, DNS, URL_REWRITE, etc, but the easiest most controlling method was to use code. This would allow me to manage from a CMS what domains will be redirected and where. There is a future business requirement to internationalize the site using different languages and domains. Another requirement was to maintain the state of the Query String. Below is the c# code I placed on a web control that is present on every page of the site. A more optimal way to implement this would be to extend the System.Web.UI.UserControl on every page and overide the page_load event to containg the below code.

    
protected void Page_Load(object sender, EventArgs e)
  {

      /* 301 redirect with query string support.*/

      string c_absUri = Request.Url.DnsSafeHost;
      string c_absPath = Request.Url.AbsolutePath;

      // shorthand to set query string value
      string c_queryString = Request.QueryString.ToString().Length > 0 
             ? Request.QueryString.ToString() : String.Empty;

      //Only Redirect if Doain is not int he NoRedirect List
      if (!isNoRedirectUrl(c_absUri))
      {
          // eventualy I would create a hash to lookup 
          //the redirect instead of hardcoding it
          string newUrl = "http://www.foo.com" + c_absPath;

          //is querystring is not empty Append to redirect url
          if (!c_queryString.Equals(string.Empty))
          {
              //add query string
              newUrl += "?" + c_queryString;
          }

          Response.Status = "301 Moved Permanently";
          Response.AddHeader("Location",newUrl);
      }

  }

  public static bool isNoRedirectUrl(string Url)
  {
      // This could be an externally managed list - CMS maybe?
      string[,] a_noRedirectUrl = new string[,] {
          
              {"ext.foo.com"},
              {"dev.foo.com"},  
              {"beta.foo.com"},
              {"localhost"},                                        
              {"foo.co.uk"}                           
          };

      bool isNoRedirectUrl = false;
      foreach (string s in a_noRedirectUrl)
      {
          if (Url.Contains(s))
          {
              isNoRedirectUrl = true;
          }
      }

      return isNoRedirectUrl;

  }

A few months ago I was designing a custom CMS for my current employer. The system will primarily be used to manage there retail products and all the attributes that relate to them. EX: color, size, retail location, etc.

In the current system these attributes, like any poorly manage application, have spun out of control. The only way to clean up these attributes was for me to write some custom SQL and pass the results off to marketing to mark up, correct and send back to my intern to make the SQL changes.

To avoid this tedious task (I like my interns), I decided to build in a viewable/clickable relationship between each attribute and its products in the new CMS. Considering its user base, I wanted to make it as easy as possible to view this parent child relationship.

Initially we just added a VIEW button embedded in the gridviews that would take you to a page that lists which products relate to it’s clicked attribute. From there you could click again to manage a specific product within the list. This wasn’t an optimal solution but solved the problem.

While looking at the page I thought an embedded hoverMenuExtender control that popped up another gridview of each of the attributes related products would allow for a faster and easier workflow for managing products and attributes.

So below is the code that I developed. It’s strictly asp.net no codeBehinds, using the business logic DAL pattern for the Data Access as well as generics.

<asp:ObjectDataSource ID="odsFeatures" runat="server" OnSelected="ObjectDataSource1_FeatureSelected"
      OnDeleted="ObjectDataSource1_FeatureDeleted" UpdateMethod="UpdateFeature" InsertMethod="InsertFearure"
      DeleteMethod="DeleteFeature" DataObjectTypeName="com.safes.Feature" TypeName="com.safes.FeatureDAL"
      SelectMethod="SelectFeature">
   <SelectParameters>
      <asp:ControlParameter ControlID="ddlCategories" Name="categoryID" PropertyName="SelectedValue"
               Type="Int32" />
   </SelectParameters>
</asp:ObjectDataSource>
<asp:GridView ID="GridView2" runat="server" Width="100%" CssClass="smallCopy" DataSourceID="odsFeatures"
      AutoGenerateColumns="False" DataKeyNames="FeatureId">
  <Columns>
      <asp:CommandField ButtonType="Button" ItemStyle-Width="95" ShowEditButton="True"
              ShowDeleteButton="True" ControlStyle-CssClass="cmsButton" CausesValidation="false" />
      <asp:BoundField DataField="Name" ItemStyle-Width="600" ControlStyle-Width="600" HeaderText="Name"
              SortExpression="Name" />
      <asp:TemplateField HeaderStyle-HorizontalAlign="Center" ItemStyle-HorizontalAlign="Center"
              HeaderText="View">
          <ItemTemplate>
              <asp:Panel ID="pnlFeatureTarget" runat="server">
                  <asp:Button ID="btnFeatureView" CausesValidation="false" OnClientClick="return false;"
                          ToolTip='<%# Eval("FeatureId") %>' Text='View' CssClass="cmsSelectButton" runat="server" /></asp:Panel>
              <ajaxToolkit:HoverMenuExtender ID="hmeFeature" runat="Server" HoverCssClass="popupHover"
                      PopupControlID="pnlFeatureView" PopupPosition="Left" TargetControlID="pnlFeatureTarget"
                      PopDelay="100" />
              <asp:ObjectDataSource ID="ObjectDataSource4" runat="server" SelectMethod="getSafesByFeatureId"
                       TypeName="com.safes.SafeFactory">
                  <SelectParameters>
                      <asp:ControlParameter ControlID="btnfeatureView" Name="FeatureId" PropertyName="tooltip"
                              Type="Int32" />
                 </SelectParameters>
             </asp:ObjectDataSource>
             <asp:Panel ID="pnlFeatureView" runat="server" CssClass="popupControl">
             <asp:GridView EmptyDataText="No Safes Found" EmptyDataRowStyle-BackColor="#EEEEEE"
                EmptyDataRowStyle-ForeColor="black" EmptyDataRowStyle-CssClass="smallCopy" ID="FeatureGridView2"
                runat="server" AutoGenerateColumns="False" DataSourceID="ObjectDataSource4" CssClass="smallCopy cmsAlignRight"
                Width="0" BorderColor="#333333" BorderStyle="Solid" BorderWidth="1px" CellPadding="3"
                          GridLines="Vertical">
                      <Columns>
                          <asp:HyperLinkField DataTextField="ModelNo" HeaderText="Model" SortExpression="ModelNo"
                                  ItemStyle-HorizontalAlign="center" DataNavigateUrlFields="ModelNo" DataNavigateUrlFormatString="editSafeRedirect.aspx?m={0}" />
                      </Columns>
                     <RowStyle BackColor="#EEEEEE" ForeColor="Black" />
                      <SelectedRowStyle BackColor="Gray" Font-Bold="True" ForeColor="White" />
                      <PagerStyle BackColor="#999999" ForeColor="Black" HorizontalAlign="Center" />
                      <HeaderStyle BackColor="#D23127" Font-Bold="True" ForeColor="White" CssClass="mediumCopy" />
                      <AlternatingRowStyle BackColor="Gainsboro" />
            </asp:GridView>
            </asp:Panel>
          </ItemTemplate>
      </asp:TemplateField>
  </Columns>
  <RowStyle BackColor="#EEEEEE" ForeColor="Black" />
  <SelectedRowStyle BackColor="Gray" Font-Bold="True" ForeColor="White" />
  <PagerStyle BackColor="#999999" ForeColor="Black" HorizontalAlign="Center" />
  <HeaderStyle BackColor="#D23127" Font-Bold="True" ForeColor="White" CssClass="mediumCopy" />
  <AlternatingRowStyle BackColor="Gainsboro" />
</asp:GridView>

Below is a screen shot of the final rendered product. As you can see you can select a feature and get its attributes, and from there you can hover over the view button on the attribute to see what products belong to its respected attribute. I also added a link to the product for easy editing.



One of the things I struggled with was how to set the select selectparameters of the hovering objectdatasource/gridview. What I ended up doing is setting the tooltip parameter of the button to the featuredID of the attribute. Then I referenced the tooltip parameter using the controlParamter. From there I could write a custom query to get the products associated with the attribute.

It's not a Game! It's an online 3-D Experience brought to you buy Big Blue (IBM).

Last week our CFO dropped an article on my desk regarding second life. I let it sit for a day or 2 while I was catching up on some work until the Director of IT stopped by and asked if I signed up yet. His explanation sparked my interest so I immediately signed up.

I am still a little unclear how or why this has not received the attention it deserves. Second Life is an online 3D social networking site on steroids.

This is second life's explanation:

Second Life is a 3-D virtual world entirely built and owned by its residents. Since opening to the public in 2003, it has grown explosively and today is inhabited by a total of 3,550,486 people from around the globe.

From the moment you enter the World you'll discover a vast digital continent, teeming with people, entertainment, experiences and opportunity. Once you've explored a bit, perhaps you'll find a perfect parcel of land to build your house or business.

You'll also be surrounded by the Creations of your fellow residents. Because residents retain the rights to their digital creations, they can buy, sell and trade with other residents.

The Marketplace currently supports millions of US dollars in monthly transactions. This commerce is handled with the in-world unit-of-trade, the Linden dollar, which can be converted to US dollars at several thriving online Linden Dollar exchanges.

I hope to learn more about the IDE for creating New Actions and 3-D Items.