Migrating Drupal from MySQL to MSSQL

So I am going to start by ranting a little bit about Microsoft Azure cloud services.  Something odd about Azure is that when you are creating a new Drupal or WordPress site it will automatically crate a MySQL database but if there is already a MySQL database it will not create it automatically.  You can however create a MySQL database then install your CMS and associate it with said database.  The second part of my rant is that with a MSSQL database the smallest size is 2 GB and it is less expensive than the 20 MB MySQL database, which just seems odd to me.


Okay, now that I have that over with, let me get to the heart of what I wanted to talk about and discuss installing Drupal on a MSSQL and migrating (if need be) from MySQL in Azure.  So, to start there is one very easy config change to allow Drupal to run on MSSQL.  Download the drivers from https://www.drupal.org/project/sqlsrv then extract the files.  With the files extracted copy the sqlsrv folder and drop it in the includes/databases directory in the root of your Drupal installation.  After the files are uploaded update the configuration settings in the settings.php file and update the connection for the $databases to have the driver “'driver' => 'sqlsrv'”.  Something to note, if you are performing this on Azure the PHP MSQL Drivers are already installed but if you are running this install on a different system you may need to install PHP MSSQL drivers.


One thing that was missing on my installation was a SUBSTRING function that is used for Drupal when performing a post.  The site worked fine but when I tried to post as an authenticated user I received an error.  To solve the problem I needed to add the function.  Click Here for the specific TSQL code.  (UPDATE)Even with the substring SQL function installed I was still having trouble.  So I ended up hitting the install.php page and then running a database update.  This seems to have fixed my missing SQL functions, but only time will tell if I am not missing anymore.  I have included all of the functions that are created in the link referenced in this paragaraph.


So if you need to migrate from MySQL to MSSQL there are a few steps that you need to take.  After creating the new database in MSSQL download the MSSQL Migration Assistant for MySQL from http://www.microsoft.com/en-us/download/details.aspx?id=42657.  Point the MySQL to your old MySQL instance and point the MSSQL to the new MSSQL instance.  Start by changing the target schema to dbo (this will make the migration easy).  Then right click on your database and click “Convert Schema” this will create the local metadata of the schema.  Once the schema conversion is done, on the target database right click and select “Synchronize with Database.”  Once the synchronization is complete (this will take a while) your tables will be created but without data.  Lastly right click on your original MySQL database and select “Migrate Data.” 


If you have not updated your connection in your settings.php file, you can update that now to point to the MSSQL instance and you should be good.  I tested my change by making a small change to my site and seeing how it reflected in the database (specifically I changed my locale on my user account). 


I hope this will help the few that run Azure sites under your MSDN and do not want to pay for additional MySQL storage.

SharePoint Login as a Different User



  2. <Elements xmlns="http://schemas.microsoft.com/sharepoint/">
  3. <CustomAction
  4. Id="LoginScript"
  6. Location="ScriptLink">
  7. </CustomAction>
  8. <CustomAction
  9. Id="LogInAsUser"
  10. GroupId="PersonalActions"
  11. Location="Microsoft.SharePoint.StandardMenu"
  12. Sequence="998"
  13. Title="Sign in as a Different User"
  16. </CustomAction>
  17. </Elements>


  1. LogoffAndLogin(){
  3. var siteCollUrl = '';
  5. var clientContext = new SP.ClientContext.get_current();
  6. var site = clientContext.get_site();
  7. clientContext.load(site);
  8. clientContext.executeQueryAsync(Function.createDelegate(this, function(){
  9. siteCollUrl = site.get_url();
  11. window.location = fullURL;
  12. }))
  13. })
  14. }
  15. else {
  17. }
  18. };


