support of mirroring Vault Database

[hwapdc]

Does Vault Standard supports mirroring of Vault Database and automatic fail over.

SQL Server 2008 supports real time mirroring of databases as outlined in
http://technet.microsoft.com/en-us/libr ... 88712.aspx.

I am interested in setting up High safety with automatic failover (synchronous)
In the case the principal database server fails the mirror server assumes the role of principal server and normal operations of database services continues.
How do I set this up for the Vault Service?
Do I have to manually edit the connection string in web.config to facilitate automatic failover?
Is this a supported setup for Vault?

[Beth]

Vault does not support mirroring.

What you could do is allow your database to mirror, but then when the first one goes down, you could install a second instance that would take over the mirrored database. With practicing it a few times and documenting your actions, this can become a very fast procedure.

What a few users do is they have a backup Vault server that they restore a backup to every day and then rerun a few scripts relating to permissions. It's not a real-time fail-over, but then users can at least perform a Get of code from the prior day or older and view history.

I would still recommend periodic SQL backups even with having a mirror.

[ajj3085]

I know this isn't supported by vault, but you might be able to setup mirroring anyway (unless there's a reason this wouldn't work that SourceGear knows).

Setup mirroring as you would, and simply add the Failover Partner=<mirror_server> to the configuration strings in the web.config.

Not promising this would work with Vault, but this is how we setup mirroring on our databases for our products at work (we have mirror + witness server).

HTH
Andy

[Beth]

We haven't tested it here to confirm or deny the possibility of doing this. If you went ahead with it and anything were to go wrong with it, we wouldn't be able to help.

[hwapdc]

Has there been any development in this issue? I do believe support of mirroring (not replication) is crucial in a production environment. To my knowledge there is no way an application accessing a database can detect or observe whether the database is mirrored or not. My main objective is just to have an online backup ready if disaster strikes – I would not go for automatic failover. So my question again: will Sourcegear support mirrored sgvault database? (Vault version 6)

[ajj3085]

I think this is something SourceGear should support. Its a bit unreasonable for a development shop to not be able to commit to their source control system because only one non-mirrored database server is supported.

[Beth]

This was discussed in a little more detail in this thread: http://support.sourcegear.com/viewtopic.php?f=5&t=10367.

hwapdc: When you say " I would not go for automatic failover," do you mean that the database would just sit there, and then when the primary database fails, you'd perform another Vault install and point it to the new database? I'm not sure how else you'd use the mirror if you didn't have it do a failover to the other server.

If the mirrored database is just sitting there as a means of a backup, I can't see there being a problem. When the first server fails, then I think you could then treat this in the same manner as if you moved the Vault database (http://support.sourcegear.com/viewtopic.php?t=924).

[hwapdc]

@Beth:Yes I would go for manual failover. The main purpose of mirroring is to have an online backup. Ready to be used in case disaster strikes. In case SQL Server A fails I would do a manual failover to SQL Server B. Vault databases at SQL Server B would be active (primary). I can easily reinstall Vault Web Service and point it to the databases at SQL Server B. That would be fine with me

[Beth]

I can easily reinstall Vault Web Service and point it to the databases at SQL Server B.

I think that route should work fine.

[whatdoesthisdo]

I'm completely astonished that a product that is designed for the protection and archiving of source code would little to no support for Database Mirroring within MS SQL Server. If you are a person that uses Database Mirroring or wants to do so in order to run a High Availability application, you NEED to know how to fix orphaned SQL Logins/DB Users on the Mirror instances.

Contrary to some of the posts I've seen, the availability/performance mode you choose to run the mirror with is irrelevant. That is up to what you want your application to do. Seriously, why put in the effort if you aren't going to make it an automatic failover process? It's not any more difficult than setting up mirroring in the first place.

No matter which mode you use, the same problem exists. Orphaned database users and/or mismatched SIDs on the login. I'm using SQL logins for authentication in my environment. YMMV.

In a typical scenario, you'd have an SQL Server Login mapped to a Database User inside the database you're mirroring. Initially, Server_A is the Principal and Server_B is the Mirror. When failover occurs (manually or automatic), the roles of Principal and Mirror are reversed between the two servers. Think of it as the database literally moving to a new SQL Server. In fact, it is LITERALLY a database that has been restored (from backup) to a different server. The SQL Login that the Database User was mapped to on Server_A, does not exist on Server_B. This means there is no longer any mapping. The Database User is now orphaned. There is no tie between SQL Login and Database User on the Server_B.

