The new In-Memory OLTP feature introduces memory-optimized tables and natively compiled stored procedures in SQL Server 2014, to optimize performance and scalability of OLTP workloads. To benefit from this technology in an existing SQL Server application, you need to migrate the performance critical tables and stored procedures to memory-optimized tables and natively compiled stored procedures respectively. End-to-end migration of tables and procedures was discussed in the blog post here. In this post we focus on the implications of the new transaction isolation model to application migration.

Transaction isolation levels for memory-optimized tables are implemented using optimistic concurrency control and therefore are lock-free. This means that SQL Server does not use locks to guarantee consistency in case multiple transactions attempt to access the same data. Instead, SQL uses a combination of snapshot-based isolation and conflict detection to guarantee consistency. For more details on transactions with In-Memory OLTP see the Books Online topic here. We will discuss the principles and the architecture of the lock-free engine for memory-optimized tables in more detail in a later post.

Snapshot-based isolation

All transactions read rows from memory-optimized tables as of a single point in time, usually the start of the transaction. This means that a running transaction does not observe changes made by other, concurrent, transactions that commit while the transaction is running.

Consider, for example, two transaction, tx1 and tx2, and a single memory-optimized table Tab, with a single row r1. The following table shows an example in which the transaction tx1 reads from the table, and tx2 inserts a row. The first column indicates the time; the second column indicates the contents of the table Tab.

Time	Contents of Tab	Operations in tx1	Operations in tx2
100	r1	BEGIN TRAN
101	r1	…	BEGIN TRAN
102	r1	SELECT FROM Tab * returns (r1)	INSERT Tab VALUES (r2)
103	r1, r2	…	COMMIT
104	r1, r2	…
105	r1, r2	SELECT FROM Tab * returns (r1)
106	r1, r2	COMMIT

Notice that at time 105, even though the table Tab contains two rows (r1, r2), the transaction tx1 only sees r1. All read operations in tx1 are executed as of time 100.

Snapshots in transaction isolation are not new in SQL Server. SNAPSHOT isolation has been available for disk-based tables since SQL Server 2005. However, the default isolation level for disk-based table remains read committed, and higher isolation levels such as REPEATABLE READ and SERIALIZABLE do not use snapshots. Memory-optimized tables use snapshots for all transaction isolation levels, including SNAPSHOT, REPEATABLE READ, and SERIALIZABLE.

You need to keep this in mind when migrating a table to memory-optimized, in particular when using READ COMMITTED isolation today. If there are no dependencies between concurrent transactions at the application level, in most cases you can use SNAPSHOT isolation on the migrated tables without changing the application. However, if there is a dependency, and the application relies on tx1 seeing the changes made by tx2, you need to make further changes to the app to handle snapshot-based isolation. For example, you could commit tx1 after the initial read operation at time 102, and start a new transaction, which would be able to see rows inserted by tx2.

For guidelines on transaction isolation levels with memory-optimized tables, and how to migrate apps that use the READ COMMITTED isolation level today, see Books Online.

Conflicts

Rather than taking locks to prevent concurrent access to a table, memory-optimized tables use conflict detection to enforce isolation of modifications, and to guarantee the higher isolation levels REPEATABLE READ and SERIALIZABLE.

If two concurrent transactions attempt to update the same row, one of the transactions will fail and roll back. Consider the following example, where two transaction attempt to update the same row r1.

Time	Operations in tx1	Operations in tx2
100	BEGIN TRAN
101	…	BEGIN TRAN
102	…	UPDATE r1 – success
103	UPDATE r1 – error – tx1 is aborted	…
104		COMMIT – success

In this example, tx2 successfully updates r1 and later successfully commits. Transaction tx1 attempts to update r1 after it has already been updated by tx2. The update fails, and transaction tx1 is aborted. The first writer, in this case tx2, always wins. Transaction tx1 will need to be retried. In contrast, with READ COMMITTED isolation in disk-based tables, tx2 would take a lock when it updates r1. Transaction tx1 would block and wait until tx2 commits and the lock is released. At that point, i.e. after time 104, tx1 would proceed with the update to r1.

Validation of REPEATABLE READ and SERIALIZABLE isolation is done during commit processing. If SQL Server finds that the desired isolation level has been violated, the transaction is aborted at this time.

As conflicts cause transaction abort, transactions may need to be retried. For this reason, transactions that modify memory-optimized tables require logic to retry the transactions on failures. This retry logic can be implemented either in the client application, or through a wrapper stored procedure on the server. For more details on retry logic and a sample stored procedure implementing retry logic, see Books Online.

Download SQL Server CTP1and get started today, or see more blogs in the series introduction and index here!

Hello Everyone,

This is Shijo from our team in Bangalore once again.  Today I’d like to briefly discuss account lockouts, and some UI behaviors that can trip admins up when dealing with account lockouts.

If you’ve ever had to troubleshoot an account lockout issue, you might have noticed that sometimes accounts appear to be locked on some domain controllers, but not on others.  This can be very confusing since you
typically know that the account has been locked out, but when you inspect individual DCs, they don’t reflect that status.  This inconsistency happens because of some minor differences in the behavior of the UI between Windows Server 2003, Windows Server 2008, Windows Server 2008 R2, and Windows Server 2012.

Windows Server 2003

In Windows Server 2003 the "Account is locked out" checkbox can be cleared ONLY if the account is locked out on the domain controller you are connected to. This means that if an account has been locked out, but the local DC has not yet replicated that information, you CANNOT unlock the account on the local DC.

Windows 2003 account properties for an unlocked account.  Note that the checkbox is grayed out.

 

Windows Server 2008 and Windows Server 2008 R2

In Windows Server 2008/2008 R2 the "Unlock account" checkbox will always be available (regardless of the status of the account). You can tell whether the local DC knows if the account is locked out by looking at the label on the checkbox as shown in the screenshots below:

Windows 2008 account properties showing the “Unlock Account” checkbox.  Notice that the checkbox is available regardless of the status of the account on the local DC.

 

Windows 2008 (and higher) Account Properties dialog box showing locked account on this domain controller

 

If the label on the checkbox is just "Unlock account" then this means that the domain controller you are connected to recognizes the account as unlocked. This does NOT mean that the account is not locked on other DCs, just that the specific DC we're working with has not replicated a lockout status yet.  However, unlike Windows Server 2003, if the local DC doesn’t realize that the account is locked, you DO have ability to unlock it from this interface by checking the checkbox and applying the change.

We changed the UI behavior in Windows Server 2008 to help administrators in large environments unlock accounts faster when required, instead of having to wait for replication to occur, then unlock the account, and then wait for replication to occur again.

 

Windows Server 2012

We can also unlock the accounts using the Active Directory Administrative Center (available in Windows Server 2008 R2 and later).  In Windows Server 2012, this console is the preferred method of managing accounts in Active Directory. The screen shots are present below about how we can go about doing that.

You can see from the account screenshot that the account is locked which is denoted by the padlock symbol. To unlock the account you would have to click on the “Unlock account” tab and you would see a
change in the symbol as can be seen below.

 

You can also unlock the account using the PowerShell command shown in the screenshot below.

 

In this example, I have unlocked the user account named test by simply specifying the DN of the account to unlock . You can modify your powershell command to incorporate many more switches, the details of which are present in the
following article.

http://technet.microsoft.com/en-us/library/ee617234.aspx

Hopefully this helps explain why the older operating systems behave slightly differently from the newer ones, and will help you the next time you have to deal with an account that is locked out in your environment! 

 

If you’re looking for more information on Account Lockouts, check out the following links:

Troubleshooting Account Lockout

Account Lockout Policy

Account Lockout Management Tools

 

Shijo “UNLOCK IT” Joy

Dear Friends,

 

Every release of SQL Server shipped with full of new features so is SQL Server 2012. Some of them are Availability Groups, Contained Databases, Column Store Indexes, User Defined roles,  DQS, Power View to name a few. We do observe lot of enhancements in GUI as well which ease up our task and helpful to the ones who doesn’t want to play with T-SQL. Needless to say, we do look on how newer versions of SQL Server have matured in terms of GUI. With this in mind, today I’m going to show you how you can restore a page using SSMS on SQL 2012.

To begin with; at different times we do observe page level corruptions in a database as such definitely as database admins we are interested in restoring single page rather than restoring the entire database mostly to minimize downtime. In practical also when your database is quite huge one in size, you never want to go for full restoration only when a single page is identified to be corrupted. By saying so, my statement definitely is not a generic one understanding this may differ across environments.

We can say that page level restoration is not something new to us but yes page restoration using SSMS definitely something new in SQL Server 2012. Till SQL Server 2012 page restoration was possible using T-SQL but now with the launch of SQL Server 2012, you have the flexibility to use its improved GUI to achieve the same objective.

Today, I’ll be using AdventureWorks2012 database for the demo purpose. Table I’ll be using here is HumanResources.JobCandidate and I’m interested to corrupt Index Page for this table today. You can read more on how to identify a page here.

As we are discussing on SQL Server 2012 as such I’m not going to use DBCC IND to find out index page details instead will be using new DMV SYS.DM_DB_DATABASE_PAGE_ALLOCATIONS to retrieve similar information. This is an undocumented change and you can read more on this link.

So I ran the query as can be seen on following screenshot to obtain similar details;

Ok, from query output I identified PageID 1057 of Page Type 2 i.e. Index Page (image below);

Now time to corrupt this page but as always recommended before I do that, let me backup my database first (to be at the safer side);

We are all set to corrupt PageID 1057. I’ll be using XVI32 editor to corrupt the page which can be downloaded from here; but before I do so I need to OFFLINE the database.

 

Now, before I open up hex-editor; I need to find out the page offset and for me it is 1057 * 8192 (i.e. No of bytes for the page) = 8658944. By doing so, I’m all set to corrupt the page now. I went to the exact page and corrupted that by entering some letter;

By doing so, in technical terms I’ve corrupted the page. Time to bring database online and query corrupted table. Let’s do that;

All set to run Select query on HumanResources.JobCandidate table so going forward;

It can be seen very clearly that I’ve corrupted the same page matched in hex-editor. Now it is the time to think about remediating the corrupted page.