SharePoint security trimmed site list






    1. using Microsoft.SharePoint;
    2. using System;
    3. using System.Collections.Generic;
    4. using System.Linq;
    5. using System.Text;
    6. using System.Threading.Tasks;
    8. namespace SPSiteListing.ListSPSites
    9. {
    10. class SiteLookup
    11. {
    12. private Boolean _EnableTrimming;
    13. private SPContext _Context;
    14. private string _CurrentUserName;
    15. public SiteLookup(SPContext context, Boolean enablePermissionTrimming)
    16. {
    17. _EnableTrimming = enablePermissionTrimming;
    18. _Context = context;
    19. _CurrentUserName = context.Web.CurrentUser.LoginName;
    20. }
    21. public List<SPSiteInfo> GetSites()
    22. {
    23. if (IsRootInApplication())
    24. {
    26. list.AddRange(GetSitesUnderCurrentWeb());
    27. list.AddRange(GetSitesUnderManagedPath());
    28. return list;
    29. }
    30. else
    31. return GetSitesUnderCurrentWeb();
    32. }
    33. public Boolean IsRootInApplication()
    34. {
    35. if (_Context.Site.RootWeb.Url != _Context.Site.Url)
    36. return false;
    38. return false;
    39. return true;
    40. }
    41. public List<SPSiteInfo> GetSitesUnderManagedPath()
    42. {
    44. var applicationSites = _Context.Site.WebApplication.Sites;
    45. foreach (SPSite item in applicationSites)
    46. {
    48. item.CatchAccessDeniedException = false;
    49. try
    50. {
    52. sites.Add(new SPSiteInfo(item));
    53. }
    54. catch (UnauthorizedAccessException)
    55. {
    59. }
    60. }
    61. return sites;
    62. }
    63. public List<SPSiteInfo> GetSitesUnderCurrentWeb()
    64. {
    66. if (_EnableTrimming)
    67. {
    68. foreach (SPWeb item in _Context.Web.GetSubwebsForCurrentUser())
    69. {
    70. sites.Add(new SPSiteInfo(item));
    71. }
    72. }
    73. else
    74. {
    75. foreach (SPWeb item in _Context.Site.AllWebs)
    76. {
    77. sites.Add(new SPSiteInfo(item));
    78. }
    79. }
    80. return sites;
    81. }
    82. }
    83. }


    1. using Microsoft.SharePoint;
    2. using System;
    3. using System.Collections.Generic;
    4. using System.Linq;
    5. using System.Text;
    6. using System.Threading.Tasks;
    8. namespace SPSiteListing.ListSPSites
    9. {
    10. class SPSiteInfo
    11. {
    12. public string SiteName { get; private set; }
    13. public string SiteUrl { get; private set; }
    14. public string HTMLLink
    15. {
    16. get
    17. {
    19. SiteUrl,
    20. String.IsNullOrEmpty(SiteName) ? SiteUrl : SiteName);
    21. }
    22. }
    23. public SPSiteInfo(SPSite site)
    24. {
    25. try
    26. {
    27. SiteName = site.RootWeb.Title;
    28. }
    29. catch (UnauthorizedAccessException)
    30. {
    33. }
    34. SiteUrl = site.Url;
    35. }
    36. public SPSiteInfo(SPWeb site)
    37. {
    38. SiteName = site.Name;
    39. SiteUrl = site.Url;
    40. }
    41. }
    42. }

    ASCX file

    3. </ul>

    ASCX code behind

    1. protected void Page_Load(object sender, EventArgs e)
    2. {
    4. foreach (var item in query.GetSites())
    5. {
    7. li.InnerHtml = item.HTMLLink;
    8. siteList.Controls.Add(li);
    9. }
    10. }


Good Luck !!

The culture paradigm shift

Almost everyone who knows me almost immediately knows my political and religious views and even like-minded people refer to me as a zealot.  With that disclosure I am going to place in writing what I have been saying for a long time, and that is my belief that there is currently a paradigm shift going on throughout the United States and slowly we as a people are shifting back to the ideals of years past.

At the beginning of the 1960’s people wanted to throw away their straight-laced image and embrace the counter-culture that began to develop.  This counter-culture embraced drugs, liberal ideals, and “free love” resulting in a society that had a great deal of moral decay.  The detestable act of abortion became legal and communist philosophies started to be embraced by a large portion of the United States.

I know that I am generalizing quite a bit but my point is not the specifics it is what I believe is coming as a whole.  While the counter-culture in the 1960’s embraced moral decay, the counter-culture of today is embracing God and conservative ideals.  Examples of this can be demonstrated with the wave of religious movies that have been released in recent years as well as the strength of the Tea Party movement.  Other areas demonstrating this shift have been with the support of companies that are demonstrating their faith such as Hobby Lobby and Chick-Fil-A.  I have been saying for a long time that there is a shift coming and I think that it is becoming apparent.

I think of the United States’ culture as a pendulum, it never stays in the middle it always swings from one side to the other.  At its worst we have big government with things like government-controlled healthcare where at its best we have winning the cold war.  So here is my prediction, over the next few years the Tea Party will become a large influence in daily politics and the Christians will stand united to show their faith and be lights unto the world. 

So I say support your Hobby Lobby and Chick-Fil-A.  Be the salt of the earth and tell your story of Christ in your life.