NoSQL versus SQL

Already this post has started out like many other posts comparing NoSQL to SQL, pitted against each other.  So let’s change that.  The new title of this post is: NoSQL and SQL!

What is NoSQL?

There are two types of NoSQL:

  1. The original NoSQL, which has been around for about two decades and is a relational database, does not use the Structured Query Language (SQL).
  2. The recycled NoSQL, which comes in a variety of flavors, vendors, and either uses SQL, does not use SQL, can use SQL but does not prefer SQL, is relational or isn’t rational, and so on.  It’s new technologies borrowing an already taken moniker.  A better name might be just calling each technology what it is instead of trying to lump them together, i.e. MongoDB, Cassandra, Riak, etc.

They are two different types of tools and one or the other or both can be used in the same environment to perform different tasks.  Let’s look at storing data to highlight a major difference.

Data Design in SQL and NoSQL

A SQL database, such as MySQL or MS SQL, traditionally stores information in tables, rows, and columns you determine during the design phase.  You end up with small, “virtual spreadsheets” that are inter-related to each other through unique identifiers.  The data going into the spreadsheet must fit the constraints of the design of the column the data is going into.  A person’s last name can’t be inserted into an Integer-type field unless that person’s last name happens to be a number.  This is great from a data mining perspective where consistent and precise information is needed.  Your bank’s software will run off of this, and probably Oracle or Microsoft.

Data stored for a person in this type of database usually looks something like:

ID first_name last_name
1 Bill Murray
2 Dan Akroyd

In NoSQL, there normally isn’t this “virtual spreadsheet” to which your data needs to fit into.  Instead, data is generally manipulated and stored in a standardized format, such as JSON or XML.  This is great if your data requirements are changing frequently and/or you want the data to be easily usable by a broad range of applications.  Your social media accounts such as Facebook uses HBase and compliments it with Presto to perform data analytical queries.

In a document-based NoSQL database, data might be stored as:

{
id: 1,
fname: "Bill",
lname, "Murray"
}

But what if the data stored needs to change, for example, adding the middle initial?

In SQL, you would have to create a new column on the table and update any stored procedures, triggers, web application executions, etc. before you could start using the new table.

In NoSQL, you simply change your JSON to:

{
id: 1,
fname: "Bill",
mi: "A"
lname, "Murray"
}

That seems too easy.  Why isn’t everyone doing this?  Many companies are doing this and it really isn’t that easy.  Before you use NoSQL with multiple developers, there still needs to be a defined schema or data structure that needs to be strictly enforced.  There is nothing worse than lost data because it was overwritten by newer and incorrect data.

At the end of the day, developers have been storing JSON and XML data in traditional databases for as long as JSON and XML have existed as a way to get around making changes to the databases’ design and improving data portability.  It was only a matter of time before that method was extracted into a system of its own.

When to Use SQL and When to Use NoSQL

Like any other technology, gather your requirements, use cases, and approach the project with a platform-agnostic mindset.  Even if I dislike Company A, if their Product A is fits the requirements better than Product B, I’m going with Company A.

If you are supporting a high-performance, high-availability web application, consider NoSQL technologies.

If you are building financial transaction software that requires rigid data consistency, reporting, and auditing, consider a solution like Oracle or Microsoft.

If you are considering building a financial transaction software with a huge web-based user population, consider a SQL back-end with a NoSQL front-end, which is what Chase, Bank of America, and other global entities have done with MongoDB for their customer relationship management (CRM) aspect of their companies.

Why Can’t I Use NoSQL for Financial Data

You can.  I’m not telling you how to design your solution, by any means.  However, There are very few NoSQL solutions that would/can pass an ACID test.  Even tech giants like Facebook and Twitter still use a SQL database to store vital data and provide reporting.  Companies like Chase and Bank of America, still use SQL farms for the critical data.  That shouldn’t deter you from designing your solution.

Quick ACID refresher:

A – Atomicity – Either perform the transaction or don’t, no middle ground.
C – Consistency – Only valid data be written and committed.
I – Isolation – Transactions occurring at the same time are performed independent of each other.
D – Durability – Ability to recover working data from a failure or disaster.

Without these in place, applications built using NoSQL would likely fail State and Federal audits.