As mentioned before starting SQL Server 2012 we can select this from SSMS by right clicking on database and choose restore page. As soon as I do that, I’ll get the following window;

What’s great to see here is SQL Server has already ran a DBCC CHECKDB and pulled details on corrupted page. Good one to have for sure. This wizard also lets you select backup that you want to use to repair corrupted pages so let’s move ahead.

Yes, I’ve successfully restored corrupted page; so let’s check out if we can query HumanResources.JobCandidate.

We can very much retrieve data out of table is in question. Needless to say, this indeed a cool feature offered by SQL Server 2012.

 

Please note I’ve tested this in a LAB system for demonstration purpose and you should never ever try these steps in any production or live environments.

 

If you liked the post, do like us on Facebook at http://www.FaceBook.com/SQLServerGeeks

Have a SQL Server question? Join the fastest growing SQL Server Facebook group at: http://www.facebook.com/groups/458103987564477/

 

Regards,

Kanchan 

The new In-Memory OLTP feature introduces memory-optimized tables and natively compiled stored procedures in SQL Server 2014, to optimize performance and scalability of OLTP workloads. To benefit from this technology in an existing SQL Server application, you need to migrate the performance critical tables and stored procedures to memory-optimized tables and natively compiled stored procedures respectively. End-to-end migration of tables and procedures was discussed in the blog post here. In this post we focus on the implications of the new transaction isolation model to application migration.

Transaction isolation levels for memory-optimized tables are implemented using optimistic concurrency control and therefore are lock-free. This means that SQL Server does not use locks to guarantee consistency in case multiple transactions attempt to access the same data. Instead, SQL uses a combination of snapshot-based isolation and conflict detection to guarantee consistency. For more details on transactions with In-Memory OLTP see the Books Online topic here. We will discuss the principles and the architecture of the lock-free engine for memory-optimized tables in more detail in a later post.

Snapshot-based isolation

All transactions read rows from memory-optimized tables as of a single point in time, usually the start of the transaction. This means that a running transaction does not observe changes made by other, concurrent, transactions that commit while the transaction is running.

Consider, for example, two transaction, tx1 and tx2, and a single memory-optimized table Tab, with a single row r1. The following table shows an example in which the transaction tx1 reads from the table, and tx2 inserts a row. The first column indicates the time; the second column indicates the contents of the table Tab.

Time	Contents of Tab	Operations in tx1	Operations in tx2
100	r1	BEGIN TRAN
101	r1	…	BEGIN TRAN
102	r1	SELECT FROM Tab * returns (r1)	INSERT Tab VALUES (r2)
103	r1, r2	…	COMMIT
104	r1, r2	…
105	r1, r2	SELECT FROM Tab * returns (r1)
106	r1, r2	COMMIT

Notice that at time 105, even though the table Tab contains two rows (r1, r2), the transaction tx1 only sees r1. All read operations in tx1 are executed as of time 100.

Snapshots in transaction isolation are not new in SQL Server. SNAPSHOT isolation has been available for disk-based tables since SQL Server 2005. However, the default isolation level for disk-based table remains read committed, and higher isolation levels such as REPEATABLE READ and SERIALIZABLE do not use snapshots. Memory-optimized tables use snapshots for all transaction isolation levels, including SNAPSHOT, REPEATABLE READ, and SERIALIZABLE.

You need to keep this in mind when migrating a table to memory-optimized, in particular when using READ COMMITTED isolation today. If there are no dependencies between concurrent transactions at the application level, in most cases you can use SNAPSHOT isolation on the migrated tables without changing the application. However, if there is a dependency, and the application relies on tx1 seeing the changes made by tx2, you need to make further changes to the app to handle snapshot-based isolation. For example, you could commit tx1 after the initial read operation at time 102, and start a new transaction, which would be able to see rows inserted by tx2.

For guidelines on transaction isolation levels with memory-optimized tables, and how to migrate apps that use the READ COMMITTED isolation level today, see Books Online.

Conflicts

Rather than taking locks to prevent concurrent access to a table, memory-optimized tables use conflict detection to enforce isolation of modifications, and to guarantee the higher isolation levels REPEATABLE READ and SERIALIZABLE.

If two concurrent transactions attempt to update the same row, one of the transactions will fail and roll back. Consider the following example, where two transaction attempt to update the same row r1.

Time	Operations in tx1	Operations in tx2
100	BEGIN TRAN
101	…	BEGIN TRAN
102	…	UPDATE r1 – success
103	UPDATE r1 – error – tx1 is aborted	…
104		COMMIT – success

In this example, tx2 successfully updates r1 and later successfully commits. Transaction tx1 attempts to update r1 after it has already been updated by tx2. The update fails, and transaction tx1 is aborted. The first writer, in this case tx2, always wins. Transaction tx1 will need to be retried. In contrast, with READ COMMITTED isolation in disk-based tables, tx2 would take a lock when it updates r1. Transaction tx1 would block and wait until tx2 commits and the lock is released. At that point, i.e. after time 104, tx1 would proceed with the update to r1.

Validation of REPEATABLE READ and SERIALIZABLE isolation is done during commit processing. If SQL Server finds that the desired isolation level has been violated, the transaction is aborted at this time.

As conflicts cause transaction abort, transactions may need to be retried. For this reason, transactions that modify memory-optimized tables require logic to retry the transactions on failures. This retry logic can be implemented either in the client application, or through a wrapper stored procedure on the server. For more details on retry logic and a sample stored procedure implementing retry logic, see Books Online.

As of September 2013, sixteen years prior, Microsoft released the Enterprise Edition of Windows NT Server 4.0 which included Microsoft Cluster Server (also known as MSCS).  My how server clustering has changed over the years!  In one of my last posts to Ask PFE Platforms as I plan to retire from Microsoft in October, this seemed like a good time to reflect on the sweet sixteen milestone for Server Clustering in Windows Server.  My history with clustering goes back a bit in time and includes time well spent as one of the few beta engineers for MSCS prior to initial product release.

Early Days of Microsoft Clustering

Prior to joining Microsoft, I was a systems programmer in the mini/mainframe world and got some experience with clustering.  In fact, in true geek fashion, I had my own cluster at home built out of surplus hardware and hobbyist licenses.  I was a big fan of Dave Cutler and the emerging Windows NT operating system.   When I jumped on board with Microsoft in March of 1995, I supported Windows NT 3.1 and 3.5…and a few other corporate level server products.  I had no idea that just over a year later I would become a beta engineer for clustering using Windows NT…nor did I know that I’d still be involved with clustering many years later…or that I’d have a cluster in the corner of my basement keeping my home automation system highly available.

When I first joined the project as a beta engineer in 1996, it was definitely an exciting time following the release of Windows NT Server 4.0.  With prior clustering experience, I was always about design with redundancy in mind.  This became quite the funny joke around the office because after joining the beta, my wife became pregnant with twins.   Not only did the cluster beta guy have multiple two node clusters in his cube, he was now going to have redundant kids!  As a precaution since product release was approaching, we found a volunteer to learn clustering and be my stand-in in case I became unavailable earlier than expected.  Within two months, his wife was also pregnant with twins.  The laughing stopped.  Others in the office stayed way clear of our cubes (and the water cooler) until after product release.  Was I ever glad that clustering at the time was limited to a maximum of two nodes! J

When Microsoft Cluster Server released in September 1997 with Windows NT Server 4.0, Enterprise Edition, clustering began to provide high availability in many high visibility situations.   I would find myself on the phone supporting clusters around the globe and meeting lots of great people.  While MSCS was a released product for quite some time, people continued to refer to it by its beta name “Wolfpack”.  Clustering in those days was limited to two servers coupled together as nodes and was very dependent on hardware that needed to be thoroughly tested, and on a certified list.  Many issues were due to hardware, having the wrong drivers or firmware, or all of the above.  When implemented correctly with proper hardware, etc…it worked great.

Evolution of Microsoft Clustering

Clustering improved with Windows 2000 but even more so with Windows Server 2003.  Clusters could easily go beyond two nodes, use more memory, and utilize more processing power.  In fact, there are still quite a number of Windows Server 2003 based clusters in existence today as the extended support end date for Windows Server 2003 approaches.    Some of those clusters are geographically dispersed.  However, Windows Server 2008 was a significant leap forward for clustering.   Not only were clusters able to support even more nodes, memory, and processors…2008 x64 based clusters had support for Hyper-V virtualization built-in.  Another fantastic improvement was the built-in validation process that alleviated the need for the old Hardware Compatibility List.  Windows Server 2008 R2 took virtualization to another level with Cluster Shared Volumes and Live Migration.

Server Clustering Today

Windows Server 2012 expanded upon the great leaps forward of Windows Server 2008 R2 clustering by providing support for virtualization features like Hyper-V Replica.  Windows Server 2012 clustering also scales to 64 nodes and up to 8000 virtual machines.  That’s a pretty significant difference from the original limitation of only two nodes.  For an example of how to create a Windows Server 2012 Failover Cluster, click here.

Based on Brad Anderson’s post about Windows Server 2012 R2 release to manufacturing, general availability is right around the corner.  In fact, his later post marks availability to TechNet and MSDN subscribers.  If you are a subscriber, what are you waiting for?  Downloaded it yet?  Server Clustering once again continues to evolve and is easy to install.  For a great summary of improvements and features, check out What’s New in Failover Clustering in Windows Server 2012 R2 on TechNet!

For More Information

Windows Server 2012 Failover Clustering

http://technet.microsoft.com/en-us/library/hh831579.aspx

Windows Server 2008 R2 Failover Clustering

http://technet.microsoft.com/en-us/library/ff182338(v=WS.10).aspx

Should I use the same network adapters for all interfaces on my cluster?

http://blogs.technet.com/b/askpfeplat/archive/2013/02/21/mailbag-should-i-use-the-same-network-adapters-for-all-interfaces-on-my-cluster.aspx

Clustering: What exactly is a File Share Witness and when should I use one?

http://blogs.technet.com/b/askpfeplat/archive/2013/06/05/3506327.aspx

Cluster Team Blog on Technet

http://blogs.msdn.com/b/clustering/

Brad Anderson’s ‘In the Cloud’ Blog

http://blogs.technet.com/b/in_the_cloud/

 

