Since I cannot login to the RMS Admin portal, does anyone know of a way to verify what version I am on, so I can install the correct version before migrating? I was on the latest version, but have not checked for upgrades for a couple of weeks.

Check out this page for dates of the releases: http://www.rockrms.com/Rock/ReleaseNotes.
Check the __MigrationHistory table in your database.
select * from __MigrationHistory will give you the date of the release package in its name, and a ProductVersion. Not sure how the product version relates to version 3.1 etc. Check the github site for Rock to see if you can make the link. Let me know if you find out.

Do you know if RMS will work with SQL 2012 Web Version? I assume so, since it will work with Express.

It says 6.1.0-30225 in the DB, and I don't see a way to make the connection to 3.4. Since the last update was 6/11, I must be on 3.4, because I know I have checked for upgrades since then.

Check in your packages.config file in your wwwroot directory.
I have a line that reads
package id="Antlr" version="3.5.0.2" targetFramework="net451" /

Thanks Ken, I will do that. Do you know what this line from the installation manual means? "SQL Server Express will work betst with fewer than 20,000 records". Is it referring to Rock RMS records or database records?

I'm trying to figure out if SQL express will work for us.

Yes! I have the exact same info in my file.

(I'm just not sure if the Antlr line is relevant, now. 3.5 <> 3.4 (the latest Rock version).)

I would think database records? We are a church of average Sunday attendance of around 200. There are 1756 people in our Person table, but 499,000 total rows in the db. 40% of these (206,000) are in the WorkflowLog table (I've been doing lots of testing - and this table is growing every 10 mins - need to look into this). Next largest table is PageViews (28% 138,000 rows), all the rest are smaller. Our History table is 31,000 (6%). (Hope that gives you some sense of scale.)

Ken, when you said that you "Used MS SqlServer Management Studio to create script export of old database". What script did you use? I have never exported an entire DB.

Also, when I do the export, it only has one place to set ANSI Padding to "True". Is "True" the right options?

I will write up the process I followed and what I learned. In the meantime, Google 'how to generate script SQL server management studio'.

1. In Management Studio right click your database, Tasks, Generate Scripts.


2. Next, Choose Objects: 'Script entire database and all database objects.'


3. Set Scripting Options:
   
   
   
   • save to specific location, and specify a file name.
   
   
   
   


4. Click the Advanced button.


5. Make sure ANSI Padding is FALSE, Script Logins is False, Script Owner is False (you wont be moving the logins and owner over, you'll be creating new ones on your new server(, Types of data to script - Schema and data, and I had Script Indexes, Primary Keys, Triggers and Unique Keys all True.


6. When the script file is downloaded edit it. You might need to download emeditor if the file is large.


7. Comment out all the lines that create the database, and any to do with logins.


8. Make sure any ANSI PADDING is set to ON, and there are no other ANSI PADDING lines in the script. (this created problems). Read this: http://sqlmag.com/blog/sql-server-management-studio-s-ugly-ansipadding-bug.


9. Create a new SQL database on your new server. Add a new user.


10. On your PC where you downloaded the script open a Command Prompt window, and type:
    sqlcmd -S YourNewServerName -d YourNewDatabaseThatYouCreated -i TheNameOfYourScriptFile -o TheNameOfaTextOutputFile -U NewServerDatabaseUserName -P NewServerDatabasePassword -e


11. I had to break my file into two to get it uploaded.

I'll also document later how you can use Management Studio itself to copy the db across.

SQL Web Version is "higher" than SQL Express in the product line. Technically, Rock will perform better in SQL Web because SQL Web is allowed access to more resources on your server than SQL Express. Here's the MS comparison of the versions: https://msdn.microsoft.com/en-us/library/cc645993(v=sql.110).aspx

You'll notice the biggest difference is that SQL Express can only access 1 GB ram while SQL Web can access up to 64 GB. Also, SQL Express's DB can only be 10GB and Web is effectively unlimited (524 PB).

Those two limitations give our team pause to recommending SQL Express to any church over a 2,500 attenders that plan to use Rock for a long time. Granted, our stats to compare that against come from a different software, however, we feel the large-ticket data items saved in the DB are similar (person and check images) and know how quickly those images monopolized space in the other software.

Additionally, have you read the documentation provided by the Rock team regarding migration? http://www.rockrms.com/Rock/BookContent/3/45#migratingtodifferenthosts

Thanks Trey! Yes, I have read the docs, but there is no reference to the ANSI Padding settings that Ken is talking about.

The easier way to do this is to use MS Visual Studio Express.

(I couldn't get the upload of a .bak database backup to work between Arvixe and 3essentials, but I might not have been doing it right.)

1. Create a new database on your new server.


2. Using Visual Studio, Tools, SQL Server, connect to your old database as Source, and your new database as Target.


3. Use the compare tool to compare the schema. Update the schema.


4. Use the compare tool to compare the data tables, then update the data tables. You might need to do this in groups. I found it bombed out if I tried to do my whole db in one go.

After I successfully copied the database over I reinstalled Rock from a fresh download.

1. Create a new database you can use during the install.


2. Start with an empty directory, upload the Rock install Start file, and do the install as per Rock instructions.


3. Run any upgrades so you get to the version of Rock you were on.


4. Modify the web.ConnectionStrings.config file in your wwwroot or httpdocs directory. Change the name of your database to the database that contains your production data. (Assumes your host is 'localhost'.)


5. Copy over the (PasswordKey, DataEncryptionKey) values from your old web.config on Arvixe to your new web.config on 3Essentials. This gives you access to your old passwords.

Note : I am encountering a script error when editing Workflow variables. I'm not sure if this is a browser issue, or a result of the new install. So I'm not yet sure I've done everything that needs to be done...

When following the Rock instructions for Setting Permissions on the web directories I had to go into the Advanced button to give all subdirectories full permission.

Don't forget to copy any pics you had in your old web site directories across.

Thanks Jay! Now, any way to confirm what version of upgrades a database has got to? Or does it not matter: upgrades can be re-run on a database but will only apply once?

Hey Ken.
The best way to see what version your DB is is to check out your migration history in the DB. That's the most accurate way of seeing what changes have occurred.
I'm not quite sure what you mean by the second part of your question. If you're asking if migrations can be run multiple times the answer is that they won't if your DB has them listed in the migration history. It is possible to undo a migration though.