Want to Learn More about NoSQL?

There are many different flavors of NoSQL but only four (4) main types of NoSQL databases.

  1. Columnar – Just as it sounds, reads/writes by column instead of row.
  2. Document – This is the JSON example I used above, and most frequently used for web applications.
  3. Graph – Uses edges, nodes, and properties to directly store the relationship between records.
  4. In-Memory Key-Value Stores – Retrieves commonly requested data from memory based on a key that correlates to that data.

I’ve provided links above where you can test them for free on Amazon’s Web Services.  Amazon also provides free training.  You can also download community versions of popular NoSQL software and install them in VMware or VirtualBox on your own device.

Quick Summary

SQL and NoSQL are non-competing technologies useful for different purposes.  You may have a need for one, the other, or both.  NoSQL may improve the performance, scalability, or availability of your application.  Test your options, know your requirements, and choose the best tool for the task.

Downloads

These or any other links on this page are provided as a courtesy and are not intended as an endorsement.  There are many flavors of SQL and NoSQL.

AWS Command Line Interface for DynamoDB

Apache Cassandra

Apache HBase

Microsoft SQL Server 2016

Microsoft SQL Server 2016 on Linux

MongoDB

MySQL

Oracle

Exchange: Find and Remove Emails

Scenario

For whatever reason, management or enterprise security comes to you and says they need an email removed from all users who received it.  No one knows who all received it.  Luckily, your organization has an Exchange server that makes this easy to do and in a short time, depending on how specific your query below is.

Get-Mailbox and Search-Mailbox with -DeleteContent

You will use two commands to get a list of mailboxes and search the subject, body, or several other attributes for the string that uniquely identifies the email.  You can use * as a wildcard before and after the text as seen in the example below.  When using the wildcard, be cautious to be very specific and avoid strings that would cast too big of a net.  For example, I would probably never use -SearchQuery ‘Body:”*a*”‘ as that would delete every email that contained the letter “a” in the body.  If you believe the email has been forwarded or replied to and you are searching by Subject, adding the * wildcard before the string you are searching for in the Subject will help find emails even with multiple FW or RE strings prefixed.

Login to your mail server.  Open a PowerShell session with administrator privileges.

Add-PSSnapin -Name Microsoft.Exchange.Management.PowerShell.E2010

Get-Mailbox -ResultSize Unlimited | Search-Mailbox -SearchQuery ‘Subject:”*Text that needs to be removed*”‘ -DeleteContent | Where-Object {$_.ResultItemsCount} > filename_to_save_results_into.txt

For a list of the attributes you can search, you can reference “Keyword Query Language (KQL) syntax reference“.

For example, just change -SearchQuery ‘Subject to -SearchQuery ‘Body.

Side Note

You can add this script to a .ps1 file and just edit it as you need it.

I write the results to a file to documented what was changed and who was impacted. If you don’t want to do this, simple leave off the “> filename_to_save_results_into.txt”.

If an email to be removed is open at the time this script is executed, it will disappear once closed. The open email is not a persistent object.

References

Microsoft; “Keyword Query Language (KQL) syntax reference”; https://msdn.microsoft.com/library/ee558911(v=office.15).aspx

Microsoft; “Search-Mailbox”; https://technet.microsoft.com/en-us/library/dd298173%28v=exchg.150%29.aspx

OpenType Font Driver Vulnerability – CVE-2015-2426

Microsoft has identified a vulnerability which impacts all versions of Microsoft Windows which could allow remote code execution if a user opens a specially crafted document or visits an untrusted webpage that contains embedded OpenType fonts.

For Home Users and Organizations without Dedicated IT Staff

If you do not have automatic updating enabled, go to Windows Update, download and install updates.  If you have automatic updates enabled, no action is necessary.  It’s that easy.

For IT Professionals

This small update requires a reboot so please plan accordingly.

Executive Summary