Orphaned database users are a common problem when restoring databases to server other than the original source. You may have already experienced this problem in the past with other scenarios. Add Database Mirroring to the mix and you have one extra issue to fix. Creating a new SQL Login with the same username/password will not be enough. The Database User and the SQL Server Login must also have matching SIDs for Mirroring to be successful for you.

Microsoft has an excellent bit of T-SQL that can help you transfer the logins, passwords and SIDs, between SQL Servers/Instances. They have you create a new stored procedure on the source server. Then execute that stored procedure to generate a script to that will help you create all the logins from the source server on any other server. It includes the existing password (hashed) and importantly, the SID. You'll likely only need just one line from the output concerning the SQL Login that you're application (Vault Server) needs as it is missing on the other server. Create that SQL Login on Server_B using the relevant portion of the script that was just generated.

http://support.microsoft.com/kb/918992

Once you have matching SQL Logins and Database Users on both Principal and Mirror, with matching SIDs across the board, you will be in a good place.

Lastly, you need to make Vault Server's connection string inside web.config aware of the "Failover Partner=MIRRORSERVERNAME". I'll let you do your own research if you don't already understand that part.

I've tested this with IIS 7.5/SQL Server 2008 R2 and am happy with the results so far.

Keywords: SQL Database Mirroring Fix Orphaned Database Users SQL Login SID Failover Partner

[Beth]

Mirroring appears to mean multiple things to multiple people, so I should probably be more specific on what I'm talking about.

SQL Clustering: This is managed by SQL and when one instance fails the other picks up. I have users successfully using this. There is still only 1 Vault server, and it thinks it's connecting to one SQL Server, even though behind the scene, SQL Server is managing multiples.

SQL Replication: This is where a SQL Server copies it's database to a new location. There are cases where it's a one way mirror, and other cases where it's a two way mirror and syncs up. This particular case that users have brought to me involves 2 Vault servers and each attaches to its own SQL Server. I don't have any customers that I'm aware of successfully running this setup. Since the two Vault servers wouldn't be aware of each other, I would expect them to create duplicate transaction IDs inside the databases. Then after syncing up, I would suspect there would be problems.

Setting up either of these cases would fall outside Vault support.

whatdoesthisdo: You're talking about the first case where it's a fail-over cluster, right?

[whatdoesthisdo]

I'm not talking SQL Replication or Clustering. One should be clear and aware that "Database Mirroring" means exactly one thing when dealing MS SQL Server. It should not mean different things to different people. Any solution that provides multiple of the same databases that are writeable simultaneously would be a bad idea for tracking source code.

I'm literally talking about the Microsoft SQL Server feature of "Database Mirroring" running in the "High safety with automatic failover (synchronous)" operating mode. This provides for redundancy of data and only allows the database(s) to be accessed on the Principal role, never the Mirror role. During a failover condition, the roles reverse between the physical servers, but only one instance would be accessible at any given time.

I have mirrored both the sgmaster and sgvault databases using the above mentioned operating mode. Automatic failovers and those triggered manually work as one would expect the "Database Mirroring" feature to work. You can't query both servers simultaneously, but, that is not a goal when using Database Mirroring. It is for redundancy should the Principal database server go offline.

After Database Mirroring is in place for both databases, you have to make the application's (VaultService, in this case) web.config aware that there is a "Failover Partner" available should the primary Data Source become unavailable. If an application has a "connection string" for use with MS SQL Server, it more than likely can support the "Failover Partner=" setting. Just customize for your own server/instance name and add the setting to the connection string in the existing web.config. You'll have something similar to:

Code: Select all

<add key="ConnectString" value="Application Name='SourceGear Vault Server'; Connection Reset='true'; Server=SERVER_A; Failover Partner=SERVER_B; Database=sgvault; User ID=sgvaultuser; pwd=SomePassword" />

Database Mirroring is easily manageable on its own and if configured properly, provides trusted layer of redundancy for your databases whether you have an application that can failover or not. Configuring the VaultService application to be Failover Partner aware is easy as are many applications. Once the application is aware of the failover partner, it has to be able to log in on the Failover Partner server and access the appropriate databases during a failover condition. That's why creating an exact duplicate of the SQL Login, including SID, and mapping it to the Database Users on the Failover Partner becomes important.

1) mirror databases.
2) make application aware of failover partner existence.
3) re-create login with SID and map to database user.

That's all there is to it.

[Beth]

Thank you for the information.

I can point customers who are interested in this to the information you provided.