Thanks so much for reading and rating my posts here...and for all the great feedback.    I have certainly enjoyed being part of the community and interacting with those that have posted comments or needed additional follow-up. 

Until we meet again!

Martin

 

Knowing a great deal about backup and recovery is key to any DBA’s career. Most of us fully understand the recovery models, recovery point objectives and recovery time objectives. What I have found from speaking at many SQL Saturday’s and User Groups is that most DBA’s do not regularly practice recovery strategies.

It is very important for a DBA to know how to recover their environments which could include recovering the master database, performing a point in time restore or possibly a piece meal restore.

In one of my sessions “Know Backup, Know Recovery” I demonstrate performing a tail log backup after deleting a my data file.  That demo gets a lot of praise, however I decided to take it a step further and demonstrate attaching a transaction log to another database and recovering the tail end of the log. I then perform a restore of my full and tlogs including the new created tail log backup. The audience has always been very impressed with that demo. My favorite response was “holy &%^#, you can do that”. I commented back that yes I can, and now you can too.

As a matter of fact, this is my Lightning talk presentation at the 2013 PASS Summit.  I gave this presentation to my User Group this week and decided that I would record it and upload it to my YouTube channel for the rest of the world to enjoy. With that being said, if you have never seen this performed, take less than 5 minutes out of your busy day and watch the video. “Ultimate Tail Log Backup and Restore”

I hope you never have to perform this in a production environment, but at least prepare yourself and know how to do it.

On October 1st the emails went out notifying existing MVP’s in the Oct cycle if they were fortunate enough to be renewed and notifying the newly awarded MVP’s. Within the SQL Server community the first day of each quarter means many are anxiously monitoring their inbox and spam folders in the hopes they are being renewed or awarded MVP.

This Oct 1st I was in the list of those anxiously awaiting the email that never came. A couple of months ago I was contacted by the MVP program to provide a list of all my community involvement including blog stats. I thought I had a pretty impressive list with speaking a dozen times the previous year, being a chapter leader, regional mentor, author on two books, decent blog stats, high karma rank on ask.ssc, etc.

The reality is that the pool of folks within the SQL Server community that is deserving of being awarded MVP is vast. I have people tell me all the time that I should be an MVP or that they already consider me an MVP. That really means a lot to me and all the other deserving future MVP’s out there. To have been nominated and considered is a huge honor alone.

Being thought of as an MVP already is a two edge sword for some. I was in Austin TX a couple of years ago at a SQL Saturday when Wes Brown (blog) was awarded MVP. I was completely floored. Wes Brown wasn’t already an MVP? How the heck was that possible. He summed it up in typical Wes Brown fashion. He stated, “to be awarded, you have to be nominated. If everyone already thinks you are, then no one is filling out the form”. That statement couldn’t be more true.

So people reading this, if there are those you know or that you feel should be and MVP, take the time to nominate them by <clicking here>. You only need to provide their name and email address and give a brief description of why you feel they are an MVP. It will then send them an email to complete additional information. If you need additional information to list why  you feel they are qualified see if they have a section on their blog about their speaking and volunteer work. Much like I have here timradney.com/community.

What are you waiting for, nominate that well deserving person today.

Hi, this is Yang and Mike, PMs from the Windows Server Essentials team. In this blog, we would like to share some of our thoughts about virtualizing Windows Server Essentials.

Today, more and more businesses have begun to virtualize their IT infrastructure. While there are a lot of articles talking about the benefits of virtualization, we would like to highlight a few in the context of small to mid-size business (SMB) IT:

• Consolidation of your workloads. Most of us started our IT infrastructure with workloads on physical servers. Over time, we added more and more servers for different workloads, which have become difficult to maintain. One obvious benefit of virtualization is to consolidate workloads onto fewer more powerful servers. By taking advantage of virtualization, when you want to deploy new workloads, you can add a virtual server that runs on your existing hardware.
• Cost-effective business continuity and disaster recovery solutions. One of the top questions that IT administrators are asked is how they plan for unexpected downtime. Virtualization has certainly expanded the options available in this area. Depending on your business requirements, you can use Hyper-V server clusters to configure failover for your critical workloads, and you can use Hyper-V Replica to have standby instances of your virtual servers in case of failure or disaster. These options are not possible if your workloads are deployed on physical servers.
• Mobility. Many SMB IT admins recognize the value of applications and services running in the public cloud, but are unsure of how these would work together with their traditional IT environments. Virtualization is the first step to help you prepare to leverage public cloud hosting and other services where it makes sense for your business needs. With virtualization, individual virtual machines (VMs) and the workloads that run on them can easily be moved to the cloud so that you can evaluate and start to take advantage of the flexibility and scalability that virtualization offers. 

Windows Server Essentials enjoys all the same capabilities and benefits of virtualization as the Standard and Datacenter editions of Windows Server. In addition, there are some capabilities that are specific to Windows Server Essentials:

• Select OEMs offer Windows Server Essentials together with their hardware solutions, which enable partners and customers to easily deploy Essentials as a Hyper-V host with Windows Server Essentials running as a guest VM using a single comprehensive wizard. For more information, see Jason’s blog post.
• It is recommended that you back up your Essentials VM with Windows Server Backup on the Hyper-V host, which can back up all the VMs running on the same host. When you need to restore, you can restore the VM, or you can restore the virtual hard disk (VHD), and then mount the VHD and restore the individual file or folder. If you only want to back up important files or folders on the VM, you can use Windows Azure Backup. We’ll publish another post in the future to talk about these various options and their pros and cons.
• When taking advantage of Storage Spaces in a virtualized environment, you can create your storage pools and virtual disks on the Hyper-V host, not on the guest VMs. To learn more about the best practices for Storage Spaces, see Storage Spaces Frequently Asked Questions (FAQ).
• When running an Essentials VM in Windows Azure (either by migrating an existing on-premises Essentials VM to Azure or by creating a new virtual machine directly in Azure), note the following requirements:
• You must have an existing virtual network and create the Essentials VM in the virtual network.
• It is recommended that you have a VM size of Medium (2 cores, 3.5 GB memory) or larger.
• Port 443 needs to be open for the Anywhere Access feature to work.
• When running the Anywhere Access Wizard, you should select the “Skip router setup” check box, because there is no physical router in the Windows Azure environment.

Stay tuned for future posts with even more information about running an Essentials VM in Windows Azure!

mdsolar tips this story at the NY Times: "Every month, Hiroko Watabe, 74, returns for a few hours to her abandoned house near the damaged Fukushima nuclear plant to engage in her own small act of defiance against fate. She dons a surgical mask, hangs two radiation-measuring devices around her neck and crouches down to pull weeds. She is desperate to keep her small yard clean to prove she has not given up on her home, which she and her family evacuated two years ago after a 9.0 earthquake and a tsunami devastated the plant five miles away. Not all her neighbors are willing to take the risk; chest-high weeds now block the doorways of their once-tidy homes. 'In my heart, I know we can never live here again,' said Ms. Watabe, who drove here with her husband from Koriyama, the city an hour away where they have lived since the disaster. 'But doing this gives us a purpose. We are saying that this is still our home.' While the continuing environmental disaster at the Fukushima Daiichi plant has grabbed world headlines — with hundreds of tons of contaminated water flowing into the Pacific Ocean daily — a human crisis has been quietly unfolding. Two and a half years after the plant belched plumes of radioactive materials over northeast Japan, the almost 83,000 nuclear refugees evacuated from the worst-hit areas are still unable to go home."

Read more of this story at Slashdot.

barlevg writes "The Washington Post reports that NSA director Gen. Keith Alexander testified before the Senate about an experimental NSA program to track location data from cell phones in 2011, but abandoned it because it lacked 'the operational value' it needed. It was not made clear what 'operation value' they were seeking. Alexander said, 'the data collected were never available for intelligence analysis purposes.' He added, 'This may be something that is a future requirement for the country, but it is not right now because when we identify a number we can give that to the FBI, [who can a warrant for the data it needs]. That’s the reason we stopped in 2011.''"

Read more of this story at Slashdot.

Nerval's Lobster writes "Former NSA technology boss Prescott Winter has a word for the kind of security he sees even at large, technologically sophisticated companies: Appalling. Companies large enough to afford good security remain vulnerable to hackers, malware and criminals because they tend to throw technological solutions at potential areas of risk rather than focusing on specific and immediate threats, Winter said during his keynote speech Oct. 1 at the Splunk Worldwide User's Conference in Las Vegas. 'As we look at the situation in the security arena we see an awful lot of big companies – Fortune 100-level companies – with, to be perfectly candid, appalling security. They have fundamentally no idea what they're doing,' Winter said, according to a story in U.K. tech-news site Computing. During almost 28 years at the National Security Agency (NSA), Winter established the spy agency's Technology Directorate and served as the agency's first CTO. He also held positions as the NSA's CIO, its deputy chief of Defensive Information Operations and, oddly, as chief of Customer Response. He is currently managing director of Chertoff Group, the strategic management and security consultancy established by Michael Chertoff, secretary of the Dept. of Homeland Security under Pres. George W. Bush and co-author of the USA Patriot Act."

Read more of this story at Slashdot.

The Guardian has released new documents from Edward Snowden showing how the U.S. National Security Agency targets internet anonymity tool Tor to gather intelligence. One of the documents, a presentation titled "Tor Stinks," bluntly acknowledges how effective the tool is: "We will never be able to de-anonymize all Tor users all the time. With manual analysis we can de-anonymize a very small fraction of Tor users, however, no success de-anonymizing a user in response to a TOPI request/on demand." (Other documents: presentation 1, presentation 2.) The NSA is able to extract information sometimes, though, and Bruce Schneier details what we know of that process in an article of his own. "The NSA creates 'fingerprints' that detect http requests from the Tor network to particular servers. These fingerprints are loaded into NSA database systems like XKeyscore, a bespoke collection and analysis tool which NSA boasts allows its analysts to see "almost everything" a target does on the internet. ... After identifying an individual Tor user on the internet, the NSA uses its network of secret internet servers to redirect those users to another set of secret internet servers, with the codename FoxAcid, to infect the user's computer. FoxAcid is an NSA system designed to act as a matchmaker between potential targets and attacks developed by the NSA, giving the agency opportunity to launch prepared attacks against their systems." Schneier explains in a related article why it's important that we figure out exactly what the NSA is doing. "Given how inept the NSA was at protecting its own secrets, it's extremely unlikely that Edward Snowden was the first sysadmin contractor to walk out the door with a boatload of them. And the previous leakers could have easily been working for a foreign government."

