Tuesday, September 14, 2010

BDC in SharePoint 2010 Foundation for a Department Level Database

One big project we are workign on currently is using the BDC to surface our company's EDI project to end users in a familiar way to have them enter confirmation of new orders from clients quickly. Before we got too deep into this we were looking at various options and ways to experiment and learn the technology. One thing we did not have was a tracking system of our servers and their assigned IP addresses and assigned roles. Here is a diagram of the testing database that I designed and wrote.

The steps to create a fully CRUD enabled application that looks like a SharePoint liust but has all the benefits of being backed by a SQL database are as follows:

1. Design your database as you normally would.
2. Create a set of views that the users will access to see the data.
3. Use INSTEAD OF triggers to make the views updateable.
4. Create External Content types to connect to the SQL data.
5. Create External Lists to display the data in SharePoint 2010.
6. Add the correct permissions to the External Content Type in the SharePoint BDC management in Central Admin.
7. Test that you can add, update, and delete items in the lists.
8. Write a webpart to supply the users with a richer, easier to understand GUI.

In my next post I hope to detail the steps for adding a CRUD enabled Silverlight web part to work with the External Lists.

Monday, September 6, 2010

PBM and ExecuteWQL()

Another item of interest in studying for the SQL Server 2008 upgrade exam. This is a really interesting feature that could be combined with another monitoring system to give you a really complete view of your SQL servers every morning.

http://msdn.microsoft.com/en-us/library/aa394606(VS.85).aspx
http://msdn.microsoft.com/en-us/library/bb895209.aspx
http://sqlserverpedia.com/blog/sql-server-bloggers/executewql-in-policy-based-management/

Sunday, September 5, 2010

SQL Server 2008 Extended Events

In my studies for the MP: DBA SQL Server 2008 I cam accross a topic that was not included in any of the books that I have been using. It's called Extended Events and is used in diagnosing things like dealocks and high CPU utilization. Read the MSDN article.

Sunday, August 29, 2010

Creating a Basic CLR Stored Procedure Using C# in Visual Studio 2010


I was recently confronted with a problem managing a database of MAC addresses. Our company purchased a block of MACs and our team was tasked with the problem of creating the database and associated services for managing the MAC range.

The problem was that there did not seem to be any way to easily convert from decimal integer types to hexadecimal in T-SQL. One possible way to solve this problem is to write a simple C# CLR stored procedure as this type of conversion is pretty trivial in languages like C#.

This tutorial assumes you will be adding the CLR stored procedure to the AdventureWorks 2008 database and that you will be using SQL Server 2008 R2.


Step 1 – Enabling CLR in SQL Server

Run sp_configure to enable CLR on your test SQL Server Instance.


EXEC sp_configure 'clr enabled', 1
GO
RECONFIGURE
GO

Friday, August 20, 2010

Refocusing on Studies and Blogging

It has been a crazy couple of months. I have actually gotten my dream as a SQL Server and SharePoint admin/developer. I cannot explain how happy this has made me and I have been working hard to fill in the gaps in my knowledge. Now that I'm more settled in, I'm trying to get back on track as far as my studies go and take the dang upgrade exam for the MCITP DBA 2008. I know I am pretty much ready for the test. I think with a good review of mirroring and replication I could pass without really doing any prep. So I am hoping to schedule this exam as soon as possible.

I use certification to both focus and measure my studies on a given topic. There are a couple of other areas that I need to explore and I am going to pursuing the following certifications in the next 6 months as well:

  • MCITP: SharePoint 2010 Administrator (70-677 | 70-668)
  • MCTS: SQL Server 2008, Business Intelligence Development (70-448)

I'm really hoping to be able to accomplish these quickly and be able to share some details about my studies and some of the very interesting things that I have going on at work.

Tuesday, August 17, 2010

The Power of Stupid...

Stupid mistakes can just make you feel like an idiot sometimes. At my new job I wasted nearly 2 hours trying to figure out why some very simple C# code was not working properly. The idea was to execute the code inside of an SSIS package so that it updates a WSS 3.0 Issue Tracker list. It was simple stuff, but it took me far too long to realize that I was logged in on the wrong domain.


It was not until I wrote the same code in PowerShell that I actually got an error that made sense: “Access Denied.” I mentioned this to my boss who asked me which domain I was logged in under. Then I typed whoami and quickly realized what a dummy I had been.

Saturday, August 14, 2010

Chinook Database on CodePlex

The other day I was browsing around CodePlex and found a wonderful tool for learning SQL Server or for integrating your applications with SQL Server. The Chinook database, while small, has some cool appeal to it. It has all the major components you need. You can work on reporting for invoicing, sales, inventory, just about anything. You can download it at http://chinookdatabase.codeplex.com/