My environment is the following: VMWare 5.5 vitalized server MS Windows Server 2008R2 Enterprise domain and SQL Server 2008 R2 Enterprise. Centralized storage with fibre-channel connection.
I have partitions in my
SQL Server DB. I have 2
file groups: one with live data (FG1), second with historical data (HDG).
The second file group is
read-only. Each month I make movement in partitions - I add new data (from previous month) to historical data. This process is automatic.
We moved our database to a new server. Initially, I had to do the process manually. During this operation my mirror breaks down (after operation 3 - see process flow bellow) with the following error:
ON PRINCIPAL SERVER:
ROW 0 in LOG:
Date 15.6.2015 20:54:11 Log SQL Server (Current - 16.6.2015 07:55:00) Source spid84 Message Setting database option MULTI_USER to ON for database MYDB.
ROW 1 in LOG:
Date 15.6.2015 20:54:11 Log SQL Server (Current - 16.6.2015 07:55:00) Source spid18s Message Error: 1453, Severity: 16, State: 1.
ROW 2 in LOG:
Date 15.6.2015 20:54:11 Log SQL Server (Current - 16.6.2015 07:55:00) Source spid18s Message 'TCP://10.201.27.154:5022', the remote mirroring partner for database 'MYDB', encountered error 823, status 3, severity 24. Database mirroring has been suspended. Resolve the error on the remote server and resume mirroring, or remove mirroring and re-establish the mirror server instance.
REMARK: I executed this operation on the old server many times automatically and I never experience such error.
ON MIRROR SERVER:
ROW 1 in LOG:
Date 15.6.2015 20:54:11 Log SQL Server (Archive #3 - 15.6.2015 21:33:00) Source spid17s Message Error: 823, Severity: 24, State: 3.
ROW 2 in LOG:
Date 15.6.2015 20:54:11 Log SQL Server (Archive #3 - 15.6.2015 21:33:00) Source spid17s Message The operating system returned error 5(Access is denied.) to SQL Server during a write at offset 0000000000000000 in file 'e:\Databases\MYDB_HISTRICAL.ndf'. Additional messages in the SQL Server error log and system event log may provide more detail. This is a severe system-level error condition that threatens database integrity and must be corrected immediately. Complete a full database consistency check (DBCC CHECKDB). This error can be caused by many factors; for more information, see SQL Server Books Online.
MY PROCESS IS FOLLOWING:
1. I make several backups of the Database (Full, File Group and TLog backups).
2. I set DB to
RESTRICTED_USER (to allow removing read-only of historical file group flag by script).
2a. I remove
READ-ONLY flag of my Historical File Group.
3. I set DB to
MULTI_USER to allow normal operation of our software.
4. I update partitions so the data is moved the the historical file group.
5. I repeat steps 2, 2a and 3 in so I can set historical file group READ ONLY again.
6. I make backups again.
Does anyone have idea why I receive that error?
EDIT: We receive the same issue during the different phase of the procedure. This is the only situation in which mirror breaks down so I suppose the problem is inside the procedure, but I can not figure out why!