locked
Is mirror database supposed to be directly updateable via INSERT statement? RRS feed

  • Question

  • [This post applies to SQL Server 2008 SP3 Developer's Edition (32-bit) with one post-SP3 update installed - GDR 5512. No post-SP3 cumulative updates installed.

    SELECT @@VERSION displays this:

    Microsoft SQL Server 2008 (SP3) - 10.0.5512.0 (Intel X86)   Aug 22 2012 15:16:00   Copyright (c) 1988-2008 Microsoft Corporation  Developer Edition on Windows NT 5.2 <X86> (Build 3790: Service Pack 2)

    OS is Windows Server 2003 R2 SP2 32-bit. ]

    Is mirror database supposed to be directly updateable via INSERT statement? I would expect it shouldn't be directly updateable at all but it seems I can successfully execute INSERT statement on mirror database via SQL Server Agent job, and this just doesn't make sense.

    This what I did:

    1) On Server1 created database called test that contains only one table:

    USE [test]
    GO
    /****** Object:  Table [dbo].[Table1]    Script Date: 11/09/2012 16:59:20 ******/
    SET ANSI_NULLS ON
    GO
    SET QUOTED_IDENTIFIER ON
    GO
    CREATE TABLE [dbo].[Table1](
    	[id] [bigint] IDENTITY(1,1) NOT NULL,
    	[Description] [nchar](100) NOT NULL,
     CONSTRAINT [PK_Table1] PRIMARY KEY CLUSTERED 
    (
    	[id] ASC
    )WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY]
    ) ON [PRIMARY]
    GO

    2) Mirrored this database to Server2, so Server2 contains database test that has Mirror/Synchronized/Restoring status

    3) Setup SQL Server agent job on Server1 (Important: SET IT UP ONLY ON SERVER1) that executes this T-SQL script every minute:

    DECLARE @I INT=0;
    SET NOCOUNT ON
    WHILE @I<10
    BEGIN
    INSERT dbo.Table1( Description )
    VALUES  ( CAST(SYSDATETIME() AS NVARCHAR(MAX))  )
    SET @I=@I+1
    END

    As expected every minute 10 new rows is inserted in dbo.Table1. They look like this:

    id	Description
    1300	2012-11-09 16:57:00.3272092                                                                         
    1299	2012-11-09 16:57:00.3115841                                                                         
    1298	2012-11-09 16:57:00.2959590                                                                         
    1297	2012-11-09 16:57:00.2959590                                                                         
    1296	2012-11-09 16:57:00.2803339                                                                         
    1295	2012-11-09 16:57:00.2647088                                                                         
    1294	2012-11-09 16:57:00.2647088                                                                         
    1293	2012-11-09 16:57:00.2490837                                                                         
    1292	2012-11-09 16:57:00.2334586                                                                         
    1291	2012-11-09 16:57:00.2334586                                                                         

    4) Failed over database test from Server1 to Server2. Now database test on Server1 has Mirror/Synchronized/Restoring status and database on Server2 has Principal/Synchronized status. Note that on Server1 I DID NOT disable job that updates database test every minute.

    5) At this point I would expect job on Server1 that updates test database to fail each time because this database is a mirror. However, this doesn't happen - it always succeeds. Not only that, test database on Server2 is being updated as if job from Server1 was running on Server2 (and it doesn't, I never re-created that job on Server2).

    How is this possible????

    It just doesn't make any sense. If try to run this query on Server1:

    USE test 
    
    SET NOCOUNT ON
    WHILE @I<10
    BEGIN
    INSERT dbo.Table1( Description )
    VALUES  ( CAST(SYSDATETIME() AS NVARCHAR(MAX))  )
    SET @I=@I+1
    END

    the output, as expected, is:

    Msg 954, Level 14, State 1, Line 1
    The database "test" cannot be opened. It is acting as a mirror database.

    However, this script somehow runs successfully on Server1 as SQL Server Job (whether scheduled or manually initiated) and updates ****Mirror***** database on Server1, and these changes are then passed to the principal database on Server2 as confirmed by querying database on Server2. Or at least this is how it looks like to me.

    Can someone explain this? What am I missing here?

    Friday, November 9, 2012 10:29 PM

