Exam 70-458 – Objective 4 – Design and implement a data warehouse

This entry is part 4 of 8 in the series Exam 70-458

  Design and implement dimensions Design shared/conformed dimensions Conformed dimensions have same meaning to each fact table (http://www.jamesserra.com/archive/2011/11/conformed-dimensions/) Determine whether you need support for slowly changing dimensions (http://msdn.microsoft.com/en-us/library/ms141715.aspx) If old values must be retained then you need this Determine attributes (http://msdn.microsoft.com/en-us/library/ms174760.aspx) Design hierarchies (http://msdn.microsoft.com/en-us/library/ms174935.aspx) Determine whether you need star or snowflake schema (http://dwhlaureate.blogspot.com/2012/06/difference-between-star-snowflake.html) Star schema …

Read More “Exam 70-458 – Objective 4 – Design and implement a data warehouse”

Exam 70-458 – Objective 3 – Implement high availability

This entry is part 3 of 8 in the series Exam 70-458

  Implement AlwaysOn Implement a mirroring solution using AlwaysOn Availability modes (http://technet.microsoft.com/en-us/library/ff877931.aspx) Asynchronous-commit mode Synchronous-commit mode Steps to perform Create mirroring endpoint (http://technet.microsoft.com/en-us/library/ff878259.aspx) Enable AlwaysON (http://technet.microsoft.com/en-us/library/ff878259.aspx) Create an availability group (http://technet.microsoft.com/en-us/library/ff878307.aspx) ALTER AVAILABILITY GROUP GroupA ADD DATABASE DB Create an availability group listener (http://msdn.microsoft.com/en-us/library/hh213080.aspx) Add a secondary replica (http://technet.microsoft.com/en-us/library/hh213078.aspx) ALTER DATABASE db SET HADR AVAILABILITY …

Read More “Exam 70-458 – Objective 3 – Implement high availability”

Exam 70-458 – Objective 2 – Implement Security

This entry is part 2 of 8 in the series Exam 70-458

  Manage logins and server roles Configure server security (http://msdn.microsoft.com/en-us/library/bb283235.aspx) Secure the SQL Server using Windows Account/SQL Server accounts, server roles Server-Level Roles (http://msdn.microsoft.com/en-us/library/ms188659.aspx) Create log in accounts (http://msdn.microsoft.com/en-us/library/aa337562.aspx) Manage access to the server, SQL Server instance, and databases Database Roles (http://msdn.microsoft.com/en-us/library/ms189121.aspx) Create and maintain user-defined server roles (http://msdn.microsoft.com/en-us/library/ee677627.aspx) Manage certificate logins CREATE LOGIN Example …

Read More “Exam 70-458 – Objective 2 – Implement Security”

Exam 70-458 – Objective 1 – Manage Data

This entry is part 1 of 8 in the series Exam 70-458

  Configure and maintain a backup strategy Manage different backup models, including point in time recovery Recovery models (http://technet.microsoft.com/en-us/library/ms189275.aspx) FULL SIMPLE – No point in time recovery BULK-LOGGED – Can recover to last backup, no point in time recovery Should take transaction log backup before and after Protect customer data even if backup media is …

Read More “Exam 70-458 – Objective 1 – Manage Data”

Exam 70-458 Transition Your MCTS on SQL Server 2008 to MCSA: SQL Server 2012, Part 2 Study Resources

I passed the 70-457 Transition Your MCTS on SQL Server 2008 to MCSA: SQL Server 2012, Part 1 today.  Next I will be attempting the 70-458, as I study I’ll put my notes and links to relevant materials here.  I hope to take and pass the exam by the end of March 2014. I’ll add links …

Read More “Exam 70-458 Transition Your MCTS on SQL Server 2008 to MCSA: SQL Server 2012, Part 2 Study Resources”

T-SQL Tuesday #44 The Second Chance

This month’s T-SQL Tuesday is brought to us by Bradley Ball (blog | @SQLBalls). Bradley asked us to write about Second Chances.  Giving us a chance for us to talk about things we have learned from mistakes we have made.  Over the years we have all made mistakes or wished we would have done something …

Read More “T-SQL Tuesday #44 The Second Chance”

Query “SQL Server has encountered ? occurrence(s) of I/O requests taking longer than 15 seconds to complete on file” from the Logs

Recently we moved some servers from being physically on local drives to virtual on our SAN.  We started getting Error 833 messages every night during Integrity Checks.  The more servers we added the more messages we got.  I needed a quick way to retrieve these messages from our error logs and considering we cycle the …

Read More “Query “SQL Server has encountered ? occurrence(s) of I/O requests taking longer than 15 seconds to complete on file” from the Logs”

Automation of PAL (Performance Analysis of Logs) Tool for SQL Server Using Powershell – Part 3

This entry is part 3 of 3 in the series Automation of PAL

In the final part of the process will be the process that creates all the reports and zips up the performance counter file on a central server.    The first part copies the PerfMon file from the each server to a central location.  After the copy is complete and loops back through each server and …

Read More “Automation of PAL (Performance Analysis of Logs) Tool for SQL Server Using Powershell – Part 3”

Automation of PAL (Performance Analysis of Logs) Tool for SQL Server Using Powershell – Part 2

This entry is part 2 of 3 in the series Automation of PAL

In this part of the process we will setup a process to automatically stop the data collector or counter log on the remote server.  Then  rename the file with a time stamp if it is an older version of Windows.  Finally we will start the data collector or counter log on the remote servers for …

Read More “Automation of PAL (Performance Analysis of Logs) Tool for SQL Server Using Powershell – Part 2”

Automation of PAL (Performance Analysis of Logs) Tool for SQL Server Using Powershell – Part 1

This entry is part 1 of 3 in the series Automation of PAL

I recently was re-exposed to the PAL (Performance Analysis of Logs) Tool available on CodePlex at a wonderful SQLskills class.  I had played with the tool in the past but at the time the server I had for setting things up and automtaing processes was Windows 2000 so I forgot about this tool.  Now I …

Read More “Automation of PAL (Performance Analysis of Logs) Tool for SQL Server Using Powershell – Part 1”