Read more of this story at Slashdot.

The sound of the phone woke Sergio from a deep sleep.

"We've been hacked. All of our data is gone. I can't believe this is happening"

Gathering up all of the wits he could find at 2:15 in the morning, Sergio contemplated his next move.

"Who are you?"

"It's Peter." A beat of silence. "Peter from Communibox"

That was enough to trigger a string of memories. Peter was the CEO of Communibox, a distributor of telecom hardware. About five years back Sergio had done some contract work for them. Nothing much. Just the website, inventory system, CMS and a risk management assessment. Peter himself was a fairly intense, alpha personality. A visionary, at least in his own mind, he was full of ideas and surrounded himself with people whose job it was to implement them while he moved on to the next big thing.

"Ah...Peter. What happened?"

The downside of some "visionary" personalities is that once a problem has been solved, there is no need to revisit it. After all, it's already working, so why waste time on it. There are newer and shinier objects to play...er...work on. So despite Sergio's requests at the time, Communibox never cared to maintain the application, upgrade the physical hardware or even patch PHP or MySQL. Eventually, Sergio stopped contacting them, comfortable in the fact that he had done a good job and that the customer was happy. That's a win-win in most consultant's books.

"Everything. It's all gone."

"No problem Peter. I'll check and call you back as soon as I know something"

Sergio cracked open his laptop, found the connection information (see...backups do come in handy sometimes) and tried to log in. Not surprisingly, his old credentials worked like a charm. As he went through the applications, Sergio found that the CEO was right. The vast majority of the data was gone. Not everything, mind you. But where there once was tens of thousands of products with description, images and real-time stocking information, there now was only a hundred or so entries left. The result was that Communibox's website, a fairly typical on-line catalog, was nearly empty.

The next obvious destination was the backups. The idea didn't leave Sergio brimming with confidence. He recalled a conversation he had with Peter while performing the risk assessment.

"Peter, you need to back all of this data up. Your catalog is growing like crazy and you need to protect it."

"I've got it covered", said Peter. "We're resellers of a very expensive backup solution and our expert is putting it in place for your system."

"All I really need is a dump of the MySQL database and some zipped file," countered Sergio. "A costly system isn't worth the effort. The key is that it needs to be off the server and preferably off prem as well."

"Not to worry. I've taken care of it"

Sergio reached out to Peter to get the name of the 'expert'. Turns out the he was the sales rep for the backup solution. When Sergio got hold of the expert, the news was not surprising.

"Yeah, Peter told me about it this morning. I forgot what I did there in the first place. But once the system was installed, I haven't touched it since."

After getting the details, as paltry as they were, Sergio was able to confirm his expectation. The most recent backup was from 4 years ago. The expert had failed to enable the rotation option on the hardware, so as soon as the disk space was used up, the backups stopped. Supposedly emails were sent to the interested parties, but they seem to have been successfully ignored.

It was time to bring Peter back to reality. "Peter, the situation is not good at all. Your data is gone and your most recent back up is 4 years old. I've checked the site logs and while your software is way out of date, there's nothing to indicate a security breach on your web site."

You could almost see the light bulb above Sergio's head go on. Of course, the darkness of the early morning hadn't yet been dissipated, so that might have something to do with it.

The Logs. Back when Sergio was working for Communibox, he was still in his "let's log every single trivial action" stage of his career. The details were stored in a couple of database tables, ready for access when required. Now seemed like a good time.

"Hold on a couple of seconds", Sergio said to Peter.

After a couple of quick queries (well...the queries were quick to write. The results took a lot longer to become visible), an odd pattern emerged.

"Peter," said Sergio, "I'm looking at some custom logs. It looks like starting late on Friday night, someone went in and started deleting the product listings. Through the user interface. One at a time, it seems. For almost 40 straight hours. And the username is 'michelle'. Do who know who 'michelle' is?"

"What???", exclaimed Peter. "That's not possible. She was let go last Friday."

"And did you disable her user account?"

The silence was damning.

"I'll call you back."

Of course, Peter never did call back. A few months later, Sergio ran across Larry, a colleague who worked with him on the Communibox project. Naturally the late night data disaster came up in the conversation.

"Don't you remember Michelle?", said Larry. "You know, the brunette in the sales department with legs to die for and a smile that you couldn't resist? Well it turns out that Peter couldn't resist either. They were having an affair."

"That's not that big a deal. I mean it could be, but unless it got in the way of business, what's the problem?", said Sergio.

"Well, it's a problem if the CFO finds out. And the CFO is the CEO's wife."

Turns out that immediately after the CFO discovering the affair, Michelle was let go. Probably not the smartest decision, given the opening for a lawsuit that provided to Michelle. Of course, Peter was a little too busy with other things to even think about disabling an account. In fact, no one found out about the termination until the following Monday. And Michelle decided to take a different path than the legal one anyway. Apparently, the entire company was enlisted, full-time for three weeks, to do nothing be entering the information back into the system. Not an orthodox recovery plan, to be certain. But less painful (and less expensive) than Peter's upcoming divorce proceedings promised to be.

One of the subjects that was raised again at last week’s  A3 IT Question Time in London was that of storage tiering.  To recap, the concept is pretty straightforward; you install storage within an array or architecture that has multiple levels of performance and data is placed on the appropriate tier based on its performance requirements.  In more complex environments, data can be dynamically moved between tiers as required.  However I believe the tiering model as it exists today has come to the end of its useful life.  The reasons for this include: Rigidity – Tiering is a fixed model.  You install categories of storage in the array and your server gets whatever performance level that tier can provide.  However each of those tiers are fixed steps (for example the difference between 15K & 10K drives) which means inevitably, some hosts are receiving more IOPS capability than they demand, and that’s wasted resource.  Dynamic tiering helps with this but of course is typically implemented over a measurement period in days rather than seconds or milliseconds.  If a workload profile changes, adding “adding more IOPS” means physically installing and de-installing disks.  This issue can be mitigated somewhat by adding in disk to match the workload profile as part of a growth plan, but that still means effort in planning and internal data migration. Waste – To achieve dynamic tiering means having spare capacity available to promote or demote data.  This immediately means disk space is being under-utilised just in case a requirement to move data arises.  Creating many levels of tiering also creates waste because it is difficult to guess in advance what amounts of each tier will be required at array deployment time. Complexity - Tiering models are just too complex to manually interact with.  As environments scale and change dynamically, the tiering model needs to be reviewed frequently, even when dynamic/automated tiering is implemented.  Having many tiers across multiple architecture from multiple vendors means a lot of work in getting a consistent architecture.   Best Efforts Essentially, almost all storage arrays today work on the idea of best efforts.  All I/O is delivered as fast as possible, with algorithms and queuing techniques used to improve throughput.  This shouldn’t be a surprise, because data was always stored on an unpredictable persistent medium that was orders of magnitude slower than the compute and memory components – a medium known as the hard disk drive.  When I/O response time from disk is unpredictable, there’s no way to implement a quality of service algorithm that can guarantee response time.  The only way to reduce latency is to keep more data in cache and have a big cache, but that then creates issues of data integrity, scale out/up and power consumption.  Think of all those heavy duty batteries deployed today just to protect the cache in the event of a power failure.  So with physical disk, best efforts was all we could expect.   Infinite Tiers The ideal solution, excluding the issue of capacity, is to have a scenario that allows for an infinite number of tiers.  As the tier count increases, each server will be closer to receiving the performance level they need, with an infinite number of tiers delivering to every possible performance level.  If we can deliver that, move the data between tiers dynamically and fix the wasted capacity issue within each tier, then we have our ultimate storage device.  It may sound like a tall order, but its not really.   Storage Performance Hierarchy We are lucky to have experienced an increase in the number of methods used to store data over the last few years.  The hierarchy now goes something like this: DRAM – very, very fast, but volatile and expensive.  Not a great scale out solution. Persistent DRAM – emerging technologies like that from Diablo, where NAND is build in the DIMM form-factor, accessible on the motherboard, with good scale and persistence. Flash – in many guises – PCIe SSD, custom NAND cards, SLC SSD, MLC SSD.  Persistent storage with large capacity and scale.  Still a little expensive though. All of the above provide low latency with predictable response times.  Then there are the hard drives.  These scale from 15K RPM, through 10K, 7.2K and 5.9K and increase in capacity as speeds reduce.  Perhaps with new recording techniques we’ll see even slower drives, with much larger capacities. As we can see, we’re not just stuck with spinning disks.  There is the opportunity to build arrays from a range of devices offering a balance of performance versus capacity. Putting it all Together What we want in the future is an infinitely scaleable array that delivers I/O to a service level, rather than a fixed physical storage tier.  This means assigning latency and IOPS to each LUN.  Even at the simplest level, we have the ability to use our DRAM and Flash layers to stage reads and writes so they are delivered to the host with the guaranteed service level assigned to the data.  For example, imagine providing a guaranteed 5ms response on I/O write to a host.  In the traditional model, once the data is in non-volatile cache, the I/O would be acknowledged and de-staged to disk/flash.  But why do the acknowledgement immediately?  Why not hold it until the 5ms requirement and do it then?  There are some issues to be resolved around data integrity in this model, but those exist already today and are catered for by the array. Delivering to a service level already exists; SolidFire, a vendor I’ve mentioned many times, already does this.  However at the moment, their solution is based purely on Flash, because, unsurprisingly, Flash gives the most predictable performance while delivering high numbers of IOPS.  The evolution from SolidFire is to build a system that knows how many IOPS can be delivered from any underlying hardware (adding up disk, flash, DRAM) and match that to the IOPS requirement per LUN.  This would truly be Software Defined Storage (SDS). The Architect’s View Our array vendors may already be working on the next generation of storage that really does deliver to SDS and quality of service.  I’m hoping that there’s promise in solutions like ScaleIO to deliver to the infinite tiers model.  When we get to that point, our work in the physical storing of data will be pretty much done, and we can all move onto the next stage of getting more value out of all of this stuff we’re keeping. Related Links ScaleIO – EMC’s New Baby SolidFire Update: New Products & Review Storage QoS – How hard can it be?   Comments are always welcome; please indicate if you work for a vendor as it’s only fair.  If you have any related links of interest, please feel free to add them as a comment for consideration. Subscribe to the newsletter! – simply follow this link and enter your basic details (email addresses not shared with any other site). Copyright (c) 2013 – Brookend Ltd, first published on http://architecting.it, do not reproduce without permission.

