Have you tried making the parameter nullable?

Hello.  I'm not surprised that the report runs fast in VS because the data are cached.  Before we start talking about tuning, I'd like to help you think through whether...

I totally understand the desire to have a clean server.  He can still have that.  Actually, IMO, things would be cleaner without the risk of a migration.    

-...

It's still a viable option.  This will take a little work, but really not a whole lot (IMO).  Here is what I would do:

- Have my server guy...

The best option would be to use a time/date dimension table if you have one.  If you don't, you can easily find one.  Using it would avoid the loop.  I...

It's a recruiting/staffing application.  I know the schema 😉

Something like this would work.  Please note, I personally would refine this - I hate looping, I would make it more configurable (i.e. tweaking parameters) and I would investigate why...

Have you thought about doing in-place upgrades to get the current server up to 2016?  If you wanted to transfer to another server you could then just restore over.  Is...

I know a good portion of your schema, but obviously not all of it.  I've seen this warning pop up in unexpected places in some of my queries.  Is rar.ContractREFERENCE the...

How do you like working with Adapt?

Your question as-is really can't be fairly answered.  Generally though, putting the 2 services on different servers is going to be best from a performance stand point.  However, that's just...

Wow, this is a scary question.  My first thought is to run for the door.  I've become very accustomed to working in an Agile environment and this sounds much different. ...

This sounds strange.  Restoring the encryption key should do the trick.  Are you sure you have the correct key?  Maybe export it from the production server just to make sure...

If you're thinking about AlwaysOn, check out Brent Ozar's video/quiz to see if you're really up for it.  It's under the header Gotchas and Problems with AlwaysOn Availability Groups.

I know your original post is really old.  Are you still looking for feedback?