This security update resolves a vulnerability in Microsoft Windows. The vulnerability could allow remote code execution if a user opens a specially crafted document or visits an untrusted webpage that contains embedded OpenType fonts.This security update is rated Critical for all supported releases of Microsoft Windows. For more information, see the Affected Software section.
The security update addresses the vulnerability by correcting how the Windows Adobe Type Manager Library handles OpenType fonts. For more information about the vulnerability, see the Vulnerability Information section. For more information about this update, see Microsoft Knowledge Base Article 3079904.Suggested actions. The majority of customers have automatic updating enabled and will not need to take any action because the update will be downloaded and installed automatically. Customers who have not enabled automatic updating, or who install updates manually, can use the links in the Affected Software section to download and install the update. See Microsoft Knowledge Base Article 3079904 for more information.

For more information, users can visit https://technet.microsoft.com/library/security/MS15-078 to find other methods of preventing this issue.

Lync 2013 to Skype for Business 2015 Enterprise Edition Upgrade

The following are my notes from recently upgrading Lync 2013 Enterprise Edition to Skype for Business 2015 Enterprise Edition.  Our production environment consisted of all Microsoft Server 2012 R2 virtual servers running in a VMware environment.  Our database back-end servers are Microsoft SQL Server 2012.

Pre-requisites