I have made some minor updates and bug fixes for all of my SQL Server Diagnostic Information Queries for this month. I have also added a new version for SQL Server 2014.

Rather than having a separate blog post for each version, I’ll just put the links for all five versions here.

SQL Server 2005 Diagnostic Information Queries              SQL Server 2005 Blank Results

SQL Server 2008 Diagnostic Information Queries              SQL Server 2008 Blank Results

SQL Server 2008 R2 Diagnostic Information Queries         SQL Server 2008 R2 Blank Results

SQL Server 2012 Diagnostic Information Queries              SQL Server 2012 Blank Results

SQL Server 2014 Diagnostic Information Queries              SQL Server 2014 Blank Results

About half of the queries are instance specific and about half are database specific, so you will want to make sure you are connected to a database that you are concerned about instead of the master, system database.

The idea is that you would run each query in the set, one at a time (after reading the directions). You need to click on the top left square of the results grid to select all of the results, and then right-click and select “Copy with Headers” to copy all of the results, including the column headers to the Windows clipboard. Then you paste the results into the matching tab in the spreadsheet. There are also some comments on how to interpret the results after each query.

Note: These queries are stored on Dropbox. I occasionally get reports that the links to the queries and blank results spreadsheets do not work, which is most likely because Dropbox is blocked wherever people are trying to connect.

I also occasionally get reports that some of the queries simply don’t work. This usually turns out to be an issue where people have databases in 80 compatibility mode, which breaks many DMV queries.

Please let me know what you think of these queries. Thanks!

The post SQL Server Diagnostic Information Queries for September 2013 appeared first on Glenn Berry.

Symon Perryman, Microsoft IT Pro Evangelist, was recently asked by the System Center Advisor team to help evangelize the new System Center cloud service called System Center Advisor. We are happy to announce that an end-to-end walkthrough of a System Center Advisor setup and configuration has been published to the Edge show! You can find this session here: http://channel9.msdn.com/Shows/Edge/Edge-Show-75--Proactive-Datacenter-Monitoring-using-System-Center-Advisor

In this episode, Symon discusses how Advisor works to find best practices for all of your Microsoft Infrastructure such as:

· Hyper-V

· SQL Server

· Exchange

· SharePoint

· Lync

· SC: Virtual Machine Manager

The entire demo spans a week of time allowing Advisor to provide real world recommendations and best practices. It’s a fantastic overview of live data that can be utilized to help you understand what the tool is capable of. Thanks to Symon and his team for putting this together.

/Enjoy!

Christian Booth (ChBooth) | Sr. Program Manager | System Center

Program Lead: System Center: Cloud & Datacenter MVP

If you’ve read my prior post about Storage Spaces, or about how to import a storage pool, then the discussion here will be a good addition.  One of the clear advantages of using a storage pool is the ability to expand the pool by adding additional storage.  That is great if you’re doing any thin provisioning and reach a point that you need more space.  Perhaps you’ve simply acquired some additional drives.   Adding them to the pool is fairly easy: right-click on the storage pool and choose Add Physical Disk…

Some of you have tried this and found it easy.   Once you’ve expanded an existing pool, you can add other virtual disks or even extend those you have.   However, there are occasions where attempting to extend a virtual disk can be problematic…and for good reason.  However, the reason may not seem readily apparent.  This difficulty can be further confusing if some virtual disks extend, but others indicate they are using the maximum available space while the pool continues to have unused space available.

How virtual disks stored on the pool?  

The answer to that question depends on what options were chosen when the virtual disk was created.  For instance, you may have selected mirroring or other options.   To make this simple, let’s assume you had 5 disks in the pool at the time you created a virtual disk.  Let’s also assume that due to whatever options were chosen for redundancy, the virtual disk (vdisk1) occupies space on disk similar to the following:

 

Figure 1: Virtual Disk Layout Example

In this example, the blocks for the virtual disk vdisk1 are distributed across 5 devices.  Remember, also that Storage Spaces does not require that all devices be the same size to be part of a pool.  Let’s also entertain the possibility of a second virtual disk (vdisk2) that is only spread across 3 disks.   If you add a disk to the pool, it might look like the following:

 

Figure 2: Expanded Storage Pool

If you attempt to extend vdisk1, the maximum available space for that disk has already been realized even though additional space exists within the pool.   This is because the layout required by the vdisk1 due to the options chosen at creation (mirroring, parity, etc.) are requiring 5 disks.  Therefore, to expand vdisk1, you would need to add 4 more disks.

If you attempt to extend vdisk2, you would have the option to do so because that disk is currently spread across 3 devices and there exists available space across three devices to extend it.  Storage Spaces refers to the arrangement of blocked storage as columns.  So, in a pre-expanded state, vdisk1 uses 5 columns and vdisk2 uses 3 columns.

Vdisk2 might just be a virtual disk that used 3-way mirroring.  Meaning that data on disk 1 is duplicated on disks 2 and 3.  If you want to expand a virtual disk like that, it has to have the appropriate number of columns available to accommodate the needs of the virtual disk.

How can I tell how many columns a virtual disk uses?

Now that I’ve hopefully shed some light upon why extending a virtual disk within a storage pool can be problematic and why, I know someone out there is asking: “How do I tell how many columns a particular virtual disk uses?”  The Get-VirtualDisk cmdlet in PowerShell is a great place to start.

 

Figure 3: Get-VirtualDisk

Get-VirtualDisk can tell you many things about a virtual disk you’ve created within a storage pool.  Given no parameters it will display the virtual disks it finds with some basic status information.  However, you don’t get the full picture.  If you pipe the returned objects to Format-List, you’ll get much more detail…perhaps more than you want.

 

Figure 4: Get-VirtualDisk | Format-List

Notice that the number of columns appears in the listing as well as the parity layout.   This particular virtual disk is different than the prior examples and uses 7 columns.  Therefore, to extend it, the pool would need the appropriate number of columns available to accommodate the layout of the disk.

For More Information about Storage Spaces:

Prior Posts about Storage Spaces

http://blogs.technet.com/b/askpfeplat/archive/2012/10/10/windows-server-2012-storage-spaces-is-it-for-you-could-be.aspx

http://blogs.technet.com/b/askpfeplat/archive/2012/12/24/windows-server-2012-how-to-import-a-storage-pool-on-another-server.aspx

TechNet Reference for Get-VirtualDisk

http://technet.microsoft.com/en-us/library/hh848644.aspx

TechNet Reference for Get-StoragePool

http://technet.microsoft.com/en-us/library/hh848654.aspx

Until next time!

Martin

 

SQL Server 2014 has enhancements to two common database maintenance operations:Partition Switching and Online Index Rebuild.  This will greatly increase enterprise application availability by reducing maintenance downtime impact.

The two new features are:

• Managed Lock Priority: This allows you to manage the priority of table locks acquired by partition SWITCH and Online Index Rebuild (OIR), thereby allowing you to mitigate any negative performance impact of these locks on the primary workload on the server.
• Single Partition Online Index Rebuild - This features allows you to rebuild indexes online for individual partitions, thereby increasing application uptime.  This is great for large partition tables.

More Info:

Exploring Partition-Level Online Index Operations in SQL Server 2014 CTP1

Improved Application Availability During Online Operations in SQL Server 2014

Exploring Low Priority Lock Wait Options in SQL Server 2014 CTP1

Starting today I will be visiting CERN, the Conseil Européen pour la Recherche Nucléaire with 11 other STEM social media advocates. What is CERN? It’s the location of the Large Hadron Collider and the birthplace of the World Wide Web. From the CERN Website:

At CERN, the European Organization for Nuclear Research, physicists and engineers are probing the fundamental structure of the universe. They use the world’s largest and most complex scientific instruments to study the basic constituents of matter – the fundamental particles. The particles are made to collide together at close to the speed of light. The process gives the physicists clues about how the particles interact, and provides insights into the fundamental laws of nature.

I will be sharing facts, images and videos via this blog and other social media.

I will be tweeting about this event a lot using the hashtag #CERNTweetup. If you aren’t interested in this sharing, you can use the filtering mechanism of your Twitter client to avoid those tweets.

…or your can use the search feature of your client to follow the tweets of all the invitees. Isn’t metadata great that way?

I was working on an index build issue for an 80 CPU system and kept seeing that only 64 CPUs were getting used. I had carefully studied sys.dm_os_spinlock_stats and sys.dm_os_wait_stats along with performance counters, memory usage pattern, and I/O activities.   In fact, I had an 80 CPU, 2TB RAM, 4TB SSD system so I was convinced the SQL Server was CPU bound and adding more CPUs for the index build could be a benefit.

Note: I must caution you that adding more CPUs can lead to reduced performance because a bottleneck such as memory or I/O can become a larger problem.  

I like to think of it like pumping gas at my favorite filling station.  The pump at the storage tank can only move so much liquid through a finite pipe size.   Adding more filling outlets for patrons to use does not mean the overall flow of gas (gallons/sec) increases.  In fact, I like filling when no one else is filling because I maximize my flow and reduce my overall time at the pump. 

TEST any MAXDOP setting well as you might find less goes faster.

After looking at various SQL Server Books Online references and then stepping though the code I realized our documentation is not as accurate as it could be.   I hope I this post can reduce some of the confusion.

There are plenty of references for tuning MAXDOP to allow queries to run at their best while reducing the overhead of the parallelism.  You have all seen the references for capping MAXDOP at 8, or number of schedulers of the NUMA node if smaller, or ….   The fact is, these are all great and recommended best practices.

