Failed 70-432 today

NotHackingYou

Came out with a score of 466. Ironically, I felt pretty good about my answers while taking the test.

I believe the reason I failed was due to lack of practice on my part. I studied a lot and did lots of practice tests but ultimately I did not spend enough lab time. My work experience and studying carried me through installing and configuring, maintaining instances (and databases) and optimizing sql server performance.

However, I scored low in managing security, monitoring and troubleshooting (which I thought I had a good handle on?!) and implementing HA.

I feel like the test itself was fair and the results per domain are pretty accurate with how I would appraise my experience and skillset. The questions were difficult and the answers very detailed /specific but they were not worded to be confusing or misleading.

I'll use this as an opportunity to redouble my efforts with a lab setup and hopefully slay the beast next time!


Edit: For study I used the MS press book by Mike Hotek w/measureUP practice tests, Real world skills MCTS book by Tom Carpenter, and SQL Server administration in action by Rod Coolige. I read all three books cover to cover.

NotHackingYou

Started studying tonight with fresh VMs.

I created 3 server 2008 VM's on my macbook with sql server 2008 installed. I also created another 2008 instance to act as a DC. With this config, I will set up each form of database mirroring and try every scenario I can think of. Then I'll un-do it all and try log shipping, then replication. I have worked a lot with replication in the past but I don't want to miss anything this time!

When all that is done, I'll un-do all that config and spin up a virtual appliance NAS and try a failover clustering setup. I tried this on my old laptop but it could only handle 2 Server 2008 VMs at once.

jibbajabba

Good luck .. btw. - for the sake of labbing. You can download the free iSCSI target for 2008R2 so you could create an iSCSI target on your DC which you can then use for clustering - if you are running low on resources that is and you don't want to create yet another VM (NAS).

RobertKaucher

That's a shame! I'm sure you will get it next time, though.

Labing is really one of the most important components of studying for the SQL Server tests. You've got to go through the scenarios at least three times. Build your clusters and take a disk offline to see what happens or pull a cable in the NIC to watch how the failover works.

One illustrative example I have is that when I was studying for my MCSE 2003 I went as far as to set up two entirely different domains on different subnets and ensure that I was able to route email via Exchange Server between these two domains. Even though I was in no way intending on getting the Exchange certification. The important thing was that I verified my ability to configure all the protocols needed by the service to get the system working properly.

For SQL Server I would suggest that you implement HA around an application like SharePoint (installed on a separate server) fill it with some test data and then cause one of the SQL Servers to fail and watch what happens. Does it work like you expect? What sort of warnings do you get? Etc..

Just setting up or reading about clustering is not enough to drive the details into your brain cells.



Change that to "... I don't need to lab this. I understand it enough."

NotHackingYou

Thanks guys!

I am planning to introduce every break/fix/failover I can think of in these scenarios.

@Robert, I was planning to just build a quick asp.net web site to pull data out of AdventureWorks and use that. Would you suggest that or the Sharepoint setup?

Also, I think I remember something about 4 different 'modes' for failover clustering (not the quorum types). I think one of them was AlwaysOn but I can't seem to find anything about this except for SQL Server 2012. Any help here?

@jibba - Thanks, I'll look into the iSCSI target on the DC!

RobertKaucher

The reason I suggest SharePoint is because it's real world and is complex enough to bring up scenarios that you might not come to find in a simple ASP.NET data on forms sort of app. One thing that I did in the past was use the Example data bases in creating custom SharePoint lists. I had two PCs (one had VMs running the DBs and the second physical PC running SharePoint front end) then on my personal laptop I ran a script writing to a sample DB and another script populating data in SharePoint lists. I then unplugged the NIC that the first VM was using as its network and watched what happened in my fail over and made sure it occurred as I expected it would.

Always On is one of the new features of 2012. So you will not find anything on it before that version. Basically it allows highly distributed failover clustering.

NotHackingYou

Thanks Robert! I will keep looking to find out what those 4 cluster settings for SQL Server 2008 were!

Zorodzai

Hey Carl - all the best in preparing for your second attempt. I know how hard you have been working; don't give up - you goal is in sight.

NotHackingYou

Thanks, Zorodzai.

Today's update, got my 3 SQL Server instances running in a mirroring session (High safety, automatic failover) along with another VM as a DC. The failover worked as I expected as did the failback.

Going to try all the mirroring configs and failovers before I try replication (again) and finally, clustering. Learning so much more than just reading and really looking forward to trying to iSCSI target on the DC.

