June 24, 2010 at 6:07 am
Hey guys,
I'm attempting to setup a mirror between two sql 2008 servers for the very first time. On my principal server(SQL-A) i've created a full backup of my "prod" database along with a full back up of the transactionlog. On my target server(SQL-B) I've done a restore with NORECOVERY and everything seems to be fine, except it has (Restoring....) next to the name of my data base "Prod" on SQL-B. I figured i needed to do a restore in NORECOVERY of the transactionlog and it would remove the (Restoring...) but it still says this. What am I missing here? Is this suppose to be what it looks like? How do I know the changes on SQL-A are being copied to SQL-B when I can't view the tables etc since its in (Restoring...) when i complete the mirroing?
Secondly, I went ahead and tried to setup mirroring between SQL-A and SQL-B but I get the error message:"The mirroring configuration is not supported. Because the principal server instance, DARWIN-SQL, is Standard Edition, the mirror server instance must also be Standard Edition? Both of these servers are running 2008 standard!
Any help you guys can give me is much appreciated!
June 24, 2010 at 6:16 am
OK, I ran the command "RESTORE DATABASE PROD WITH RECOVERY" and I can see my database and view all the tables etc! Now I'm still having the issue with the versions are different. I checked the "Help" menu and then went to about SQL server and the versions looks the same. any ideas?
June 24, 2010 at 6:33 am
you had it right the first time.
1.full then log backup on principal.
2.restore full then log with norecovery on mirror
3.establish mirroring
when you restore with norecovery, the database will be in a (Restoring...) state. When mirroring is established, the principal will show DatabaseName(Pricipal\Connected) and the mirror database will show as DatabaseName(Mirror,Synchronized\Restoring...)
As far as the editions, make sure they are the same, check patch versions etc. here's the microsoft link, might be of some help.
June 24, 2010 at 7:25 am
Actually i found the problem. The target version has developer edition installed instead of standard edition. I need to unistall dev edition and install the standard and i'll try again.
June 24, 2010 at 7:42 am
Don't think that is the problem at all. You need to restore the database and transaction log using NORECOVERY, then establish the mirror connection, starting with establishing the partner connection from the MIRROR side first, then the PRINCIPLE side.
June 24, 2010 at 8:07 am
So your saying I have to initiate the mirror on the mirror server not the principal server? This goes against all the articles i've read.
June 24, 2010 at 9:31 am
So i got the correct versions on both sides, did a full back up of the database and log files, did a restore on the backup server with no recovery on both the database file and log file and now when i'm trying to setup mirroring i get the following error:
The server network address "TCP://dcom-sql:5022" can not be reached or does not exist. Check the network address name and that the ports for the local and remote endpooints are operational.(Microsoft sql server, error:1418).
Firewalls are off on both servers, one is on a domain, one is just in a workgroup on the local LAN. I can't initiate the mirror on the backupsql machine because it says DBNAME(Restoring...) and when i right click it, and then do a mirror it says Database DBNAME cannot be opened because it is in a middle of a restore?
Any ideas??
Thanks!
June 24, 2010 at 9:53 am
Use the GUI, worry about scripting it later.
Start with the principal. Right click > tasks > mirror, click configure security. The wizard will walk you through specifying each database (and witness) and specifying an endpoint name. When complete, the wizard will create the necessary endpoints and establish the mirroring.
Not sure about one being on a domain and one not. Check the link I posted earlier, namely the part about logins and users.
June 24, 2010 at 11:04 am
Calvo,
Thanks for the response. I have been using the wizard the whole time. For 2008 standard, do i need to have a witness server? that may be the issue. I've been trying to set it up without one.
June 24, 2010 at 11:23 am
Using the GUI, work from the Principal Server. I didn't use the GUI when establishing my mirror databases, did it all using T-SQL, and yes, I initiated the mirror from the mirror side first.
With one server in a workgroup and the other in a domain, you may need to use certificates to authenticate between servers.
June 24, 2010 at 11:33 am
There was another step I needed to do, had to grant connect privs, but not exactly sure what that step was off the top of my head. I will check my notes when I get back to my office.
June 24, 2010 at 12:17 pm
I had to create a login for the service account running SQL Server and grant it CONNECT priveledges to the mirroring endpoint.
June 24, 2010 at 12:29 pm
So im connecting from the principal server to the mirror server using "SQL Server Administration", the sa account. How would I grant the sa account CONNECT privileges on the mirror server?
June 27, 2010 at 12:17 am
alex.kolesar (6/24/2010)
So im connecting from the principal server to the mirror server using "SQL Server Administration", the sa account. How would I grant the sa account CONNECT privileges on the mirror server?
Again, I had to create a login for the service account running the SQL Server engine, and grant it connect privileges to the mirror endpoint on the server.
June 28, 2010 at 11:29 am
alex.kolesar (6/24/2010)
So im connecting from the principal server to the mirror server using "SQL Server Administration", the sa account. How would I grant the sa account CONNECT privileges on the mirror server?
You can't grant access to the 'sa' account to another server. You have to use certificate authentication.
Viewing 15 posts - 1 through 15 (of 26 total)
You must be logged in to reply to this topic. Login to reply