A couple of days ago a new article went live over on SearchSQLServer.com where I dig into setting up Master Data Services. This is the first in a series of articles which will be either 2 or 3 articles long where I’m going into MDS in some detail.
The first article “Getting started with Master Data Services in SQL Server 2008 R2” is the one which went up, with the others coming in the following months.
God that sounds boring doesn’t it.
Anyway, I was installing SQL 2008 CU 6 for SP 1 the other day and I was greeted with the dreaded “some process is running which will cause your SQL Server to reboot after install” screen. The app which was running was fshost.exe which is part of the Full Text Search process.
So I naturally stopped the SQL Full Text Service, gave it a few minutes and the process was still running. As this was a production server I didn’t want to just kill the process (the only thing which uses Full Text on the server is the MS CRM database, and we don’t even use MS CRM yet).
So I moved forward and opted for the reboot. After the installer finished however there was no reboot prompt. Apparently when the SQL Service went down for patching the fshost.exe also went down so there was no reboot needed.
So if you are installing a CU, and it tells you that you’ll need to reboot because of fshost.exe you probably won’t need to reboot.
During my SQL Service Broker sessions I talk about using SQL Service Broker as a replacement for SQL Replication. But I’ve never really documented how to do this other than bits and pieces here and there. So here I’m going to try and spell out how I put a system like this together for an auto finance company that I used to work for.
Why Service Broker
Before I get into the code a little reasoning behind why we went with SQL Service Broker over SQL Replication is probably good to talk about.
The main reason is that we wanted to be able to do ETL on the data as is moved from the production OLTP database to the reporting database. We also wanted the ability to easily scale the feed from one to many reporting databases with the reporting servers being in different sites.
Below you’ll find some SQL Code that we’ll use to create some sample tables. In our OLTP database we have two tables LoanApplication and Customer, and in our Reporting database we have a single table LoanReporting. As data is inserted or updated in the LoanApplication and Customer table that data will then be packaged up into an XML document and sent to the reporting database. In the case of my sample code here everything is on one server, but the databases could be easily enough moved to separate servers.
IF EXISTS (SELECT * FROM sys.databases WHERE name = 'Sample_OLTP') DROP DATABASE Sample_OLTP GO IF EXISTS (SELECT * FROM sys.databases WHERE name = 'Sample_Reporting') DROP DATABASE Sample_Reporting GO CREATE DATABASE Sample_OLTP CREATE DATABASE Sample_Reporting GO ALTER DATABASE Sample_OLTP SET NEW_BROKER ALTER DATABASE Sample_Reporting SET NEW_BROKER GO ALTER DATABASE Sample_OLTP SET TRUSTWORTHY ON ALTER DATABASE Sample_Reporting SET TRUSTWORTHY ON GO USE Sample_OLTP GO CREATE TABLE LoanApplication (ApplicationId INT IDENTITY(1,1), CreateTimestamp DATETIME, LoanAmount MONEY, SubmittedOn DATETIME, ApprovedOn DATETIME, LoanStatusId INT, PrimaryCustomerId INT, CoSignerCustomerId INT) GO CREATE TABLE Customer (CustomerId INT IDENTITY(1,1). FirstName VARCHAR(50), LastName VARCHAR(50), EmailAddress VARCHAR(255)) GO USE Sample_Reporting GO CREATE TABLE LoanReporting (ApplicationId INT, CreateTimestamp DATETIME, LoanAmount MONEY, SubmittedOn DATETIME, ApprovedOn DATETIME, LoanStatusId INT, PrimaryCustomerId INT, PrimaryFirstName VARCHAR(50), PrimaryLastName VARCHAR(50), PrimaryEmailAddress VARCHAR(255), CoSignerCustomerId INT, CoSignerFirstName VARCHAR(50), CoSignerLastName VARCHAR(50), CoSignerEmailAddress VARCHAR(255)) GO
Service Broker Objects
With this system I use a single pair of service broker queues to handle all the data transfer. This way transactional consistency can be maintained as the data flows in. These SQL Service Broker objects should be created in both the Sample_OLTP and the Sample_Reporting database..
CREATE MESSAGE TYPE ReplData_MT GO CREATE CONTRACT ReplData_Ct (ReplData_MT SENT BY ANY) GO CREATE QUEUE ReplData_Source_Queue GO CREATE QUEUE ReplData_Destination_Queue GO CREATE SERVICE ReplData_Source_Service ON QUEUE ReplData_Source_Queue (ReplData_Ct) GO CREATE SERVICE ReplData_Destination_Service ON QUEUE ReplData_Destination_Queue (ReplData_Ct) GO
In the OLTP database you create a route like this (just change the BROKER_INSTANCE to match your server).
CREATE ROUTE ReplData_Route WITH SERVICE_NAME='ReplData_Destination_Service', BROKER_INSTANCE='566C7F7A-9373-460A-8BCC-5C1FD4BF49C9', ADDRESS='LOCAL'
In the reporting database you create a route like this (just change the BROKER_INSTANCE to math your server).
CREATE ROUTE ReplData_Route WITH SERVICE_NAME='ReplData_Source_Service', BROKER_INSTANCE='A4EC5E44-60AF-4CD3-AAAD-C3D467AC682E', ADDRESS='LOCAL'
Stored Procedures on the OLTP Database
In the OLTP database we need just a single stored procedure. This stored procedure will handle the sending of the message so that we don’t have to put that same code in each table.
CREATE PROCEDURE SendTriggerData @XMLData XML AS BEGIN DECLARE @handle UNIQUEIDENTIFIER BEGIN DIALOG CONVERSATION @handle FROM SERVICE ReplData_Source_Service TO SERVICE 'ReplData_Destination_Service' ON CONTRACT ReplData_Ct WITH ENCRYPTION=OFF; SEND ON CONVERSATION @handle MESSAGE TYPE ReplData_MT (@XMLData) END GO
OLTP database Triggers
The triggers that are on each table on the OLTP database are kept as small as possible so that we put as little additional load on the OLTP server as possible. Obviously there will be some additional load on the OLTP database, but we want to keep that to a minimum.
CREATE TRIGGER t_LoanApplication ON LoanApplication FOR INSERT, UPDATE AS BEGIN DECLARE @xml XML SET @xml = (SELECT * FROM inserted as LoanApplication FOR XML AUTO, ROOT('root')) EXEC SendTriggerData @xml END GO CREATE TRIGGER t_Customer ON Customer FOR INSERT, UPDATE AS BEGIN DECLARE @xml XML SET @xml = (SELECT * FROM inserted as Customer FOR XML AUTO, ROOT('root')) EXEC SendTriggerData @xml END GO
Procedures on the Reporting Database
The reporting database is where the real work happens. Here we take the XML document, identify which table the data is from then pass the XML document to a child procedure which then processes the data and updates the table.
CREATE PROCEDURE ProcessOLTPData_LoanApplication @xml XML AS DECLARE @hDoc INT EXEC sp_xml_preparedocument @hDoc OUTPUT, @xml UPDATE LoanReporting SET ApplicationId=a.ApplicationId, CreateTimestamp = a.CreateTimestamp, LoanAmount=a.LoanAmount, SubmittedOn=a.SubmittedOn, ApprovedOn=a.ApprovedOn, LoanStatusId=a.LoanStatusId FROM OPENXML (@hDoc, '/root/LoanApplication') WITH (ApplicationId INT '@ApplicationId', CreateTimestamp DATETIME '@CreateTimestamp', LoanAmount MONEY '@LoanAmount', SubmittedOn DATETIME '@SubmittedOn', ApprovedOn DATETIME '@ApprovedOn', LoanStatusId INT '@LoanStatusId', PrimaryCustomerId INT '@PrimaryCustomerId', CoSignerCustomerId INT '@CoSignerCustomerId') a WHERE a.ApplicationId = LoanReporting.ApplicationId INSERT INTO LoanReporting (ApplicationId, CreateTimestamp, LoanAmount, SubmittedOn, ApprovedOn, LoanStatusId, PrimaryCustomerId, CoSignerCustomerId) SELECT ApplicationId, CreateTimestamp, LoanAmount, SubmittedOn, ApprovedOn, LoanStatusId, PrimaryCustomerId, CoSignerCustomerId FROM OPENXML (@hDoc, '/root/LoanApplication') WITH (ApplicationId INT '@ApplicationId', CreateTimestamp DATETIME '@CreateTimestamp', LoanAmount MONEY '@LoanAmount', SubmittedOn DATETIME '@SubmittedOn', ApprovedOn DATETIME '@ApprovedOn', LoanStatusId INT '@LoanStatusId', PrimaryCustomerId INT '@PrimaryCustomerId', CoSignerCustomerId INT '@CoSignerCustomerId') a WHERE NOT EXISTS (SELECT * FROM LoanReporting WHERE a.ApplicationId = LoanReporting.ApplicationId) EXEC sp_xml_removedocument @hDoc GO CREATE PROCEDURE PRocessOLTPData_Customer @xml XML AS DECLARE @hDoc INT EXEC sp_xml_preparedocument @hDoc OUTPUT, @xml UPDATE LoanReporting SET PrimaryEmailAddress = EmailAddress, PrimaryFirstName = FirstName, PrimaryLastName = LastName FROM OPENXML(@hDoc, '/root/Customer') WITH (CustomerId INT '@CustomerId', FirstName VARCHAR(50) '@FirstName', LastName VARCHAR(50) '@LastName', EmailAddress VARCHAR(255) '@EmailAddress') a WHERE PrimaryCustomerId = a.CustomerId UPDATE LoanReporting SET CoSignerEmailAddress = EmailAddress, CoSignerFirstName = FirstName, CoSignerLastName = LastName FROM OPENXML(@hDoc, '/root/Customer') WITH (CustomerId INT '@CustomerId', FirstName VARCHAR(50) '@FirstName', LastName VARCHAR(50) '@LastName', EmailAddress VARCHAR(255) '@EmailAddress') a WHERE CoSignerCustomerId = a.CustomerId EXEC sp_xml_removedocument @hDoc GO CREATE PROCEDURE ProcessOLTPData AS DECLARE @xml XML DECLARE @handle UNIQUEIDENTIFIER, @hDoc INT WHILE 1=1 BEGIN SELECT @xml = NULL, @handle = NULL WAITFOR (RECEIVE TOP (1) @handle=conversation_handle, @xml=CAST(message_body AS XML) FROM ReplData_Destination_Queue), TIMEOUT 1000 IF @handle IS NULL BREAK EXEC sp_xml_preparedocument @hDoc OUTPUT, @xml IF EXISTS (SELECT * FROM OPENXML(@hDoc, '/root/LoanApplication') ) BEGIN EXEC ProcessOLTPData_LoanApplication @xml END IF EXISTS (SELECT * FROM OPENXML (@hDoc, '/root/Customer') ) BEGIN EXEC PRocessOLTPData_Customer @xml END EXEC sp_xml_removedocument @hDoc END CONVERSATION @handle END GO
You can now set the queue to run the ProcessOLTPData Stored Procedure as an activation procedure and it will process the data as it comes in.
Hopefully someone finds this useful as a framework in their shop. If you have any questions please feel free to post them here.
So until just a few minutes ago the problem that everyone had with the blog written by John Dunleavy, who is the owner of SQLTech Consulting, was that he was taking the first part of whitepapers and posting them, with links to the original work, which John defended.
However then Paul Randal pointed out John’s entry into this months TSQLTuesday. It would appear that a good portion of John’s post were taken directly from an older article which I wrote. Below are screen shots from John’s blog. I’ve underlined the parts which are taken directly from an article which I wrote a couple of years ago titled Optimize Disk Configurations in SQL Server. You’ll find the matching content about half way down.
Now if you compare those sections to the RAID level descriptions that I posted in my article you’ll find that they match way to closely to be by accident. Hell the RAID 5 one matches word for word, and the RAID 10 one is pretty damn close.
The best part is John apparently didn’t finish my article, he just got to the parts he needed to swipe and stopped because in his RAID 10 description he says that if you are attached to a SAN you’ll be using RAID 10 for everything, and he even says that RAID 0 is find for ultrahigh write databases. Where in my article I say that RAID 5 or RAID 6 will work fine for most, then move up to RAID 10 if you need the higher write throughput.
Now, yes you could say that it’s RAID, how many ways are their to describe it, so it could just be chance. However note that the punctuation matches, including the placement of the semi-colons. It really isn’t very likely that this was an accident.
Even if posting unattributed copies of others works with links back was ok, flat out stealing content from their site isn’t.
Needless to say I was less than impressed when I saw this. In case you were wondering stealing someones content is not flattery. At all.
P.S. In case you are looking for the unchopped version of this blog post, I’ve included screen shots below in case his post is removed from his site.
Today’s conversation all started when I pointed out to John that posting other people’s white papers under your own name wasn’t exactly the best of ideas. Below is a screen shot of the post that caught my eye.
The biggest performance issue when virtualizing your SQL Server is storage. One way to fix this is to present storage directly to the virtual machine instead of placing the virtual machines storage within a virtual disk. In VMware this is done by using a Raw Device Mapping.
In a nut shell what we are doing with a Raw Device Mapping we take a LUN, which is presented to the VMware host. Instead of formatting this LUN with a file system and making it a datastore within VMware you detect it as normal, but leave it unformated. You can then attach the now mounted device to the virtual machine as a SCSI device.
This can be done either when the virtual machine is being created, or it can be done after the fact. In the case of these screenshots the RAW device is being added to an existing virtual machine.
Edit the virtual machine’s properties and click the Add button at the top to add a new hardware device to the virtual machine. Select Hard Disk and click Next.
On the next screen select Raw Device Mappings instead of creating a new virtual disk, or using an existing one.
On the next screen select the raw unformatted device which you wish to present to the guest. (Yes I realize that this LUN which is showing up shows as 0 bytes, but this is just a demo, you’ll want to select a LUN which has a LUN ID which isn’t 0 and has a size over 0 bytes.)
The next screen asks what data store you want to store the LUN mapping. Typically you’ll store this with the Virtual Machine, but you can select any datastore to store this mapping on. (In my case I’m selecting to store with the Virtual Machine.)
On the next screen you’ll need to make a decision as to weather you want the ability for VMware to take a snapshot of the raw device or not. Personally when it comes to SQL Server I don’t see this as a function that is all that useful. SQL has full and transaction log backups to handle the point in time recovery, so I would say that most of the time you’ll want to select Physical. If you need the ability to take snapshots of the raw device then select virtual.
On the next screen you need to select the SCSI address that you want to mount the disk to within the virtual machine. If you selected Virtual on the prior screen then you’ll also have the option to make the disk Independent as well as persistent or nonpersistant (this isn’t shown, but are the same options which are available when creating a virtual disk).
The last screen will present you wish a basic summary of what is being added to the virtual machine.
After Clicking Finish, click OK on the Virtual Machine properties. Log into the VM and rescan the SCSI bus as you would to detect any new disk being adding to the virtual machine. Create a partition (with the offset set correctly) and format the partition normally. You can now access the drive, and the virtual machine will have direct access to the SAN over the fiber channel network.
So it turns out that the code that I posted last week for discounts to SSWUG as stopped working for some reason. SSWUG has created a new code for $30 off. The new code is DCSPVC10.
Everything else is my old post is still valid.
I’ll be presenting several sessions at SQL Saturday as are several other great speakers. However they are still looking for additional speakers both new and experienced to give presentations at the event. They are also looking for additional sponsors to help make the event even better.
So if you have been looking for an event to give your first presentation at, or if you have a stack of presentations, this would be a great event for you to speak at.
If you’ve been looking for some free SQL Server training in the Southern California area, this is the event for you. There will be hours of free SQL Server training all available for just showing up (and for $10 to cover the lunch).
I hope to see you there.
Were you planning on attending the SSWUG vConference this April 7-9, 2010 but you wanted to save some extra cash in the process? Have I got the perfect solution for you. When signing up use VIP code DCHERRYSPVC10 and this will save you an extra $30 off the registration cost of $249.00. If you sign up now, you’ll get the Early bird rate of $190, minus the $30 off which brings the price down to a very reasonable $160.
Conference registration includes 6 month membership/extension to SSWUG.org, all materials, 3 days of sessions, 45 days of on-demand viewing and a lot more!
Conference details are stolen strait off of the sign up page.
I’ve got three sessions which I just finished recording earlier in the week, and I’m planning on being online during them to handle any Questions which come up via the Live Q&A (I may have a scheduling conflict as I’ll be in Redmond at the Microsoft Offices for part of one of the days, but hopefully we can work around that.)
See you there.
The timing on this post might seam a little strange, but I’ve been meaning to write this for a while and I finely got a chance to do it.
Back when Hyper-V 1.0 was released it wasn’t all that great of a product. It showed some promise, but it really wasn’t there. I had all sorts of people (mostly from Microsoft) telling me that it was way better than ESX and that I needed to give it a shot. My personal feelings are that it wasn’t anywhere near where ESX was, and for my production environment I needed the better product, so we went with ESX 3.5.
Well a while back Microsoft released Hyper-V 2.0 and it is a much better release than it was at the time. I’d even be willing to stack it up next to VMware’s ESX 3.5 which was VMware’s competing version at the time of release. Put next to ESX 3.5 you would have two well matched products. Both included a real time online migration solution vMotion for ESX and life migration for Hyper-V. Both support being put into a high availability cluster. Both support pass through disks so that the guest OS has direct access to the fibre channel storage.
However shortly after Hyper-V 2.0 was release VMware released vSphere 4.0 which is the successor to ESX 3.5 and with vSphere 4.0 they’ve blown the doors off of Hyper-V yet again.
vSphere gives us FT or Fault Tolerance which basically runs a single VM on two machines with only one of the machines being active at a time. In the event that one host fails the other host being running the VM actively with no outage to the guest. Users connected to the guest won’t even know that the guest has switched to another machine.
VMware has also introduced some interesting features as experimental which means that we will probably see them show up as full on features in a future release. This includes the ability to map an HBA directly to a virtual machine to give the VM actual direct access to the HBA. At the moment that HBA can only be mapped to a single VM, but hopefully in the next release they will fix that.
Now don’t get we wrong, I think that Hyper-V has come a long way since the v1 release. Do I think that Hyper-V is an Enterprise Ready solution? Yes I do. Do I think Hyper-V is ready to be called the winner in the virtualization server space? No, not at all. I think it is anyone’s game still before we have a clear winner. Hyper-V has a big selling point to it, the cost to get into Hyper-V is free, as long as you don’t want to cluster it. Then you’ll need to purchase a management tools license for each host machine. How with VMware you’ll want the management tool weather you cluster the machines or not, but its a single purchase from VMware at least.
What it really comes down to is that you need to fully evaluate both platforms and due a solid CBA (Cost Benefit Analysis) as well as a full feature analysis before picking a platform for your enterprise. Because once you pick one platform moving from one to the other is very tough to do.