• http://support.microsoft.com/kb/2806535
• http://blogs.msdn.com/b/psssql/archive/2013/08/28/i-want-to-know-why-my-query-is-not-parallelized.aspx 
• http://technet.microsoft.com/en-us/library/ms181007(v=SQL.105).aspx 
• http://blogs.msdn.com/b/psssql/archive/2011/02/03/resource-governor-and-maxdop-parallel-plan-generation.aspx 
• http://blogs.msdn.com/b/psssql/archive/2008/02/13/how-it-works-sql-server-per-query-degree-of-parallelism-worker-count-s.aspx 

This post is not intended to contradict any of the current recommendations.  This blog is solely focused on a specific maintenance target.    The reset of my system has to be idle so I can safely consume the majority of schedulers.  It is probably the middle of the night, users are sleeping and you want to schedule a job that can take full advantage of the overall system.  I have reviewed various performance points and I believe using a high level of parallelism could allow my index build to complete quickly.

Warning: Index fragmentation with increased levels or parallelism: http://blogs.msdn.com/b/psssql/archive/2012/09/05/how-it-works-online-index-rebuild-can-cause-increased-fragmentation.aspx

There are several stages to determining the degree of parallelism (MAXDOP) a query can utilize.

Stage 1 – Compile

During complication SQL Server considers the hints, sp_configure and resource workgroup settings to see if a parallel plan should even be considered.  Only if the query operations allow parallel execution:

If hint is present and > 1 then build a parallel plan

else if no hint or hint (MAXDOP = 0)

          if sp_configure setting is 1 but workload group > 1 then build a parallel plan

           else if sp_configure setting is 0 or > 1 then build parallel plan

Stage 2 – Query Execution

When the query begins execution the runtime, degree of parallelism is determined.  This involves many factors, already outlined in SQL Server Books Online: http://technet.microsoft.com/en-US/library/ms178065(v=SQL.105).aspx 

Before SQL Server looks at the idle workers and other factors it determines the target for the degree of parallelism.

if sp_configure or query hint forcing serial plan use (1)

else if resource workgroup set

    if query hint present use min(hint, resource workgroup)

     else use resource workgroup

If still 0 after the detailed calculations it is set to 64  (default max for SQL Server as documented in Books Online.)   This fooled me some because on the 80 CPU system it has 2 Windows scheduler groups x 40 CPUs.   I might have expected a 40 CPU cap to avoid crossing over a Windows scheduler group.   This is not the case, SQL Server hard codes the 64 CPU target when the runtime target of MAXDOP is still 0 (default.)

The MAXDOP target is now adjusted for:

• Actual CPU count (affinity settings from sp_configure and the resource pool). 

• Certain query types (index build for example) look at the partitions

• Other query type limitations that may exist

Now SQL Server takes a look at the available workers (free workers for query execution.)   You can loosely calculate the free worker count on a scheduler using   (Free workers = Current_workers_count – current_tasks_count) from sys.dm_os_schedulers

Once the target is calculated the actual is determined by looking at the available resources to support a parallel execution.  This involves determining the node(s) and CPUs with available workers.

Older versions of SQL Server used a polling mechanism every ~1 second to determine the node with the most free workers to target.   This meant you could encounter race conditions from multiple queries, both going parallel on the same node when going parallel on separate nodes would have resulted in better CPU usage.

 

Newer builds of SQL Server actively track the free workers.   This significantly reduces the possibility of assigning parallel queries to the same set of schedulers.

Trace flag 2466 - Force older version logic to determine number of available resources.

The worker location information is then used to target an appropriate set of CPUs to assign the parallel task to.

In general the placement decisions are:

• SMP (FPlaceThreadsOneNodeSystem): If a single node system treat as SMP and only enqueue to a single node. SOFT NUMA and affinity may cause SQL to treat as SMP
  

• CONNECTION (FPlaceThreadsOneNodeSystem): If trace flag 2479 is enabled force all parallel decisions to be limited to the node the connection is associated with.  This may be helpful when using SOFT Numa or connection, node affinity.
  

• FULL (FPlaceThreadsAllNodes): If MAXDOP target is equal to all schedulers enqueue work to all schedulers
  

• LEAST (FPlaceThreadsWithinLeastLoadedNode): If target MAXDOP target is less than a single node can provide and if trace flag 2467 is enabled attempt to locate least loaded node 
  

• SPREAD (FPlaceThreadsMultipleNodes): The load is spread across any available node

 

Using XEvents you can monitor the MAXDOP decision logic.  For example:

• XeSqlPkg::calculate_dop_begin
• XeSqlPkg::calculate_dop

Back to trying to get my index build to use all 80 CPUs.  I can do several things:

1. Use MAXDOP=80 query hint

2. Set sp_configure ‘max degree of parallelism’, 80    -- Warning this applies to any query

3. Create resource pool/workload group and set MAXDOP=80 and assign only the index build connection to it using a resource governor classifier.

Testing Results

Here are the MAXDOP results on my 80 CPU system at different setting levels.

Query Hint	sp_configure	Workgroup	RUNTIME
0	0	80	80
0	0	0	64
1	0	80	1
2	0	80	2
0	80	0	80
0	1	0	1
0	2	0	2
80	2	0	80
80	10	2	2

You can monitor the number of parallel workers by querying:

sys.dm_os_tasks

Note:  Some configuration changes may require a flush of procedure cache (dbcc freeproccache) or a disconnect/connect pairing to take affect.

 

Bob Dorr - Principal SQL Server Escalation Engineer

A common issue I've run into while helping with SQL Server Failover Cluster (FCI) installations is the failure of the Network Name. In the following post I'll discuss a bit of background, the common root cause, and how to resolve it.

Background

The SQL Server Database Engine service is dependent on the Network Name resource. A failure of the Network Name will result in the SQL Server Resource not coming online.

When the Windows Failover Cluster (WFC) is initially configured a Cluster Name object (CNO) will be created. The CNO is visible as a computer object in your Activity Directory Users and Computer snap-in (dsa.msc). By default the CNO will be created in the Computers container and granted specific permissions:

After a successful SQL Server FCI installation you will now see a Virtual Computer Object (VCO) for the SQL Server Network Name:

*Note: After the CNO is created any additional Network Name resource in the cluster is considered a Virtual Computer Object. VCO’s are simply Computer objects in which the CNO has permissions to change the properties or reset the password.

Problem

But what if the CNO does not possess the required permissions to create computer objects in the “Computers” container?

It is in the above scenario where we commonly see the following errors during SQL Server FCI installation:

The following error has occurred:

The cluster resource 'SQL Server (SQL2012)' could not be brought online due to an error bringing the dependency resource 'SQL Network Name(VSQL2012)' online. Refer to the Cluster Events in the Failover Cluster Manager for more information.

A user encountering the same issue while installing a pre-SQL Server 2012 version may see:

The cluster resource 'SQL Server (MSSQLSERVER)' could not be brought online.  Error: The resource failed to come online due to the failure of one or more provider resources. (Exception from HRESULT: 0x80071736)

System log:

Cluster network name resource 'SQL Network Name (VSQL2012)' failed to create its associated computer object in domain 'motox.com' during: Resource online.

The text for the associated error code is: A constraint violation occurred.

Please work with your domain administrator to ensure that:

- The cluster identity 'CLUS2012$' has Create Computer Objects permissions. By default all computer objects are created in the same container as the cluster identity 'CLUS2012$'.

- The quota for computer objects has not been reached.

- If there is an existing computer object, verify the Cluster Identity 'CLUS2012$' has 'Full Control' permission to that computer object using the Active Directory Users and Computers tool.

Cluster log:

[RES] Network Name: [NNLIB] Creating object VSQL2012 using ADSI in OU OU=SQL,DC=motox,DC=com on DC: \\MOTOXDC.motox.com, result: 8239

[RES] Network Name: [NNLIB] Failed to create Computer Object VSQL2012 in the Active Directory, error 8239

Cause

The common cause of the Network Name resource failure is insufficient permissions. More specifically, the permission "Create Computer Objects" has not been granted to the Cluster Name Object(CNO).

http://technet.microsoft.com/en-us/library/cc731002(v=ws.10).aspx

“…when you create a failover cluster and configure clustered services or applications, the failover cluster wizards create the necessary Active Directory computer accounts (also called computer objects) and give them specific permissions. The wizards create a computer account for the cluster itself (this account is also called the cluster name object or CNO) and a computer account for most types of clustered services and applications”

When the SQL Server Network Name is first brought online during the FCI installation process, the CNO identity is used to create the VCO(as long as the VCO doesn’t already exist). If the required permissions are not granted to the CNO, the creation of the VCO will fail and so will your SQL Server FCI installation.

*Note: The Create Computer objects right only applies to Domain Functional Levels above Windows Server 2003. For Windows Server 2003 the required privilege is “Add Workstations to the Domain”.

Resolution(s)

Option #1

We must grant the permissions "Read all properties" and "Create Computer objects" to the CNO via the container. Here's an example of granting the required permissions for demonstration purposes:

1. Open the Active Directory Users and Computers Snap-in (dsa.msc).

2. Locate “Computers” container:

3. Make sure "Advanced Features" is selected:

4. Open the properties of the container and click the "Security" tab. Click "Add" and add the CNO. Make sure to select “Computers” option in the “Object Types” window:

5. Click "Advanced", highlight the CNO, and click "Edit":

6. Make sure "Read all properties" and "Create Computer objects" are checked. Click OK until you're back to the AD Users and Computer window:

7. Retry your previously failed installation. Note that with SQL Server 2012 there will be a “retry” button.

Option # 2

We can also “Pre-Stage” the VCO, which is useful in situations where the Domain Administrator does not allow the CNO “Read All Properties” and “Create computer Objects” permissions:

1. Ensure that you are logged in as a user that has permissions to create computer objects in the domain.

2. Open the Active Directory Users and Computers Snap-in (dsa.msc).

3. Select View -> Advanced Features.

4. Right click the OU/Container you want the VCO to reside in and click “New” -> “Computer”

5. Provide a name for the object (This will be your SQL Server Network Name) and click “OK”:

6. Right click on the on the VCO you just created and select “Properties”. Click the security tab and then click “Add”:

7. Enter the CNO (Make sure to select “Computers” option in the “Object Types” window) and click “OK”.

8. Highlight the CNO, check the following permissions, and click “OK”.

Read

Allowed To Authenticate

Change Password

Receive As

Reset Password

Send As