Part of the reason I didn't lab this stuff the first time was the time overhead of setting up a domain and all the servers with the appropriate access accounts. I'll also need to try setting a session up with certificates in a non-domain environment.


Edit: And for those counting, that's 5 VM's running simultaneously on this retina MacBook Pro!
3x Server 2008 Enterprise with SQL Server 2008 Enterprise
1x Server 2008 Enterprise Domain Controller
1x Win7


Thanks everyone!

NotHackingYou

Tonights progress with mirroring was excellent. Practiced all the different failover scenarios, switching between high performance and high safety (synchronous and asynchronous). Failed over and back with and without FORCE_SERVICE_ALLOW_DATA_LOSS. Removed mirror and failed back and forth to simulate rolling upgrades, tested partner/mirror/witness 'failing unexpectedly' (me disabling network adapter) and moving production to the running server. Practiced each method and setting using T-SQL and the GUI.


Reference material:

Rolling upgrades
How to: Install a Service Pack on a System with Minimal Downtime for Mirrored Databases

Changing transaction safety level
How to: Change Transaction Safety in a Database Mirroring Session (Transact-SQL)

High safety mode overview
Synchronous Database Mirroring (High-Safety Mode)

High performance mode overview
Asynchronous Database Mirroring (High-Performance Mode)

Quorum
Quorum: How a Witness Affects Database Availability

Remove mirroring
How to: Remove the Witness from a Database Mirroring Session (Transact-SQL)


Code for reference:

--Force principal to failover to mirror while in high-safety mode (execute on principal)
ALTER DATABASE AdventureWorks2008
SET PARTNER FAILOVER


--Force mirror to take over while in high-performance mode (execute on mirror, must not have connectivity with principal)
ALTER DATABASE AdventureWorks2008
SET PARTNER FORCE_SERVICE_ALLOW_DATA_LOSS
--Will need to resume mirroring after


--Force resume of mirroring
ALTER DATABASE AdventureWorks2008
SET PARTNER RESUME


--Pause mirroring (puts DB in suspended)
ALTER DATABASE AdventureWorks2008
SET PARTNER SUSPEND

--Stop mirroring (bring a server online that has lost quorum, disaster situation where mirror and witness are offline)
ALTER DATABASE AdventureWorks2008 SET PARTNER OFF

--Grant the service account access to the mirroring endpoint
GRANT CONNECT on ENDPOINT:: Mirroring TO [INSTANCE\USERNAME]


--Adjust mirroring failover timeout to 90 seconds
ALTER DATABASE AdventureWorks2008
SET PARTNER TIMEOUT 90



--Mirroring metadata


--Mirroring status, role, safety level and witness status. One row for each mirrord DB in this instance.
SELECT * FROM sys.database_mirroring


--Information about mirroring endpoints
SELECT * FROM sys.database_mirroring_endpoints


--One row for each witness role in this instance
SELECT * FROM sys.database_mirroring_witnesses




--Create/Configure mirroring


--Update mirroring status table in msdb.
EXEC msdb..sp_dbmmonitorupdate


--Creates SQL Agent jobs to update MSDB tables with mirroring stats
EXEC msdb..sp_dbmmonitoraddmonitoring


--Change update interval for SQL Agent job that updates mirroring stats
EXEC msdb..sp_dbmmonitorchangemonitoring 1,1


--Returns current update interval (which is set using above)
EXEC msdb..sp_dbmmonitorhelpmonitoring


--Stops and removes SQL agent job for updating stats
EXEC msdb..sp_dbmmonitordropmonitoring


--Alternative to database mirroring monitor. Same information, but text based
EXEC msdb..sp_dbmmonitorresults 'AdventureWorks2008', 0 , 0 




--Check for existing endpoints
SELECT name, role_desc, state_desc
FROM sys.database_mirroring_endpoints


--Endpoints use windows authentication by default


--Create endpoint on principal
CREATE ENDPOINT mirroring
    STATE = STARTED
    AS TCP ( LISTENER_PORT = 7575)
    FOR DATABASE_MIRRORING (ROLE=PARTNER)


--Create mirror server endpoint
CREATE ENDPOINT mirroring
    STATE = STARTED
    AS TCP ( LISTENER_PORT = 7575 )
    FOR DATABASE_MIRRORING (ROLE=PARTNER)
    
--Create witness server endpoint
CREATE ENDPOINT mirroring
    STATE = STARTED
    AS TCP ( LISTENER_PORT = 7575)
    FOR DATABASE_MIRRORING (ROLE=WITNESS)
    


--Drop an endpoint
DROP ENDPOINT mirroring

Zorodzai

Hey Carl, how is the studying going ?