All replies

  • This is not possible ...

    Can you please check the data in primary and secondary server both? if this is happening the data should not be there on primary server which is getting changed\inserted in mirror.

     

    Please check the DB and Table correctly - do the table exist in correct db ?   issue a select command by giving fully qualifies name to verify the table exists in correct db.

    Is there anyone else who createed this job for you on server 2



    Sarabpreet Singh Anand

    Blog , Personal website

    This posting is provided , "AS IS" with no warranties, and confers no rights.

    Please remember to click "Mark as Answer" and "Vote as Helpful" on posts that help you. This can be beneficial to other community members reading the thread.

    Saturday, November 10, 2012 1:21 AM
  • Hi Joe,

    i havn't tried this but what my understanding is, when you create the mirroring

    1) you are passing permissions(user details) for primary and secondary.

    2)system creating end points with port information.

    and observe the action what happend when you failover the database it automatically goes control to the other database.

    these two are working as single unit.

    Saturday, November 10, 2012 2:30 AM
  • I am not the only one experiencing this:

    SQL Agent Jobs and mirroring

    SQL Agent jobs operate across mirror

    (no explanation in these threads either).

    It's not supposed to happen but it does. I just played with it more. After rebooting Server1 (Server1 is mirror and has job configured, Server2 is principal) job starts to fail (as expected) with this message:

    "Unable to connect to SQL Server 'Server1'.  The step failed."

    Then I failover from Server2 to Server1. Job doesn't fail on Server1 anymore (as expected) because Server1 is now  principal.

    Now I failover from Server1 to Server2. Server1 is now mirror again yet the job still doesn't fail. And updates are reflected on principal database on Server2. I also ran SQL Server Profiler trace on on Server2 and it shows query from the job on Server1 (host name column in profiler grid says "Server1" for this query).

    I appreciate everyone's input but at this time I am fairly confident I am not missing something obvious.

    Thursday, November 15, 2012 11:11 PM
  • Yes, I was able to reproduce this issue as well. From profiler, I see the insert statement being run on the new principal database, so the insert statement is reaching the new principal server somehow. If you stop the SQL instance where you have new principal server then the job fails.

    You may want to post this issue on connect

    http://connect.microsoft.com/SQLServer




    Friday, November 16, 2012 5:13 AM
  • Hi JoeSchmoe115,

    Can you please let me know the version and SP level of SQL Server to reproduce this scenario.


    Sarabpreet Singh Anand

    Blog , Personal website

    This posting is provided , "AS IS" with no warranties, and confers no rights.

    Please remember to click "Mark as Answer" and "Vote as Helpful" on posts that help you. This can be beneficial to other community members reading the thread.

    Friday, November 16, 2012 9:14 AM
  • Hi JoeSchmoe115,

    Can you please let me know the version and SP level of SQL Server to reproduce this scenario.


    Sarabpreet Singh Anand

    Blog , Personal website

    This posting is provided , "AS IS" with no warranties, and confers no rights.

    Please remember to click "Mark as Answer" and "Vote as Helpful" on posts that help you. This can be beneficial to other community members reading the thread.


    SQL Server 2008 SP3 Developer's Edition (32-bit) with one post-SP3 update installed - GDR 5512. No post-SP3 cumulative updates installed.

    SELECT @@VERSION displays this:

    Microsoft SQL Server 2008 (SP3) - 10.0.5512.0 (Intel X86)   Aug 22 2012 15:16:00   Copyright (c) 1988-2008 Microsoft Corporation  Developer Edition on Windows NT 5.2 <X86> (Build 3790: Service Pack 2)

    OS is Windows Server 2003 R2 SP2 32-bit.
    Friday, November 16, 2012 2:24 PM
  • Yes, I was able to reproduce this issue as well. From profiler, I see the insert statement being run on the new principal database, so the insert statement is reaching the new principal server somehow. If you stop the SQL instance where you have new principal server then the job fails.

    You may want to post this issue on connect

    http://connect.microsoft.com/SQLServer




    I appreciate you taking time to reproduce it.

    What version of SQL Server did you reproduce it on? I wonder if it is fixed on 2008 R2 or 2012.

    Friday, November 16, 2012 3:38 PM
  • Here is an version of my original post to demonstrate the issue (I didn't want to modify my original post in order to preserve thread continuity):

    [This post applies to SQL Server 2008 SP3 Developer's Edition (32-bit) with one post-SP3 update installed - GDR 5512. No post-SP3 cumulative updates installed.

    SELECT @@VERSION displays this:

    Microsoft SQL Server 2008 (SP3) - 10.0.5512.0 (Intel X86)   Aug 22 2012 15:16:00   Copyright (c) 1988-2008 Microsoft Corporation  Developer Edition on Windows NT 5.2 <X86> (Build 3790: Service Pack 2)

    OS is Windows Server 2003 R2 SP2 32-bit. ]

    1) On Server1 create database called test that contains only one table:

    USE [test]
    GO
    
    /****** Object:  Table [dbo].[Table1]    Script Date: 11/16/2012 11:24:05 ******/
    SET ANSI_NULLS ON
    GO
    
    SET QUOTED_IDENTIFIER ON
    GO
    
    SET ANSI_PADDING ON
    GO
    
    CREATE TABLE [dbo].[Table1](
    	[id] [bigint] IDENTITY(1,1) NOT NULL,
    	[DateStamp] [datetime2](7) NOT NULL,
    	[Comment] [char](100) NULL,
     CONSTRAINT [PK_Table1] PRIMARY KEY CLUSTERED 
    (
    	[id] ASC
    )WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY]
    ) ON [PRIMARY]
    
    GO
    
    SET ANSI_PADDING OFF
    GO
    

    2) Mirror this database to Server2, so Server2 contains database test that has Mirror/Synchronized/Restoring status

    3) Setup SQL Server agent job on Server1 (Important: SET IT UP ONLY ON SERVER1) that executes this T-SQL script every minute:

    DECLARE @I INT=0;
    SET NOCOUNT ON
    WHILE @I<10
    BEGIN
    INSERT dbo.Table1( DateStamp, Comment )
    VALUES  ( SYSDATETIME(),  'Server: ' + @@SERVERNAME + ';  Host: ' + HOST_NAME())
    SET @I=@I+1
    END

    As expected every minute 10 new rows is inserted in dbo.Table1 on Server1. They look like this (queried database on Server1):

    id	DateStamp	                                Comment
    373	2012-11-16 10:24:00.6394224	Server: Server1;  Host: Server1                                                          
    372	2012-11-16 10:24:00.6081722	Server: Server1;  Host: Server1                                                          
    371	2012-11-16 10:24:00.5925471	Server: Server1;  Host: Server1                                                          
    370	2012-11-16 10:24:00.4831714	Server: Server1;  Host: Server1                                                          
    369	2012-11-16 10:24:00.4362961	Server: Server1;  Host: Server1                                                          


    4) Fail over database test from Server1 to Server2. Now database test on Server1 has Mirror/Synchronized/Restoring status and database on Server2 has Principal/Synchronized status. Note that on Server1 I DID NOT disable job that updates database test every minute.

    5) At this point I would expect job on Server1 that updates test database to fail each time because this database is a mirror. However, this doesn't happen - it always succeeds. Not only that, test database on Server2 is being updated as if job from Server1 was running on Server2 (and it doesn't, I never re-created that job on Server2).

    When I query database on Server2 it looks like this:

    380	2012-11-16 10:25:00.2820900	Server: Server2;  Host: Server1                                                          
    379	2012-11-16 10:25:00.2664650	Server: Server2;  Host: Server1                                                          
    378	2012-11-16 10:25:00.2664650	Server: Server2;  Host: Server1                                       

    As you can see, Server Name changed to Server2 (as expected) yet the host is still Server1. It can also be verified by using SQL Server Profiler.

    SUMMARY: Not only mirror database is modified by SQL Server Agent Job, changes from mirror are propagated to the principal, which doesn't make any sense. It's not supposed to happen but it does.


    Friday, November 16, 2012 4:38 PM