Validate write To DNS Host Name

Validate Write To Service Principle Name

Read Account Restrictions

Write Account Restrictions

Read DNS Host Name Attributes

Read MS-TS-GatewayAccess

Read Personal Information

Read Public Information

*Note: You can replace step #8 by giving the CNO “Full Control” over the VCO

9. Install SQL Server and the Network Name resource should start without issue.

References:

Failover Cluster Step-by-Step Guide: Configuring Accounts in Active Directory

http://technet.microsoft.com/en-us/library/cc731002(WS.10).aspx

Before Installing Failover Clustering

http://msdn.microsoft.com/en-us/library/ms189910.aspx/html

Add workstations to domain

http://technet.microsoft.com/en-us/library/cc780195(v=WS.10).aspx

Troy Moen – Support Escalation Engineer

PCWorld reports that "[a] U.S. surveillance court has given the National Security Agency no limit on the number of U.S. telephone records it collects in the name of fighting terrorism, the NSA director said Thursday. The NSA intends to collect all U.S. telephone records and put them in a searchable 'lock box' in the interest of national security, General Keith Alexander, the NSA's director, told U.S. senators." But don't worry; it's just metadata, until it isn't. (Your row in the NSA database may already be getting cozy in its nice new home in Utah.)

Read more of this story at Slashdot.

New submitter SpacemanukBEJY.53u writes "In a declaration that could make Google very nervous, a U.S. federal judge on Thursday rebuffed Google's defense of its targeted ad system that scans the content of Gmail. Judge Lucy Koh — who also heard the Apple-Samsung case — found Google's terms and conditions and privacy policy isn't clear to users. Koh subsequently allowed a class-action suit to proceed against the company (official ruling). The plaintiffs in the suit allege Google violates federal and state wiretap laws by scannning the messages sent by non-Gmail and Gmail users."

Read more of this story at Slashdot.

Hugh Pickens DOT Com writes "CNN reports that Jared James Abrahams, a 19-year-old computer science student, has been arrested for allegedly hijacking the webcams of young women — among them reigning Miss Teen USA Cassidy Wolf — taking nude images, then blackmailing his victims to send him more explicit material or else be exposed. Abrahams admitted he had 30 to 40 'slave computers' — or other people's electronic devices he controlled — and has had as many as 150 total. His arrest came six months after a teenager identified in court documents as C.W. alerted authorities. She has since publicly identified herself as Cassidy Wolf, the recently crowned Miss Teen USA. Wolf received messages featuring pictures of her at her Riverside County address and others apparently taken months earlier when she lived in Orange County, says the criminal complaint (PDF). The message explained 'what's going to happen' if Wolf didn't send pictures or videos or 'do what I tell you to do' in a five-minute Skype videoconference, according to the criminal complaint. 'Either you do one of the things listed below or I upload these pics and a lot more (I have a LOT more and those are better quality) on all your accounts for everybody to see and your dream of being a model will be transformed into a pornstar (sic),' wrote Abrahams. FBI agents raided Abrahams' Temecula home in June and seized computers and hardware, cellphones and hacking software, court records show. Outside the court, Abrahams' lawyer, Alan Eisner, said that his client's family feels 'profound regret and remorse' over what happened. Eisner told CNN affiliate KTLA that Abrahams is autistic. 'The family wants to apologize for the consequences of his behavior to the families who were affected.'"

Read more of this story at Slashdot.

An anonymous reader writes "In the process of standardizing the SHA-3 competition winning algorithm Keccak, the National Institute of Standards and Technology (NIST) may have lowered the bar for attacks, which might be useful for or even initiated by NSA. 'NIST is proposing a huge reduction in the internal strength of Keccak below what went into final SHA-3 comp,' writes cryptographer Marsh Ray on Twitter. In August, John Kelsey, working at NIST, described (slides 44-48) the changes to the algorithm, including reduction of the bit length from 224, 256, 384 and 512-bit modes down to 128 and 256-bit modes."

Read more of this story at Slashdot.

Some time ago, Marla was asked to take a look at a problematic data load to help figure out exactly what the heck was going wrong with it and report back to management.

The process was supposed to be simple: download an XML file from a web service and then merge the data to the database, updating and inserting as needed.

It worked, but the time it took to process a 500 KB test file wasn't exactly stellar - in all, it took several minutes to finish. Not necessarily an issue but when files are normally 50 MB or more, it was taking hours to process a single file.

After sifting through the project's VB code, Marla was able to narrow down the core WTF of the data load process:

Public Shared Sub LoadXMLtoDataset(ByVal xmlfile As String, ByVal schemafile As String, ByVal conn As String)

    For Each dataTable In dataSet.Tables
      dataTable.BeginLoadData()
    Next

    dataSet.ReadXml(xmlfile)
    For Each dataTable In dataSet.Tables
      Dim tablename As String = dataTable.TableName
      Dim row As DataRow
      For Each row In dataTable.Rows
        'Check if there is a new table found in the XML which cannot be found in the database.  
        If iTableProperty.CheckTableExists(dataTable.TableName, conn) = True Then
          Dim declareTableVar As String = "Declare "
          Dim setValues As String = Nothing
          Dim mergesqlcmd As String
          Dim selectsqlcmd As String = " Select "
          Dim updatesqlcmd As String = "Update Set "
          Dim insertsqlCmd As String = "Insert ("
          Dim matchfields As String = ""

          'Iterate the datatable columns in order to form an insert, a select, a declare and set statements of a merge 
          For i As Integer = 0 To dataTable.Columns.Count - 1
            If dataTable.Columns(i).ColumnName.ToString().Trim  "ID" Then
              'Check if there is a new column found in the XML which cannot be found in the database.  
              If iTableProperty.CheckColumnsExists(dataTable.TableName, dataTable.Columns(i).ColumnName.ToString(), conn) = True Then   ' target." + dataTable.Columns(i).ColumnName.ToString() + " Or "
                selectsqlcmd = selectsqlcmd + "@" + dataTable.Columns(i).ColumnName.ToString() + " as " + dataTable.Columns(i).ColumnName.ToString() + ","
                insertsqlCmd = insertsqlCmd + dataTable.Columns(i).ColumnName.ToString() + ","
              End If
            End If
          Next
          insertsqlCmd = insertsqlCmd.Substring(0, insertsqlCmd.Length - 1) & ") values ("
          selectsqlcmd = selectsqlcmd.Substring(0, selectsqlcmd.Length - 1)
          declareTableVar = declareTableVar.Substring(0, declareTableVar.Length - 1)
          setValues = setValues.Substring(0, setValues.Length - 1)
          matchfields = matchfields.Substring(0, matchfields.Length - 3)

          'Iterate the DataTable columns for constructing an insert statement.
          For x As Integer = 0 To dataTable.Columns.Count - 1
            ' Add the column value for this row
            If dataTable.Columns(x).ColumnName.ToString().Trim  "ID" Then
              Dim ConvertedInsertval As Object = Nothing
              insertsqlCmd = (insertsqlCmd) & "@" + dataTable.Columns(x).ColumnName.ToString() & ","
            End If
          Next
          insertsqlCmd = insertsqlCmd.Substring(0, insertsqlCmd.Length - 1) & ");"
          'Iterate the DataTable columns for constructing an update statement.
          For i As Integer = 0 To dataTable.Columns.Count - 1
            ' Add the column name
            If dataTable.Columns(i).ColumnName.ToString().Trim  "ID" Then
              If dataTable.Columns(i).ColumnName.ToString().Trim  "ID" Then
                Dim ConvertedUpdateVal As Object = Nothing
                updatesqlcmd = updatesqlcmd + dataTable.Columns(i).ColumnName.ToString() & "=" & "@" + dataTable.Columns(i).ColumnName.ToString() & ","
              End If
            End If
          Next
          updatesqlcmd = updatesqlcmd.Substring(0, updatesqlcmd.Length - 1)
          'Form the merge statement and execute it.
          mergesqlcmd = " " & declareTableVar & ";" & _
             "" & setValues & "" & _
             " Merge " & "dbo." & getWord.findWord(dataTable.TableName) & " as Target" & _    '

Marla ...didn't really know where to begin... So many problems. Merging data one row at a time...was the merge command really meant to work that way? Making calls to the database to check if columns and tables exist after the XML file has already been validated earlier in the program. And perhaps the most infuriating detail: THE FACT THAT THE ORIGINAL DEVELOPER DIDN'T THINK THAT MAKING OVER 40,000 CALLS TO THE SQL SERVER TO PROCESS A SMALL DATA FILE WAS GOING TO LEAD TO “SLOW” PERFORMANCE!

But Marla - being the cool headed professional that she is - simply identified the offending procedure in its home source file, called out a handful of the really big problems, and sent her report off to management.

The reply from her higher ups was surprisingly swift. As she opened the reply, Marla half expected to find that she had been "nominated" to author the solution, which wouldn't have been that bad of a task, but instead, it wasn't anything like what she expected.

________________________________________________________
From: herb.jenkins@magenta.corp
To: marla.nonymous@magenta.corp
Subject: RE: Dataload Performance Analysis

Thanks for that great analysis, Marla!

We haven't heard anything from the users so we're going to sit on this one until someone complains.

Regards,
Herb Jenkins
Senior Project Lead
Magenta Corporation