Ensure all servers are fully patched up.  I ran updates July 16th, 2015 after Patch Tuesday and the servers running IIS 8+ were still needing the hotfix outline here (https://support.microsoft.com/en-us/kb/2982006).

Edge Servers

Install Microsoft .Net Framework 3.5 if you have not already.  You may also check for updates after you install .Net 3.5 as there will be updates available.  However, these updates will not prevent the successful installation of Skype for Business 2015.

Front-end Servers, Persistent Chat, File Stores, Database Back-ends

No additional installs.

  1. Connect to computer in the topology that does not have Lync OCSCore or any other Lync components installed. I used a database back-end.  If you try this from a computer in the topology, you will get this error message:

You cannot perform this upgrade until you have used Skype for Business Server 2015, Topology Builder to upgrade the Lync Server 2013 Pools and then publish the upgraded topology. If you have already published an updated topology those changes might not have replicated to this computer. You can force an immediate replication by running the Invoke-CsManagementStoreReplication cmdlet. You can also use the Export-CsConfiguration and Import-CsConfiguration -LocalStore cmdlets to copy the updated topology to this computer. For more information, see the appropriate cmdlet help topic.

You cannot perform this upgrade until you have used Skype for Business Server 2015, Topology Builder to upgrade the Lync Server 2013 Pools and then publish the upgraded topology. If you have already published an updated topology those changes might not have replicated to this computer. You can force an immediate replication by running the Invoke-CsManagementStoreReplication cmdlet. You can also use the Export-CsConfiguration and Import-CsConfiguration -LocalStore cmdlets to copy the updated topology to this computer. For more information, see the appropriate cmdlet help topic.

 

Skype Servers Running IIS 8+ Require a Hotfix

To correct this, go to https://support.microsoft.com/en-us/kb/2982006 and download the hotfix.  Depending on when you attempt this, it may already be available via Windows Update.  I recommend saving this file on your file store for easy access across any of your Lync servers running IIS.

On Step 4, to stop all services Microsoft recommends using, Disable-CsComputer –Scorch.  Make sure you are running the Lync Server Management Shell as an administrator or it will fail.  Disregard the message about running this directly and using bootstrapper –Scorch instead.

Edge Servers

When starting the install on your edge servers, you will get an error prompting you to install Microsoft .Net Framework 3.5.

 

When starting the install on your edge servers, you will get an error prompting you to install Microsoft .Net Framework 3.5.

Skype Edge Pool Servers Require Microsoft .Net Framework 3.5

When trying to install this, you may get an error message about winrm.  When running winrm qc from an elevated powershell, you get a message about your edge server not being resolvable.  I received this because I enabled the internal adapter to grab the IIS hotfix and a new DNS record with a DHCP IP address was created.  The quick fix is to enable the network adapters as they should be normally and restart.  This clears any issues with your edge server IP address being different from what is in your host file or in your DNS records.  Otherwise, you can clear out any DNS records you have and make sure any artifacts are cleared.

Front-end Error

This error was received on one of the front-ends and on none of the other machine types, i.e. edge pool, persistent chat pool, or Lync file store.

Error encountered: Error returned while installing Server.msi(RegistrarStoreUpdate, Feature_Server, Feature_Server_Update), code 1638. Please consult log at C:\Users\\AppData\Local\Temp\Remove-Server.msi-RegistrarStoreUpdate,Feature_Server,Feature_Server_Update-[2015_07_18][15_28_15].log

 

Error encountered: Error returned while installing Server.msi(RegistrarStoreUpdate, Feature_Server, Feature_Server_Update), code 1638.

Error encountered: Error returned while installing Server.msi(RegistrarStoreUpdate, Feature_Server, Feature_Server_Update), code 1638.

This is trying to remove the server roles but is failing for an unknown reason, perhaps because the server no longer knows what roles it should have.

Trying Disable-CsComputer –Scorch again in case something was missed provides the same results as before with no errors.

In the powershell when you run Disable-CsComputer –Scorch, it tells you to instead do this as bootstrapper.exe –Scorch.  If you try this, you may get a message that bootstrapper has no idea what –Scorch is.  Don’t worry about this.

Checked Lync services and made sure none were running.

You should be able to go into Programs and Features and remove the Lync roles one at a time, leaving Core for last.  For this upgrade, this allowed the install to continue and completely successfully.

You will then manually need to go through the Skype for Business Server 2015 – Deployment Wizard to:

  1. Install Local Configuration Store
  2. Setup or Remove Skype for Business Server Components
  3. Request, Install, or Assign Certificates
Certificates

Your certificates from Lync 2013 can be re-used and in this case you will just need to re-assign them.  Otherwise, the certificates will already be installed.

Post-install

Skype for Business 2015 Upgrade installs Microsoft SQL Server 2014 as the local data store.  There will be Microsoft updates available, including Service Pack 1.  It is recommended that you keep your servers completely patched and apply these updates.

 

Reference: “Upgrade to Skype for Business Server 2015”; Microsoft TechNet; https://technet.microsoft.com/en-us/library/dn951371.aspx; Retrieved July 16th, 2015

SharePoint 2013: Trouble Reaching Server

The issue:

In SharePoint 2013 on a Windows Server 2012 R2 web server, when trying to add a domain user to a group or permissions, you receive the following message, “Sorry, we’re having trouble reaching the server.”  You may have noticed this when trying to add users as farm administrators.

Possible causes:

1: The server does not have enough resources to process the function without timing out. Verify you have the resources below (Source: http://technet.microsoft.com/en-us/library/cc262485.aspx).

Installation Scenario Deployment type and scale RAM Processor Hard disk space
Single server with a built-in database or single server that uses SQL Server Development or evaluation installation of SharePoint Server 2013 or SharePoint Foundation 2013 with the minimum recommended services for development environments. For information, see Minimum recommended services for development environments. 8 GB 64-bit, 4 cores 80 GB for system drive
Single server with a built-in database or single server that uses SQL Server Development or evaluation installation of SharePoint Server 2013 or SharePoint Foundation 2013 running Visual Studio 2012 and the minimum recommended services for development environments. For information, see Minimum recommended services for development environments. 10 GB 64-bit, 4 cores 80 GB for system drive
Single server with a built-in database or single server that uses SQL Server Development or evaluation installation of SharePoint Server 2013 running all available services. 24 GB 64-bit, 4 cores 80 GB for system drive
Web server or application server in a three-tier farm Pilot, user acceptance test, or production deployment of SharePoint Server 2013 or SharePoint Foundation 2013. 12 GB 64-bit, 4 cores 80 GB for system drive

2: If you are sure you have these minimum resources, which you probably do, it’s time to check HTTP Activation on the web server.

On the server manager page (see below) “Add Roles and Features” and click through to the “Features” tab/page.  Make sure under .NET Framework 4.5 Features -> WCF Services  that “HTTP Activation” is checked.  Under .NET Framework 3.5 Features, make sure HTTP Activation is checked.  Click “Next” and Install.  You may have to specify an alternate source if these were not already checked.  Follow the instructions on the window and you should be fine.

SharePoint 2013 on Server 2012 R2

After installing these, you shouldn’t have to restart the web server to see if the changes fixed the issue.  Try adding a user again and if it is not fixed, try the next steps.

3: Permissions

Make sure the domain account you are using for a SharePoint service account has the necessary permissions to read domain accounts.

If this didn’t resolve your issue, please let us know.  We would be interested to know what your issue was and what the solution was.