Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

I am testing the AlwaysOn feature of SQL Server 2012. I have 2 servers set up and can successfully create availability groups and fail them over between servers. However, any databases that DO NOT belong to an availability group are stuck in a status of "Restoring" on the secondary server. I have started from scratch several times, and the same thing happens to these databases. Is this normal behavior? I was assuming that databases that don't belong to an AG would simply not show up on the secondary server.

share|improve this question
add comment

2 Answers

How did you setup your Availability Group? It sounds like what happened is you blanketed the backups and restores from the primary replica server to the secondary replica server.

In other words, you did a:

backup database <DatabaseName>
to disk .... <so on and so forth>

to all databases on the primary and a

restore database <DatabaseName>
from disk ....
with norecovery

to all the backups aimed at the secondary replica server (proper operations for future AG databases). If you don't join one of those databases to the availability group then it'll stay in that restoring state.

The main question here is, if Database1 (for example) on your primary replica server as not meant to be in the availability group, then why would it be restored/existing on the secondary replica server to begin with?

share|improve this answer
    
I have not attempted any backup/restore via T-SQL. I have been using the Availability Group Wizard only, and only select a handful of databases out of 30+. When the wizard is complete, the databases I did not select show "Restoring", and the databases I did select show "Synchronized" as expected. –  SomeGuy Jun 19 '13 at 15:00
1  
So databases you didn't select during the wizard actually appear on the secondary replica server all of a sudden? –  Thomas Stringer Jun 19 '13 at 15:25
2  
If that's the case, then it sounds exactly like what I explained in my answer, whether or not you did that directly or that was your (or the wizard's) intention. My recommendation.... use T-SQL to create all this. You can easily script out your schema and modify accordingly. That will ensure you have complete control over everything that happens. –  Thomas Stringer Jun 19 '13 at 15:33
add comment
up vote 0 down vote accepted

I have a half-answer at least. My first attempt at creating an Availability Group consisted of every database. I later decided to only try a few databases in a group. So I deleted the databases on the secondary after removing them from the AG. Subsequent attempts to create availability groups with a limited set of databases triggered the stuck "Restoring" state. I am not sure why. I stopped SQL Server on the secondary, deleted the mdf and ldf files, then restarted SQL Server. All databases still showed "Restoring", but I was able to delete them in SSMS. I now have just the databases that I intended to have in the availability group showing on the secondary as "Synchronized"

share|improve this answer
add comment

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

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