By Shawn Cannon A new feature has been added in the new 5.5 release of ESXi. You can now clear the ARP cache on an ESXi server. This is a very useful feature that should have always been part of ESXi. This feature ONLY works with ESXi 5.5. The KB article contents are below and were copied from http://kb.vmware.com/kb/2049667 Clearing the ARP cache table in ESXi 5.5 (2049667) Purpose This article provides information on the new esxcli command introduced in vSphere 5.5 to clear the ARP table. ESXi 3.x, 4.x, and ESXi 5.0/5.1 do not include any mechanism to clear the ARP table. For more information, see Troubleshooting network connection issues using Address Resolution Protocol (ARP) (1008184). Resolution vSphere 5.5 introduces the a new esxcli network ip neighbor remove command to clear the ARP cache table. To clear the ARP cache table in ESXi 5.5, use this command: esxcli network ip neighbor remove [options] Where options includes: -i string or --interface-name=string Where string is the name of the VMkernel network interface from which the neighbor entry must be removed. If this option is not specified, the neighbor is removed from all interfaces. -a address or --neighbor-addr=address Where address is the IPv4/IPv6 address of the neighbor. This is mandatory. -N instance or --netstack=instance Where instance is the network stack instance. If unspecified, the default netstack instance is used. -v number or --version=number Where number is the IP version and can either be 4 or 6. This is mandatory. For example, to delete the ARP entry for address 10.131.0.103: Connect to the ESXi 5.5 host using SSH. For more information, see Using Tech Support Mode in ESXi 4.1 and ESXi 5.x (1017910). View the current ARP table using this command: # esxcli network ip neighbor list You see output similar to: Neighbor Mac Address Vmknic Expiry State Type ------------ ----------------- ------ ------- ----- ----- 10.131.0.103 00:1c:c4:a9:6f:fc vmk0 908 sec Unknown 10.131.0.179 00:1e:0b:bf:7a:50 vmk0 1062 sec Unknown To delete the ARP entry for address 10.131.0.103, run one of these commands: # esxcli network ip neighbor -v 4 -a 10.131.0.103 # esxcli network ip neighbor --version=4 --neighbor-addr=10.131.0.103 View the ARP table again using this command: # esxcli network ip neighbor list You see output similar to: Neighbor Mac Address Vmknic Expiry State Type ------------ ----------------- ------ ------- ----- ----- 10.131.0.179 00:1e:0b:bf:7a:50 vmk0 750 sec Unknown Source: vBrainstorm

At VMWorld 2013, EMC announced its most popular product RecoverPoint for the hypervisor. As most of you know EMC RecoverPoint – one of the most robust data protection options was up until VMWorld available only as an appliance. At VMWorld, Chad Sakac (EMC SVP) and Yossi Saad (Director – RecoverPoint Product Management) announced that a beta version of the RecoverPoint for Hypervisor software is available. In near future, the software will be GA so many VMware customers can take advantage of it. I had the opportunity to talk to Yossi Saad who spoke with me about the benefits of Software Defined Data Protection which was aptly announced at the Software Defined Datacenter event (VMWorld 2013). Yossi spoke about the product, offered some details, and clarified the Integration capabilities of the software. Many thanks to Yossi for talking to me about the product’s vision and newer capabilities. Video is below:

 

Editor’s note: The following post was written by Dynamics CRM MVP Ramon Tebar

Custom Microsoft Dynamics CRM Messaging Integration

Messaging integrations are very common in enterprise deployments, where reliability and scalability are highly demanded. This article describes how to implement a simple custom messaging solution in Microsoft Dynamics CRM using the standard development components of this platform. The source code of this article can be found in the next Codeplex project:

http://dyncrmmessagingintegration.codeplex.com/

Architecture Overview

As our initial technical requirement, we have assumed that the Microsoft Dynamics CRM application needs to send the new customers to “System X” and “System X” is going to send the new competitors to CRM.

These operations have been implemented with the XML messages “NewCustomer” and “NewCompetitor”, see below the schema definition (XSD).

Between CRM and “System X”, we assume there would be another system which acts as the channel to transport these messages. This articles doesn’t cover the details of how this middle tier should be implemented, since we could write a book only about that. Ideally, I would recommend a message queue platform.

With those elements in mind, we have the next picture:

 

Messages definition (see “IntegrationMessagesDefinition.xsd” in the Codeplex solution):

<xs:complexType name="Message">

    <xs:sequence>

      <xs:element  name="MessageName" minOccurs="1" maxOccurs="1" nillable="false"

 type="xs:string" />

    </xs:sequence>

  </xs:complexType>

 

  <xs:complexType name="NewCustomer">

    <xs:complexContent>

      <xs:extension base="tns:Message">

        <xs:sequence>

          <xs:element minOccurs="1" maxOccurs="1" name="FirstName" nillable="false"

type="xs:string" />

          <xs:element minOccurs="1" maxOccurs="1" name="LastName" nillable="false"

type="xs:string" />

          <xs:element minOccurs="1" maxOccurs="1" name="MobilePhone" nillable="false"

 type="xs:string" />

          <xs:element minOccurs="1" maxOccurs="1" name="Email" nillable="false"

type="xs:string" />

        </xs:sequence>

      </xs:extension>

    </xs:complexContent>

  </xs:complexType>

 

  <xs:complexType name="NewCompetitor">

    <xs:complexContent>

      <xs:extension base="tns:Message">

        <xs:sequence>

          <xs:element minOccurs="1" maxOccurs="1" name="Name" nillable="false"

type="xs:string" />

          <xs:element minOccurs="1" maxOccurs="1" name="WebSiteURL" nillable="false"

type="xs:string" />

        </xs:sequence>

      </xs:extension>

    </xs:complexContent>

  </xs:complexType>

 

Now that we have defined our general scenario, the next section zooms into the CRM side and it describes how it has been developed.

CRM Solution Components

Microsoft Dynamics CRM is a flexible platform which allows client and server extensions based on standard technologies such as JavaScript, WCF services, WF workflows and .NET libraries. These extensions are called solution components, and they are grouped in a solution package, which can be exported and imported between environments as a ZIP file.

In our current implementation, the CRM solution package contains the next main components:

• ·         “Message” entity. The Message entity holds the XML message to be sent or received. “Message Direction” is a custom attribute to indicate the message direction (IN/OUT)

Notes: Microsoft Dynamics CRM implements a relational data model based on SQL server, where each entity is a SQL table.

• ·         “Message Processor” workflow. When a new message record is created, this workflow is run and it processes the message, reading the XML message and triggering the corresponding actions.
• ·          “Process Message” custom Workflow Activity. The “Message Processor” workflow above described uses this customer workflow activity to instantiate the corresponding actions. This custom workflow activity is part of the assembly “DynCRMMessaginIntegration.Workflow”

The next screenshot shows the actual CRM solution that you can download from Codeplex. As you can see, it contains another workflow “Send New Contact-Customer Message”. This workflow creates a new message record after a new CRM contact has been created.

 

“Send New Contact-Customer Message” workflow:

 

Message properties:

 

“Send New Contact-Customer Message” workflow is just an example of how you can create new CRM integration messages, however you should understand it can be easily extended to any other CRM interface. That said, the “NewCompetitor” message could be created in CRM by an external process that read message from the queue “SystemX.To.CRM” and call the CRM Web services. As I mentioned at the beginning, that part is not in the scope of this article.

CRM Development

For the CRM development I have used the CRM Developer toolkit, which provides some facilities within Visual Studio 2012. As you can see in the screenshot below, the Visual Studio solution is composed by three projects:

• ·         Common: library with the main logic to instantiate the message processors. It also contains the XSD file “IntegrationMessagesDefinition.xsd” with the messages definition. Using this XSD file and the utility XSD.exe, I have generated the corresponding code to easily serialize and de-serialize the XML messages. You can find the required command in the file “GenerateIntegrationMessagesClasses.bat” and its result in “IntegrationMessagesDefinition.cs”
• ·         Workflow: library with custom workflow activities. It contains “ProcessMessageActivity.cs” and its base class “BaseCodeActivity.cs”, which inherits from “CodeActivity”. This assembly uses the Common library, which is merged using ILMerge before being deployed in the CRM environment.
• ·         CrmPackage: helper project to deploy the CRM customisations into the CRM environment. It contains references to Common and Workflow project.

 

The most interesting part of this development is the Message Processor Factory. “ProcessMessageActivity” uses “MessageProcessorFactory” to get the specific “BaseMessageProcessor”, which is instantiated using reflection. This way, the “ProcessMessageActivity” doesn’t need to know how to process each message. See below a class diagram with more details.

 

Try it on your own

I have prepared a CRM trial environment where you can play with the implementation described in the article. Bear in mind this environments expires in October 21, 2013, but remember you can download the source code and the CRM solution from Codeplex any time (http://dyncrmmessagingintegration.codeplex.com/) and deploy it in your own environment.

Dynamics CRM Trial Environment
CRM URL:	https://mycompany1001.crm4.dynamics.com
User:	MessagingTester@MyCompany1001.onmicrosoft.com
Password:	dyncrm1001!

 

Test “NewCustomer” message:

Create a new contact in CRM and wait until the “Send New Contact-Customer Message” workflow is executed. Then you can see how a new message have been created and its status is “Processed”

 

Workflow instance:

 

 

Test “NewCompetitor” message:

Create manually a new message record in CRM (“Settings” area in Sitemap) with the next XML message body:

<?xml version="1.0" encoding="utf-8"?>

<NewCompetitor xmlns="http://dyncrmmessagingintegration.codeplex.com/2013/Messages">

  <MessageName>NewCompetitor</MessageName>

  <Name>Marks And Spencer</Name>

  <WebSiteURL>http://marksandspencer.com</WebSiteURL>

</NewCompetitor>

 

After this message has been saved, a new competitor should be created in CRM.

 

About the author

Ramon Tebar is a Software Engineer specialised on Microsoft Technologies with experience in worldwide projects for different industrial sectors as consultant and developer. Pragmatic and disciplined professional concerned about design and develop reusable enterprise applications, following the best practices and design patterns over the whole development life cycle. Broad experience in Microsoft Dynamics CRM, customising and extending the platform to provide tailored solutions and integrations based on service-oriented architectures and messages queuing. Motivated by community events and contributor in blogs, technical books, open source projects and forums. Awarded by Microsoft as Most Valuable Professional (MVP) on Dynamics CRM in 2012. Participated as a speaker in several technical international events including Extreme CRM 2012 and CRM Deep Dive Technical Bootcamp.  Follow him on Twitter.

 

The MVP Monday Series is created by Melissa Travers. In this series we work to provide readers with a guest post from an MVP every Monday. Melissa is a Community Program Manager, formerly known as MVP Lead, for Messaging and Collaboration (Exchange, Lync, Office 365 and SharePoint) and Microsoft Dynamics in the US. She began her career at Microsoft as an Exchange Support Engineer and has been working with the technical community in some capacity for almost a decade. In her spare time she enjoys going to the gym, shopping for handbags, watching period and fantasy dramas, and spending time with her children and miniature Dachshund. Melissa lives in North Carolina and works out of the Microsoft Charlotte office.