By Peter Jarrett - Tuesday, April 24, 2012 3:01:53 PM
The Myriad v4 Family uses Microsoft SQL Server as the Database Backend as it's a proven industry standard Database system that is hugely scalable from the smallest installations on Netbooks and tablets up to massive multi-server Clusters in Data centers.
It can be a daunting process getting it installed and running, but it's fairly straightforward and hopefully this post will help you with some resources on getting it up and running.
Microsoft SQL Server can be a very complicated beast to maintain, but as long as you install it correctly then you should have no problem using it day to day.
The first thing to know is that there are several different companies making SQL based Server Products and people sometimes accidentally call these "SQL Server", but it's important to know that we mean "Microsoft SQL Server" - not "MySQL" or "Oracle" or any other products from other companies. Wherever you see "SQL Server" mentioned below, we mean "Microsoft SQL Server"
The second thing to know is that Microsoft SQL Server comes in several Versions - for example MS SQL 2000, 2005, 2008, 2008R2 and the all new 2012 edition. For the purposes of Myriad, we have tested on "Microsoft SQL Server 2008R2" and "Microsoft SQL Server 2012" - other versions may work, they may not!
The next thing is that SQL Server 2008R2 and 2012 both come in various editions. Some of these are : "Compact", "Express", "Standard", "Enterprise" and "Datacenter". Myriad v4 has been ONLY been tested on the "Express", "Standard" and "Enterprise" editions.
A full discussion of the differences between these versions can be found at http://clinthuijbers.wordpress.com/2010/07/19/sql-server-2008r2-editions-compare/
So to summarise so far - we recommend that you use Microsoft SQL Server 2008R2/2012 "Express", "Standard" or "Enterprise" Editions.
One common misconception about MS SQL Server is that it's expensive - like so many things in life, the answer is "It can be"! The good news is that the Express Edition which is more than capable of running your average Myriad setup in a typical Radio Station is absolutely free. Yes. Free.
Not surprisingly it is missing some of the features of its bigger brothers - things like automated backups, realtime Mirroring etc. and some reporting services, but none of these are REQUIRED for Myriad to run, but you may well want to consider them as you move forward with deployment. Needless to say P Squared is a Microsoft Partner and we are more than happy to go through the differences with you, and can naturally give you pricing information if you do go down that route - and you may actually be surprised at how affordable it can be if you need it!
If you are running a larger setup then you may well want to consider using Standard Edition, or perhaps even 2 copies of Standard Edition mirrored together for resilience sake, but to evaluate Myriad on a single machine or on a small group of PC's SQL Express is more than adequate.
(if you need to download can download MS SQL Server 2008R2 Express ("with Management Tools") then it's available from Microsoft's website via http://www.microsoft.com/en-us/download/details.aspx?id=26729 and then select the "SQLEXPRWT_x86_ENU.exe" for 32 bit versions of windows or "SQLEXPRWT_x64_ENU.exe" for 64 bit versions of Windows)
Important: Please make sure you DO select the "SQLEXPRWT..." version - the "WT" bit means "with tools" which is extremely important for managing the SQL Server configuration.
If you think you might want to evaluate Standard Edition then if you visit https://www.microsoft.com/en-us/server-cloud/products/sql-server/Resources.aspx you can register for a trial version of SQL Server Standard edition.
Ok, so one way or another you have hopefully now downloaded a version of SQL Server
The next step is to decide where to install it - if you are planning on installing Myriad on a single PC only then this is a no-brainer - just install SQL on that PC.
On the other hand if you are going to run Myriad on several PC's - for example a couple of studio PC's and an Admin pc, then you will need to put SQL somewhere central. Usually you would have a Windows File Server in such a setup so this would be the ideal machine to install SQL Server on.
There is a very good walkthrough on how to install SQL Express at http://blogs.msdn.com/b/petersad/archive/2009/11/13/how-to-install-sql-server-2008-r2-express-edition-november-ctp.aspx - Installing SQL Server Standard is a very similar process so you should also be able to use this to help you there as well.
(Although these walkthroughs are based on SQL 2008R2, the actual process is basically identical for SQL2012)
There is one cautionary note though - during the install you will come to the "Database Engine Configuration" page of the wizard where it asks you who the adminstrators of the SQL Server will be. By default SQL Server only puts in the currently logged in user. You should also add the local "Administrators" group to this list. Also - although this is not Microsoft Security Best Practice - you should consider adding in the local "Users" group. The reason it's not best practice is that this means that ANY user of the PC can potentially fiddle with the SQL Servers setup, so this isn't ideal in the final environment, but is fine for initial testing.
Once you've finished installing SQL Server you should run Microsoft Update a few times to get all the latest Security etc. updates and do any rebooting that you need to do.
By default SQL Server has remote network connections disabled - this is fine if you are just running on a single PC, but if you are planning on connecting to this "Instance" of SQL Server then you will need to enable Remote Connections. A good explanation of how to do this can be found at http://www.linglom.com/2009/03/28/enable-remote-connection-on-sql-server-2008-express/
Right, by this point you are largely finished with setting up SQL Server for Myriad to use - assuming all the steps above have been done then all you should need to do is enter the "Connection String" into Myriad and you should be up and running. The good news is that Myriad comes with suggested Connection Strings already in the Setup Wizard and the drop down boxes for the SQL Server connection in Myriad File Locations. All you should have to do is make sure that the server name is the name of your PC, and if you have installed SQL Express that you append "\SQLExpress" onto the end of the server name.
For example, a server called "MyServer" running SQL Standard , you would just put the Server Name as : MYSERVER
If on the other hand you had installed SQL Express then you have to put : MYSERVER\SQLExpress
Thats pretty much it, at this point hopefully Myriad will be up and running.
By Andrew Burns - Monday, April 22, 2013 1:49:30 PM
Just thought I'd add a little note about the Backup of SQL files. There is a slight gotcha that it would be good to be aware of, as it may be something you only find at the point to need to try to restore a database.
Looking at the Back Up Database window in SQL Server Management Studio you have a section that allows you to select the Destination for the back ups which will let you add several file locations to the backup set. This is not, as you may expect, going to create several independent backups which are all stand alone, rather this will split the backup over the files you have selected. This means to restore the database you would need ALL of the files listed in the backup window and if any are missing, you have an invalid backup set.
The option to Mirror the backup set (which would create multiple separate backups) is not available in SQL 2008 R2 Express, but is available in the more powerful versions. In Express it would probably be better to backup to a single file, as this could then be copied to another location to give you a second backup off the server.
It is very unlikely that the Myriad databases would ever get to the sort of size that would require you to split the backup over several devices or locations, so this should not really cause any problems with using a single file.
For more info about creating backups, take a look at this MSDN page http://msdn.microsoft.com/en-us/library/ms187510(v=sql.105).aspx