Jukka Niiranen

Tag: SQL

  • Virtual Dataverse tables with no code, via Connectors

    Virtual Dataverse tables with no code, via Connectors

    The concept of a virtual table (previously: virtual entity) has existed in the Dataverse platform for quite some time already. The feature was originally introduced before XRM and Power Apps merged. This in turn means that the Connector feature used by Canvas apps and Power Automate flows is an alternative approach for the same core need: how to work with data that’s not physically stored within Dataverse?

    Since there are still three different flavours of Power Apps , let’s quickly recap what each of them think about data location:

    • Model-driven apps: “I’ll let you work with any business data, as long as it’s stored within Dataverse.”
    • Power Apps portals: “I’m essentially an external facing version of Model-driven apps, so I follow the same principle.”
    • Canvas apps: “Your data may be in whatever system you want! Just point me to the right API and wrap a Connector around it & we’re sorted.”

    The term “Model-driven” refers to the existence of a clearly defined data model, on top of which the visible app UI and background features (security, search etc.) are then generated by the Dataverse platform. You get all those features because a specific set of rules exists on how different types of data are related to one another.

    Canvas apps enjoy the freedom of taking some data from source A, another piece of data from source B, mashing them together in a common gallery, stitched together with a few lines of Power Fx code. The downside is that the app maker needs to build many of the generic features that in the Model-driven world would just magically appear within the app module.

    The best possible outcome would of course be if Power Apps were able to offer both the freedom of a Connector based Canvas app and the strong relational data management capabilities of Model-driven apps. While we are not quite there yet, some elements of the unified app / platform story are starting to emerge.

    Connectors in Model-driven apps

    Ultimately Microsoft wants to bring the Canvas and Model-driven app types as close together as possible. This means expanding the capabilities for working with external data sources in Dataverse to cover also the Connector technology. At Build 2021 the session “Dataverse for Developers” introduced the latest updates on what the sources for virtual tables can be:

    Previously the options for adding virtual tables to Dataverse was pretty much a pro-dev targeted story. The requirements for OData feeds were such that I don’t think I ever managed to find a sample feed to try out the feature. Same for the custom connectors, which are created via writing your own plugins. Technically they can be built, but if the requirements are similar to that of a traditional data integration approach, then it doesn’t exactly revolutionize the low-code data story of Dataverse.

    The new preview for Virtual Connector Provider looks more interesting, though. Supporting out-of-the-box connectivity to SQL Server databases is definitely a scenario that’s closer to the no-code level where I personally prefer to operate on. So, I decided to go and see how far this track can take me in building a Model-driven app that actually works with data not physically stored inside Dataverse.

    Even though the documents still say “private preview”, anyone can install the Virtual connectors in Dataverse solution from AppSource today:

    There’s a Power CAT Live video on YouTube that introduces the solution. If you’re like me and you prefer consuming written information instead of video walkthroughs, this PDF document will be the place to go for understanding the feature. Inside it you will find this diagram that explains the architecture of how concepts like connectors, data sources, connection references etc. relate to this new Virtual Connector Provider.

    Setting up SQL Server tables to expand your Dataverse

    I have a demo AdventureWorksLT database deployed in SQL Azure, just like the one used in Microsoft’s feature documentation for virtual connectors. I had already earlier used this demo SQL database as a data source for Power Apps Canvas apps, which meant I had an existing connection available in Power Apps Maker portal. Authentication is done with SQL username/password combo in my connection, but Azure AD authentication would also be an option if you’d rather not have stored credentials within the connection.

    After following the step-by-step instructions, including setting up an application user / service principal for the virtual connector provider, I had a brand new table visible in my Dataverse environment: “Entity Catalog for AdventureWorksLT”.

    Cool, we have a “table of tables”! I can see all the SQL Server database tables available via this connection. By opening up one of these records, I can specify that I want to create the corresponding SQL table as a Dataverse virtual table.

    I picked the Product and Product Category tables from there. (Note: modifying the table properties in the Power Apps UI doesn’t seem to work, so use the legacy web client and Solution Explorer to change things like table name.) After this, the virtual connection provider nicely maps all of the available columns in SQL into a matching Dataverse column, with the correct data type.

    I can then do the standard configuration tasks I’d perform for a native Dataverse table, such as adding views and modifying form layouts. Of course there are a number of considerations for virtual tables when it comes to the Power Apps features they support. Still, whatever works here is exactly the same experience from an app maker perspective, whether the table is “real” or virtual.

    Building a Model-driven app with virtual tables

    I created a small demo app module for testing how the different table types can co-exist and work together. I added a custom table called “Requests” and added it as the child table for both Product and Product Category virtual tables coming from SQL.

    Let’s first go and browser the external data from a view. Opening up the Products table, the experience is in practice the same as if I was browsing native Dataverse records. I can create a personal view “products currently sold” that filters out all products with a value in SellEndDate field. I can sort based on the SellStartDate. I can filter to see only products with Color value Black.

    This is already pretty darn impressive for someone coming from a Model-driven background. Sure, in the Canvas world I’ve been able to easily point a gallery to a SQL table and view the data, but having all of it available within the pre-generated Model-driven UI is a major step beyond that.

    Let’s try out how the native Dataverse table + external SQL Server tables work together on a form. Upon adding a new Request, I’m able to reference the related Product Category and Product tables via the standard lookup, just like everything would be stored in a single system. Behind the scenes, the native Request record will get references stored to the external Product Category and Product tables from SQL.

    But wait, there’s more! Did you notice that my Request form actually used the Form Component Control to show an embedded form of the Product table on the right side? Immediately upon populating the lookup field on the left side I see all the details of the selected product, just as if they were regular fields of the current record.

    In the above example I’m actually editing the Color field of the chose product with the value “White” before creating my request record. What this means is that within the same save event not only am I creating a new row in the Request table in my Dataverse, I’m also directly updating the data in my SQL Server’s Product table.

    That is powerful! No custom code was needed in creating an app UI that talks with multiple different line of business systems in real-time, on the very same form.

    From databases to Dataverses

    This simple example of simultaneously performing CRUD operations on data stored in different systems via a Power Apps form illustrates the reason why Dataverse needs to be seen as much more than just a database. It’s purpose is to be a value-add layer on top of different data storage systems, making them easy to leverage in your business apps. We already see today with the Dataverse file & image data getting stored in Azure Blob Storage and audit log entries in CosmosDB, alongside the core relational data in Azure SQL.

    The Virtual Connector Provider and virtual tables take things one step further. Especially in scenarios where you’d need to reference master data from an external system, there may not be a need to physically replicate it into Dataverse (perhaps you also want to reduce the storage costs). Specifying the virtual presence of such data will however make it appear as if it was part of the platform, thus brining it into both Model-driven apps and Canvas apps in a unified way. Even adding support for Dataverse business events to cover Power Automate is technically possible for virtual tables, although these understandably will require pro-developer involvement to get the external systems in sync with the API.

    Behind the scenes, these same concepts for virtual entities / tables are already being used by Microsoft in their first-party app features. By browsing the Data Sources within an environment we can see features like case/contact/activity suggestions listed here, as well as platform capabilities like component layers or non-relational data provider.

    Two years ago I wrote a blog post called “The Real Common Data Service Emerges” where I explored the direction where Dataverse (then CDS) was going. Since then we have seen Microsoft make the export of relational business data to a data lake a straightforward process with the built-in Azure Synapse Link for Dataverse. Similarly the import capabilities into Dataverse have expanded as the Dataflows / Power Query support keeps improving. Combine these physical data import/export pipelines with the virtual layers that the connector technology may soon offer for several tabular data sources and we’ve got a highly capable low-code toolkit for business data management needs in the Power Platform.

    You need to keep in mind that there are many considerations (read: limitations) for using virtual tables to review before deciding if they are a good fit for your business requirements. Even in building the above demo app there were things that don’t quite work the same way as with real Dataverse tables. For instance, I can’t specify a 1:N relationship between the two virtual tables for Product Categories and Products. Quick Find on the SQL data doesn’t seem to produce any meaningful results. Referencing virtual tables via lookups in a Canvas app seems to not retrieve related data at all times. Not to mention the fact that in two different environments the whole Virtual Connector Provider configuration process got stuck before any SQL tables ever materialized in the Entity Catalog.

    So, keep in mind that this is a preview of things to come, rather than production ready functionality to use today.

    Update 2021-08-20: the feature has now been officially released in public preview format, with new documentation available. Check out the Docs page “Create virtual tables using the virtual connector provider (preview)” that contains the information previously only available in the aforementioned PDF.

  • Creating a copy of your production CRM database

    If you have an on-premises CRM server and you’re running Dynamics CRM 2011, then you’re eligible to having more than one database in your CRM environment. Back when the multi-tenancy concept was introduced in CRM 4.0, there was a distinction between Workgroup, Professional and Enterprise editions. Only the Enterprise version granted you the right to run several CRM databases on a single server. With CRM 2011 the Professional and Enterprise editions were merged, which means that almost all Dynamics CRM customers can now enjoy the benefits of multi-tenancy. (The Workgroup edition still has a limitation of max 5 users, which in this age of cloud computing means hardly anyone would choose that version and put up a server for it, so let’s ignore that one.)

    Why would you need more than one CRM database? One possible reason could be the XRM approach: for no additional license or hardware costs you could be using your Dynamics CRM environment for also managing other processes than sales, marketing and service that traditionally are the focus area of CRM systems, while keeping it separate from your main customer database (HR, IT service desk, project management etc.). In case you’re happy with focusing on customer relationship management for now, then a typical reason for needing another database is that you want to test some customizations in an environment that won’t mess with your live system settings.

    How would you go about managing multiple databases then? In Dynamics CRM terminology, each database represents a distinct organization in CRM. Adding new organizations into your CRM server can be easily achieved through the CRM Deployment Manager. If all you need is a blank new test organization, then just start the New Organization wizard and click through the process, which will create a new database, configure language and currency settings etc. Click Finish and you’re all set!

    Oh, you wanted your production system entities, fields and other customizations in there, too? No problem, just go into your production CRM settings area, pick a solution that contains the elements you need and export it. If you don’t have such a solution, then simply create a new one, as it’s not going to affect how your live environment operates in any way. After all, solutions are just pointers to the components like entities, processes or plugins, until you export them, at which time they become physical containers to all those bits that make your CRM environment different from the plain vanilla Dynamics CRM organization. Don’t export your transport solution as managed, unless you have a good reason for it (you’ll know once you do). Just take the unmanaged solution zip file from your current CRM organization, open the new one, import it there and publish all customizations. Now we’re done!

    Huh? What’s that you say about data? Would you prefer to have not just the schema of the database but also the contents of your CRM database in that new test organization of yours? In that case, let’s forget the previous steps and use another approach, shall we? Instead of the New Organization wizard you’ll want to use the Import Organization option. Before you click on it, though, we’re going to need to create a copy of your database, because the CRM Deployment Manager does not have a “Copy Organization” feature.

    Typically the Import Organization functionality is used when taking a database from some other environment, like when establishing a whole separate development box or test server. Also the actual upgrade process for turning a CRM 4.0 database into a CRM 2011 database is handled through the same import wizard. When you’re in the process of planning your Dynamics CRM upgrade, this is a handy way to update the old CRM 4.0 customizations into new CRM 2011 solutions. In these scenarios you’d first take a backup of the original database on your old SQL Server, then copy it over to the new environment, import it into SQL and finally into CRM. However, as we’re simply creating a replica of the database inside the same environment, we don’t need to necessarily go through the backup stage.

    On your SQL Server machine, open up SQL Server Management Studio, right click on the database which has the name ending with “_MSCRM”, then select Tasks – Copy Database. This helpful Copy Database Wizard will step you through the process of creating an exact copy of your CRM organization database. Just give it a different name than the original database and point it to the same server. You don’t even necessarily need to schedule a maintenance break for your production CRM environment, since the wizard can create the copy without the need to detach and attach the original database. This wizard actually builds an SSIS (SQL Server Integration Services) package that takes care of the copy operation.

    Before returning to the CRM Deployment Manager we’ll need to pause for a while and think about how the Dynamics CRM server operates. Just like records in CRM, also the actual organization itself has a unique ID in addition to the name and display name visible in Deployment Manager. Although the Import Organization wizard does attempt to handle this, it doesn’t perform it in the most graceful way. The two known side effects from from having overlapping organization ID’s prior to the import are that: A) the import will fail if you’ve customized the business unit entity and B) email router will not work for the new organizations. There might even be other nasty surprises hiding deep inside the database, so ultimately we’d like to have a situation where the organization ID’s are 100% unique.

    There are no official tools for this operation, but luckily the Microsoft Dynamics community has come up with a solution. In the CRM Forum thread “CRM 2011 Import Organization on the same server (or how do you create a development sandboxes)” you can find a script that you can execute on your new database copy. This script will generate a new ID and update it to all the relevant tables, including the PrincipalObjectAccess table where the OrganizationID goes by the name PrincipalID. If you’re not familiar with working on SQL Server then it may look scary, but the process itself is quite simple. Select your new database (not the old one!), right click, New Query. Copy the script created by Frenkie Smart found in the CRM Forum post and paste it into the query window. Pause for a minute amd check that you have fresh backup copies of anything that’s valuable to you on that SQL Server. Got it? Good, then just click the Execute button to run the script. You’ll see in the message window below the query how many records the script has changed in each table it processes.

     

    Now we’ve got the new database in such a condition that we can proceed to the Import Organization wizard in the CRM Deployment Manager. Select the new database as the one you want to import, give the organization a unique display name and database name, accept the user mappings, and off you go (see detailed process instructions in this Technet article). The Deployment Manager will build a new organization for you from the copy of the existing database, which you can then access by replacing the organization name in your existing CRM URL (in my case from http://server/demo1 to http://server/demo2). If you want to use friendly URL’s or IFD for accessing CRM then you’ll need to know which DNS entries and settings to modify for the new organization to be available.

    That’s it, for real. Here’s a quick recap of the process steps:

    1. Copy your production database
    2. Take backups!
    3. Run the script on the new database
    4. Import the database as a new CRM Organization with a unique name

    The typical scenario for performing this process would be the need to generate several development organizations that contain identical data and customizations as a starting point. If you just want to maintain your own test organization alongside your production CRM environment then there’s a few things you should take into consideration. First of all, the chance for human error. If you have two identical CRM organizations that are separated only by a few characters in the URL, the chances of mixing them up can be high. Second, you won’t be able to test anything related to Update Rollups and other components that are shared by all the organizations on the server. Third, if you’re unsure about what you’re doing, then don’t do it on your live CRM server!

    With all this in mind, it might be a good idea to investigate the possibility of having a separate test server after all, don’t you think? If you don’t have any suitable hardware lying around, then signing up for a virtual machine straight from the cloud is a valid option these days. With its latest improvements, Windows Azure offers a convenient service for provisioning persistent virtual machines as needed. Building a VHD image with CRM 2011 is not a very difficult process if you follow the instructions (and know the few gotchas about SQL or VM size settings). Also, if you don’t need to keep the server up & running on a continuous basis, you can always delete the Azure virtual machine and still keep a copy of the VHD image, available for booting it back up again when the time comes.

  • CRM adventures in Azure: changing the virtual machine size

    CRM adventures in Azure: changing the virtual machine size

    After having managed to overcome my previous challenges of deploying SQL Server 2012 Reporting Services on a virtual machine running in Windows Azure, I was well underway in configuring my Dynamics CRM environment to contain the tools and data needed for developing, testing and even demonstrating custom solutions right in the Microsoft cloud.

    I had started off with a medium size virtual machine that had 2 CPU cores and 3.5 GB memory. While I’ve been able to successfully use CRM 2011 + SQL 2012 on such a setup as a personal development box, I have to say it’s not exactly the fastest thing around. With me being the only person working with the environment currently, it wouldn’t have been such a big issue, but upon trying to import one 5 MB solution file into a CRM organization I started running into timeout issues, leading to the following message:

    It’s not very uncommon to experience timeouts with CRM when working with large solution files. There are various settings that you can modify to overcome this issue, including the OLEDBTimeout, Web.Config parameters etc. However, I wasn’t having success with the solution import regardless of having applied the registry and settings changes, so I thought why not crank it up a bit and give my virtual machine some more resources. After all, isn’t that one of the selling points of on-demand cloud computing? If you need more power, just adjust the lever and consume the resources as you see fit.

    I proceeded with shutting down my virtual machine from Windows and going to the Azure management portal. After finally getting the portal to confirm that the machine was in a stopped state, I changed the virtual machine size from medium to large (4 cores, 7 GB). Great, now let’s fire it up once again by clicking on Restart and… it doesn’t start. Trying it again and still the only result I get is the following notification in hte Azure portal:

    The virtual machine cannot restart. The current virtual machine state is RoleStateUnknown.

    Ok, I’ll wait a while, I thought to myself. After a few minutes and some more clicks on the Restart button I was starting to get a bit anxious on why my server wasn’t booting up. I started googling for the error message and discovered a discussion thread that indicated I wasn’t the only person in the world suffering from this problem. The RoleStateUnknown message appears to be a known issue that the Windows Azure team will be fixing by the time the Preview phase is over, but for the time being, this is something you can expect to happen if you power off a virtual machine in Azure on a bad day. If the error message does not go away, the only workaround you have is to create a new copy of your virtual machine.

    While there are ways to do the process through PowerShell to export & import the virtual machine, I decided to take the GUI route and just click on the Delete button on my virtual machine. I must admit that particular action doesn’t feel quite right, deleting the very server you’re trying to get back up, but in this context it’s actually not as catastrophic or irreversible as it sounds at first. You see, the server really is just a VHD disk that has been assigned the hardware, IP and other pieces that make it operational. It’s also worth noting that this is the way how you can stop incurring costs from your virtual machine. If you just shut down your VM, you will still be charged for it, but if you delete the server, you’ll have an image available that you can later on use for creating a new server.

    After deleting the server, I created a new one with the same configuration. OK, not exactly the same, as both the [servername].cloudapp.net DNS entry and the IP address will change in the process. Also do note that the remote desktop port will be different, so only updating the server name in your RDP settings won’t allow you to connect, as I quickly discovered after clicking on Restart.

    Oh yeah, I had that extra F-drive on my machine, too! Better remember to attach that disk as well, since that’s where my CRM databases are located. I hit a restart on the SQL Server service, but noticed that the databases still weren’t available. Then I remembered what Shan McArthur had accidentally demonstrated in his Windows Azure 2012 Spring Wave webinar session on XRMvirtual earlier this week. Although the D-drive on an Azure virtual machine is great for storing temporary data that doesn’t need to be consuming that precious C-drive, the fact that the D-drive is only a temporary storage means also that any directory which you create on that disk will not be available once you spin up a new virtual machine from the same VHD. A quick peek into the Windows application log confirmed that this was what was keeping my SQL Server from starting up, as it wasn’t able to locate or create Temp DB and log it needed.

    “FCB::Open failed: Could not open file D:\SQL\Microsoft SQL Server\MSSQL11.MSSQLSERVER\MSSQL\Data\tempdb.mdf for file number 1.  OS error: 3(The system cannot find the path specified.).” There we go, that was the path that was missing from my D-drive. In a default configuration the temporary database would have been under C:\Program Files, but I had put it on D:\SQL instead, so I needed to manually go and create the folder. After this my virtual machine was again able to run CRM the way that is was meant to be. I’m sure there’s a PowerShell script sample out there somewhere for those who wish to automate the directory existence verification and creation upon restart of their servers, but this shouldn’t be a too frequent problem unless you go deleting your Azure virtual machines on a regular basis, so I didn’t bother looking up one right now. The main thing for me was I had my CRM test server running now on double the capacity.

    As a side note, once I opened up Excel, I was greeted by this Microsoft Office Activation Wizard. I guess that proves that it’s now really a whole different machine I’m working on, even though I booted up from the same VHD that I had already activated on the previous day. Hardware based license management feels a bit funny when operating in such an intangible environment as Azure, but that’s how it is…

    Finally, let’s get back to the topic mentioned in the title of this blog post: What is the right way to change the size of your Windows Azure virtual machine? It turns out that you can actually do this right from the Azure management portal without shutting down your server. That’s what it says on the Azure community pages:

    NOTE: If you are attempting to just change the size of your Virtual Machine, you can do this without stopping the Virtual Machine. You can go into the “Configure” tab on the virtual machine in the management portal and select the Virtual Machine size. This will change the size without first stopping, which will allow you to avoid this issue in this scenario.

    Will be interesting to see how the Windows server will cope with disappearing CPU cores and memory if I decide to go back from Large to Medium, but I’ll leave that experiment to the next time. Now let’s see if I could get that solution file imported first…

  • CRM adventures in Azure: SQL Server 2012 updates

    CRM adventures in Azure: SQL Server 2012 updates

    Windows Azure Pricing Calculator for Virtual MachinesAfter Microsoft announced their latest enhancements to the Windows Azure service catalog in the beginning of June (although still in a preview phase), I wanted to test the Azure Virtual Machines for running a Microsoft Dynamics CRM 2011 server instance in the Microsoft hosted cloud. Since we now have an excellent step-by-step tutorial available from Shan McArthur / AdxStudio, it was a breeze to get going with the installation process. You should also check out the Gold Coast blog by @devkeydet for some really helpful articles, such as Building a CRM 2011 dev box using a Windows Azure Virtual Machine. With high quality community content like this available, who wouldn’t want to build a CRM server in Azure?

    Hopefully I’ll get a chance to write some of my own experiences on working with Windows Azure in the future, but the first issue I ran into is actually more related to SQL Server in general.

    Side-by-side was not on my side

    In addition to Azure, I also wanted to utilize the latest SQL Server 2012 version to investigate what new reporting capaibilities it might offer compared to the trusted old SQL 2008 R2. The SQL 2012 installation went through without any errors but after I tried to launch the CRM installation I discovered that Reporting Services was not running. In the application log I had the following errors:

    Service cannot be started. System.IO.FileLoadException: Could not load file or assembly ‘ReportingServicesNativeServer, Version=11.0.0.0, Culture=neutral, PublicKeyToken=89845dcd8080cc91’ or one of its dependencies. The application has failed to start because its side-by-side configuration is incorrect. Please see the application event log or use the command-line sxstrace.exe tool for more detail. (Exception from HRESULT: 0x800736B1)
    File name: ‘ReportingServicesNativeServer, Version=11.0.0.0, Culture=neutral, PublicKeyToken=89845dcd8080cc91’ —> System.Runtime.InteropServices.COMException (0x800736B1): The application has failed to start because its side-by-side configuration is incorrect. Please see the application event log or use the command-line sxstrace.exe tool for more detail. (Exception from HRESULT: 0x800736B1)
    at Microsoft.ReportingServices.Library.ServiceAppDomainController..ctor()
    at Microsoft.ReportingServices.Library.ReportService.OnStart(String[] args)
    at System.ServiceProcess.ServiceBase.ServiceQueuedM…

    Activation context generation failed for “F:\SQL\Microsoft SQL Server\MSRS11.MSSQLSERVER\Reporting Services\ReportServer\bin\ReportingServicesNativeServer.dll”.Error in manifest or policy file “C:\Windows\WinSxS\manifests\amd64_microsoft.vc80.atl_1fc8b3b9a1e18e3b_8.0.50727.4053_none_8a1a02152edb659b.manifest” on line 0. Invalid Xml syntax.

    After a bit of googling I landed on a blog post on SQL Server VC++ Installation voes, which seemed to describe the exact same issue I was facing. I tested the sxstrace tool and the System File Checker (SFC) referenced in the post, reproducing the same results as the author was getting. It turned out that this side-by-side (SXS) error was caused by a manifest file that was empty. A similar discussion was also found in the Windows Azure Virtual Machines for SQL Server MSDN forum, so figured I must be on to something.

    Just before I was about to start modifying the manifest file properties and copy pasting the content there, I noticed that there was one detail which stopped me from proceeding: both of the posts were referencing an x86 processor architecture file (x86_microsoft.vc80.atl_1fc8b3b9a1e18e3b_8.0.50727.4053_none_d1c738ec43578ea1.manifest), whereas my error was related to the amd64 version (amd64_microsoft.vc80.atl_1fc8b3b9a1e18e3b_8.0.50727.4053_none_8a1a02152edb659b.manifest). So, I needed to hunt down the correct manifest file content for the 64-bit version with the proper hash value, but unfortunately this was where my googling came up short.

    After being a bit more creative with my search terms, I discovered the Microsoft knowledge base article 2688946: FIX: Error message when you install SQL Server 2012: “The identities of the manifests are identical but their contents are different”. The article also said that the fix for this issue was released in Cumulative Update 1 for SQL Server 2012. So, off to KB 2679368 then to request a download link for the CU1 file. Looks like MS doesn’t want just anyone installing these, so they make you fill in a form with your email address + captcha in order to get the file, but luckily that’s all there was to the process.

    I was a bit puzzled by the fact that the CU1 installer said “This installation provides updates for the Community Technology Preview (August CTP)“, but apparently it’s fully valid for the RTM version, too. I guess the components in SQL tend to be less strict about versions, since the SQL Server 2012 Reporting Services Configuration Tool still has “SQL Server 2008” in the process description… Anyway, after applying CU1 I was able to start the Reporting Services service and proceed with the CRM installation. Success!

    The lesson: there’s always an update hiding somewhere

    When building your development or demo environment, it’s important to be aware of the fact that what you get from the MSDN subscriber downloads site is mostly RTM bits. Especially when going for SQL Sever 2012 as the database for Dynamics CRM 2011, you should not use these files at all, because the installation won’t work. Just grab the product key from MSDN, but go to Microsoft Download Center here (direct download link to CRM 2011 Server) to get the new version that includes Update Rollup 6. Otherwise you won’t be able to configure the Reporting Extensions and will get the error “Unable to validate SQL Server Reporting Services Report Server installation. Please check that it is correctly installed on the local machine.” See this thread on the CRM Forum for some more info.

    Shortly after completing the CRM installation I discovered that there is in fact already a Cumulative Update 2 for SQL Server 2012, too. Wow, things move fast nowadays! Didn’t SQL 2012 get released just recently? Well, it turns out that actually the first cumulative update to SQL Server 2012 was released only 9 days after the general availability (GA) of SQL 2012. Let’s have a look at the Incremental Servicing Model of SQL Server in a bit more detail to understand what’s going on.

    It looks like also the SQL Server team have adopted a fairly agile release policy that mirrors the Dynamics CRM release roadmap concepts to some extent. What they call a Cumulative Update (CU) seems to correspond to the Update Rollup (UR) in the Dynamics CRM lingo, meaning they are both collections of hotfixes, released on a bi-monthly target schedule. However, unlike CRM Update Rollups that are nowadays delivered through Windows Update / Microsoft Update, the SQL CU’s won’t be delivered through this mechanism, because those updates need to fall into the General Distribution Release (GDR) category as defined by Microsoft (you didn’t think it was going to be that simple, now did you?). So, that’s why after installing SQL 2012 + CRM 2011 on a new Windows server you’ll automatically get Update Rollup 8 for CRM, but no updates for SQL. If you need the latest hotfixes, you should acquired the Cumulative Update package through the KB pages.

    Finally, yes, there will also be a Service Pack 1 for SQL Server 2012 released eventually, as many people will refrain from deploying a production database on a new version until this traditional MS software milestone has been reached. Whether the SP1 for SQL 2012 will be a similar release as the Q2 2012 Service Update for Dynamics CRM (to be released sometime in Q3) I’m not so sure, since in the world of CRM these Service Updates contain significant new functionality in addition to the previously released hotfixes. The latest information on SQL updates can be found from the SQL Release Serivces Blog. Note that there are no Service Packs available for Dynamics CRM, so the terminology used in describing the release policies will likely remain different from other Microsoft products like SQL or Windows. For a more detailed description of the CRM update terminology and delivery mechanisms, please see my previous post on the topic.

  • Dynamics CRM update & version madness explained

    Things used to be simple back in 2005 when I started working with Microsoft Dynamics CRM (known as only Microsoft CRM back then). You pretty much knew that there would be a version update every 2-3 years. In between, there would be a number of hotfixes created to address software bugs, which were packaged as “Update Rollups”. CRM 3.0 received in total three such packages. CRM 4.0 things kicked up a notch as we started getting these Update Rollups (UR) based on a regular schedule, one update every 2 months (until they were put on hold after UR21). CRM 2011 adopted the same UR release schedule, but in addition to that, Microsoft announced that they would adopt a new agile release model.

    Exactly one year after the announcement we find ourselves in situation that may seem quite confusing to the casual observer. There are new releases in the horizon on a continuous basis and it’s easy to lose track of what exactly is meant by which update. Well, here’s my shot at explaining what’s going on.

    What’s the “R” in R8?

    R8 means Release 8. OK, so what are the previous seven versions then? They don’t refer to CRM 1.0, 1.2, 3.0, 4.0 or 2011 (there never was a 2.0, as some of you will remember). This R-based naming policy comes from the CRM Online product, which used to have its own release schedule with no UR’s and a slightly different code base from the on-premises product. The first R was presumably released when CRM Online was announced in April 2008. Things changed when Microsoft Dynamics CRM 2011 was released on January 15th, 2011, after which both the Online and on-premises version have pretty much gone hand in hand.

    The “Rx” naming signifies a new functionality release and should never be used to refer to Update Rollup versions. Don’t shorten UR8 to R8, because the two are not the same (even though they are intertwined as we’ll later see). The Update Rollup version numbers always start from 1, so there’s been an Update Rollup 1 for both Dynamics CRM 2011, CRM 4.0 as well as CRM 3.0. Makes googling for information all the more merrier, eh? The Rx releases are a product of the cloud era and until we reach the next era beyond cloud apps, I don’t expect Microsoft to start the numbers from R1 all over again.

    What’s the Q2 2012 Service Update?

    Since it would have been confusing for on-prem customers to first release Microsoft Dynamics CRM 2011, then follow it up with a “Release 7” all of a sudden, Microsoft decided to come up with a different naming policy for these new, agile releases. Unfortunately, the naming policy they chose gave us monsters like “Microsoft Dynamics CRM 2011 Q4 2011 Service Update”. Wow, how catchy, and only 50 characters long! As a result of this, what was originally supposed to be just internal jargon became the preferred option for human communication and we learned to know the update as R7. 50 vs. 2 chars, which one would you expect to fly in the age of Twitter? ‘Nuff said.

    Most of you must surely be already familiar with the contents of R8, the next release, and if you’re not, here’s a link to the Release Preview Guide. The real catch is in how the promised new features will actually be delivered. The correct answers to this question are:

    • In multiple Update Rollup versions
    • As solution files
    • Through Microsoft hosted, subscription based services or 3rd party licensing
    • In other Microsoft products

    A, B, C, D – tick ’em all. This is the reason why there will not be any single place where you can “download R8”.

    What will the Update Rollup 9 contain?

    The official release announcement of Q2 2012 Service Update will most likely coincide with Update Rollup 9. However, if you study the contents of the Guide, you’ll find features that have already been released before UR9. Rapid View Forms were introduced in March as part of UR7 and re-labelled as Read-Optimized Forms. The same is true for SQL Server 2012 compatibility.

    As we know from the previous R7 release, Activity Feeds are delivered as a solution package that you will need to download from the Dynamics Marketplace and configure manually. The Activity Feeds solution is not a part of the “core” Dynamics CRM product. However, there are parts of it that have been built into the platform, which is why at least Update Rollup 5 is required in order to install the solution. Similarly, you will need to get an updated version of the Activity Feeds solution after installing Update Rollup 9, otherwise you won’t see the new features.

    A key deliverable of Update Rollup 9 will be the cross-browser compatibility that makes Dynamics CRM available on Chrome, Firefox and Safari. Since this is such a significant change to the platform, Microsoft has been kind enough to grant access to the R8 beta release for anyone who’s willing to follow these steps. Please note that the beta bits available for download cannot be upgraded to the final release of R8, so use them in a test environment only.

    Extended browser support is completely separate from the mobile device support promised in R8. While you will be able to run CRM on an iPad Safari browser, the mobile UI demoed by Microsoft in numerous occasions is only available as 1) a subscription service from Microsoft, if you’re running Online or IFD, or 2) a perpetual license to be purchased from CWR Mobility if you intend to host the required service on your own server. Trying to access your Dynamics CRM environment after UR9 installation with an iPhone will most likely just give you the simplified Mobile Express user interface already familiar from the CRM 4.0 days.

    Oh, and do note that the Microsoft Dynamics CRM Mobile released for Windows Phone 7 back in Q4 2011 Service Update is a completely separate product, even though it carries exactly the same name as the new offering developed by CWR Mobility and later on re-labelled by Microsoft. Since CWR Mobility didn’t have a Windows Phone client, this mobile app will likely remain on a separate development roadmap from the CWR-based offering.

    What’s the role of SQL Server 2012 in all this?

    If you haven’t seen the cool drag & drop report designer or the animated bubble chart demo of Power View, take a look at this video:

    Wow, wouldn’t that look nice on a Dynamics CRM dashboard! Well, even though Power View is listed as one of the R8 features, there’s a couple things you should know:

    • It’s not available in the cloud. Sorry, CRM Online users. Maybe in R9 then?
    • CRM dashboards are a different data visualization technique that will remain “as is” for R8, not integrated with Power View.
    The pre-configured Power View templates promised in R8 will be delivered as a Dynamics Labs solution, probably as a download from the Dynamics Marketplace. The same goes for the PowerPivot models to be included. Note that if you want to publish these PowerPivots to your organization, you’ll need an on-premises SharePoint Server 2010 with Enterprise CAL’s to view their contents on a browser window in your intranet.

    In addition, if you plan to use SQL Server 2012 for hosting your Dynamics CRM database and Reporting Services, it’s good to know that you can’t achieve this configuration with the RTM bits for Dynamics CRM 2011. Update Rollup 6 has established a new baseline for CRM and you’ll need to download the server installation files from Microsoft Download Center, otherwise you’ll run into the following error while trying to install the Reporting Extensions:

    Unable to validate SQL Server Reporting Services Report Server installation. Please check that it is correctly installed on the local machine.

    What time is Metro?

    Putting things into perspective, Dynamics CRM is a small fish in the tank compared to the big whale that is Windows 8. Since R8 will be out well before Windows 8, there isn’t yet a 100% support provided for the Internet Explorer 10 browser provided with it. Hopefully the remaining issues will be addressed by upcoming UR’s as Windows 8 is nevertheless just around the corner. Presumably we’ll see a proper Metro app for Dynamics CRM once R9 is released later this year. After all, the Windows RT tablets will not be able to run Outlook and we’ll need some solution for activity tracking to and from CRM.

    When it comes to Windows Server 8, official support has not been announced. However, that doesn’t mean you couldn’t install Dynamics CRM 2011 Server on a Windows 8. Daniel Cai has done just that and offers a tutorial for anyone wanting to test R8 on the very latest Microsoft products available.

  • Storified: Microsoft Dynamics CRM R8 and beyond