I have a Web forms asp.net site running with a database on a VM (VM1). I also have a MVC 5 Web app that has its own membership database on another VM (VM2). I want to retrieve customer information from VM1 to create a login on VM2.

My thoughts were I could create a backup database(or mirror) for VM1 to run member queries on that to retrieve information. Is this plausible? What methods would you recommend?

One method that I think I am able to obtain, would be creating a webservice on VM1, but it just seems the VM2 would be constantly using this webservice and I really want to obtain the best performance possible. Any help is much appreciated.

  • How big is the VM1 database? Do you need all the data? the biggest table in the DB? Is something as simple as linked servers a possibility (gives you access to the VM1 data live)? How current does the info from VM1 need to be? Should a customer who was just added in VM1 be able to log into the VM2 web app immediately? in five minutes? 24 hours?
    – RDFozz
    May 31 '17 at 18:00
  • Great questions! VM1 database is not that big. Customer table Data Space is ~2 MB. Is 'linked servers' achieved through Azure I am not familiar with that term. I would just need the customers email and then I would use that to create their login on VM2, maybe also pull in some licensing data. 5-10 minutes seems good.
    – avidgamer
    May 31 '17 at 19:41
  • Are these Azure DBs? That's not clear from the question. You can set up a link between two SQL Server databases (and other DBs, and even non-SQL data sources) in regular SQL Server - I haven't played with Azure, so I'm not sure what's possible there. You might want to check out this SO post - it's mostly a linkfest, but you might find something helpful there. Look for the info on "Elastic Query" if VM2 is on Azure, as you may not be able to create linked servers in your Azure DB.
    – RDFozz
    May 31 '17 at 20:20
  • To clarify, they are not Azure databases, they are VMs on Azure though.
    – avidgamer
    May 31 '17 at 20:21

With database mirroring, you have to create a snapshot of the database in order to run queries against it.

A few problems there:

  • Snapshots are an Enterprise Edition feature, which put it out of the price range of most shops
  • Running queries against the snapshot mean the server is in use, so you have to pay full Enterprise Edition pricing on it (it's not a free standby server at that point)
  • The queries are only up to date as of when the snapshot was taken
  • To refresh the snapshot, you have to drop it and take another snapshot (which means getting all users out of the snapshot to drop it)

Because of those, running queries against a mirror isn't usually cost-effective.

If you're willing to stomach the Enterprise Edition price tag, SQL 2012 added Always On Availability Groups, which let you read directly from the secondary with nearly real-time data at a much lower management cost. Still has the EE price tag, though - readable secondaries are an EE-only feature.

Generally speaking, performance tuning is easier than licensing more EE cores ($7k/core USD.)

  • I did look into making snapshots from the mirror. But that problem you stated about refreshing the snapshot and dropping the current seems cumbersome. This answers my question in regards to running queries on a mirrored database. Seems I need to focus my attention to performance tuning and look into an Always On Availability groups to see if I can achieve what I am looking for. Thanks.
    – avidgamer
    May 31 '17 at 19:49
  • Question here.. With Always On Availability Groups, does that mean it goes back to the main server to run the queries? We are trying to setup a duplicate of our prod environment to run large queries against without performance hits on prod that doesn't make us change our DR plan. Always On Availability Groups sounds right, but not if it goes back to prod to actually grab the data. Feb 14 '18 at 16:58
  • @alexander7567 for questions, go ahead and open a new question. You don't want to start new Q&A in the comments. Thanks!
    – Brent Ozar
    Feb 14 '18 at 18:24
  • Thought it was related enough I could ask here. Posted new question.. dba.stackexchange.com/questions/197937/… Feb 14 '18 at 19:04
  • @alexander7567 great question writeup! Good job, and you've got good answers over there already.
    – Brent Ozar
    Feb 14 '18 at 22:32

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.