SQL11UPD01-TSCRIPT-09
This wiki is a transcript of a previously recorded video.
Related content assets:
View Previous Transcript: Demo Transcript: Configuring SQL Server 2012 AlwaysOn Availability Groups (Part 1) (SQL11UPD01-TSCRIPT-02) Welcome to introducing SQL Server 2012 AlwaysOn. This demo is configuring contained databases with SQL Server 2012 AlwaysOn Availability Groups. My name is Jonathan Kehayias. I’m a SQL Server MVP, a Microsoft Certified Master for SQL Server 2008 as well as a Principal Consultant with SQLskills.
So one of the problems associated with having databases that fail over from one server to another server, where they’re separate installed instances entirely, is that you have logins that are server-level or instance-level entities inside of the environment that don’t travel with the database from server to server. As we discussed in the presentation, the databases users are mapped to logins by SID and if the login is not created with the same SID inside the environment, that will create issues associated with failover.
So we created our DWUser for our environment and what we’re going to do is we’re going to go out and start an OLTP application that’s going to connect to our database and it’s going to start doing inserts into the environment.
What we’ll do is we’re going to go to our Availability Group and we’re going to choose to fail this over and we’re going to fail it over from EMU-SQL1 to EMU-SQL2. We’re going to connect to our new destination primary server and we fail this over it’s going to fail over. If we look inside of our failover cluster in Windows Server Failover Cluster we see that the Availability Group is owned by EMU-SQL2.
If we go to our application and we take a look – now the connections are failing for the application and they’re failing because we didn’t create the login for the DWUser on the EMU-SQL2 server. So what a lot of cases will happen is, you’ll go over to EMU-SQL2 and you’ll do DWUser; you’ll provide it the password, and we’ll create the login. And go back to our application and it’s still having connection issues. The reason that it’s having connection issues is that the new login that we created actually has a different SID than the original login that we created on EMU-SQL1.
What we’re going to do is we’re going to drop this login from the server and we are going to fail our database back over to, or our Availability Group back over to EMU-SQL1. The reason we’re going to do this is we created the login on EMU-SQL1 that has our database user and we’re going to do is we’re going to make use of contained databases and partial containment inside of the AdventureWorksDW environment to actually make our login a contained user with a password inside of the AdventureWorksDW so when we perform a failover the login credential associated with that database user now transfers with the user automatically.
So with the instance back on our EMU-SQL1 server, if we go to our server properties, the first thing that we have to do is go to Advanced and set the value for Enable Contained Databases to true. This has to be done on each of the replicas inside of our Availability Group that could become a potential primary for our database because database containment has to be enabled at the instance level to allow to the contained user authentication to occur.
Once we’ve done that, and this has already been set up on each of my systems in my Availability Group… so once we’ve set that to true, we can go to our AdventureWorks data warehouse database and go to its properties. And under Options, we have the option for containment type here and right now the containment type is None. But if we set it to Partial, we’ll actually enable partial containment for our database and because we failed back over to EMU-SQL1, if we were to start this it would be able to execute again.
One of the things I should have shown while it was running on EMU-SQL2… let me go ahead and fail this back over really fast… was changing the application so that it actually attempted to connect to EMU-SQL2 and you’ll see it has a connection failure associated with that – even if we create our login here.
Let’s back up just slightly… and we’ll create our same strong password… for our login… and we should have changed our application connection string here to point at EMU-SQL2. The end result’s going to be the same – it’s not going to be able to get a database connection because the login mismatch still occurs for the SID.
So what I’m going to do is I’m going to stop this… and then we’re going to fail back over to EMU-SQL1, and while this does the failover I’m going to go back and re-delete this login from EMU-SQL2 because we don’t want this login to be there.
And once we have the database back on EMU-SQL1 we can go back in and (let me close these windows) open the database properties, go to the Options and enable it for partial containment. So with the database enabled for partial containment, what we can do is we can go to our demo files, which are in C:\AGDemos and there’s a script call Migrate DWUser To Contained. And what we’re going to do is we’re going to execute sp_migrate_user_to_contained for the DWUser – we’re going to keep the same name associated with it and we’re going to disable the instance-level login for this user on our EMU-SQL1 instance.
So if we execute this we’ve now migrated our user to being a contained user and if we switch our connection string back to EMU-SQL1 and play, or start up our workload, it works! So if we stop and now we go to our replica and we fail it over again – and we’re going to send it over to EMU-SQL2. And connect to EMU-SQL2 which does not have a login associated with our DWUser. If we change our connection string for our application and tell it to point to EMU-SQL2, and connect, it begins to work – and it begins to work because we have leveraged partial containment and contained user authentication inside of our environment to be able to allow our database to seamlessly fail over from EMU-SQL1 to EMU-SQL2. We could also send it to EMU-SQL3 and because the authentication is a part of the database under partial containment now, that login credential will move, or database user credential will move from server to server to server with the copy of our database.
So with this we can stop this and that concludes our demo.
Return to SQL Server 2012 Developer Training Kit BOM (en-US)