Friday, September 28, 2007

I wish this was available years back - SQL Server 2005 Always On Technologies Part 1

In response to requests made after I did a presentation on TechEd Asia 2007 on SQL Server 2005 Always On Technologies, I've made the content available on my blog.

In as much as we want to, there are not as many options in SQL Server 2000 to address disaster recovery caused by human errors. And when we think about disaster recovery and high availability, we are often tempted to think about hardware solutions. With SQL Server 2005, we have a lot of options. This will be a series of blog entries discussion about the high availability options in SQL Server 2005. I would like to highlight, though, that disaster recovery is more a process than technology. We may have the available technologies at our disposal but if we do not have the right process, then, its practically useless. Prevention is one option. Let's take a look at a simple scenario. Imagine a developer accidentally connecting to a production database and dropped a table. Now, if this table has implemented referential integrity and has foreign key relationships established on related tables, the related tables would definitely prohibit the execution of the DROP TABLE command due to referential integrity. But what about this table? Let's say the Employees table is related to the Department table and a referential integrity is defined between them. We won't be able to drop the Employees table because it references the Department table. But nobody is stopping us from dropping the Department table. The best way to prevent this scenario in SQL Server 2005 is to implement DDL(data definition language) triggers. We are all familiar with DML triggers in previous versions of SQL Server. DDL triggers are used to audit and/or prevent DDL statements at the server-level or at the database-level. You can prevent accidental data deletion (i.e., trapping and rolling back an attempt to drop a table) and unexpected schema changes. A benefit of using a DDL trigger is that it forms a part of an implicit transaction which caused the trigger to fire and, when necessary, rollback the statement when necessary. The data related to the event can be trapped even if the statement that caused the trigger to fire has been rolled back. This is beneficial if we want to audit who did what on either database or server level. A complete description of SQL Server 2005 DDL triggers can be found on the MSDN site.

Let me walkthrough a simple example on using a DDL trigger. Using the very familiar Northwind database (the Northwind database does not come with the default installation of SQL Server 2005. You can download the source from the Microsoft Download Center). First, create a table which will contain the metadata of the DDL query. We will use this to store those information for audit purposes

USE Northwind;
go

CREATE TABLE AuditDDLOperations
( OpID int NOT NULL identity CONSTRAINT AuditDDLOperationsPK PRIMARY KEY CLUSTERED,
LoginName sysname NOT NULL,
UserName sysname NOT NULL,
PostTime datetime NOT NULL,
EventType nvarchar(100) NOT NULL,
DDLOp nvarchar(2000) NOT NULL);
GO


Then, we will create a database-level DDL trigger.

CREATE TRIGGER PreventAllDDL
ON DATABASE
WITH ENCRYPTION
FOR DDL_DATABASE_LEVEL_EVENTS
AS DECLARE @data XMLSET
@data = EVENTDATA()
RAISERROR ('DDL Operations are prohibited on this production database. Please contact IT Division for proper change control procedures.', 16, -1)
ROLLBACK
INSERT AuditDDLOperations (LoginName, UserName, PostTime, EventType, DDLOp)

VALUES (SYSTEM_USER, CURRENT_USER, GETDATE(), @data.value('(/EVENT_INSTANCE/EventType)[1]', 'nvarchar(100)'), @data.value('(/EVENT_INSTANCE/TSQLCommand)[1]', 'nvarchar(2000)') )
RETURN;
GO

The CREATE TRIGGER statement consists of (1) the scope, which in this case is a database-level scope, (2) WITH ENCRYPTION option to simply hide the trigger definition, (3) the event on which the trigger will be associated with, this case will be all DDL events at the database level, (4) the EVENTDATA() function which returns information about server or database events and is called when an event notification fires, and (5) an INSERT statement to store the details of the EVENTDATA() function inside the table we created in the previous script. Now, to test the trigger, simple run any DDL statement on the Northwind database

CREATE TABLE Foo(col1 int);
go
DROP TABLE AuditDDLOperations;
GO

The previous statements are very simple DDL statements - one to create a table and one to drop a table. These statements will raise an error defined in the DDL trigger. To investigate the DDL statements and who executed them, we run a SELECT query against the table we created

SELECT * FROM AuditDDLOperations;
GO


The key thing here is implementing DDL triggers as part of your database implementation strategy to minimize disaster caused by human errors. In the next part of this series, I'll talk about how you can recover from a dropped database in cases where you don't have a DDL trigger defined in your database

Thursday, September 27, 2007

Classic Active Server Pages Allowed? I have ASP.NET 2.0

During my installation of System Center Configuration Manager 2007, I intentionally had the Active Server Pages setting to Prohibited in IIS 6.0. This is because I always limit my configuration to only those that I use. Since I assumed that .NET 2.0 is required to install SCCM 2007, I was assuming that it is using ASP.NET 2.0 in the reports. To my surprise, when I launched the Virtual Machine provided for the online virtual hands-onlabs, the URL contains a .asp?some_value. Being a part-time developer as I am, my instincts tell me that I need to Allow Active Server Pages on my IIS for this feature to work. I just don't understand why Microsoft opted for classic ASP when they already have a rich-feature set available from ASP.NET 2.0 to use for generating those reports.

Installing SCCM 2007 Clients using Software Update Point

After installing System Center Configuration Manager 2007, we need to deploy the SCCM client. There are a lot of ways to deploy the SCCM client but I will be focusing more on using the Software Update Point as I have been using Windows Server Update Services (WSUS) for patch management. The first thing you need is to make sure that you already have a WSUS 3.0 in your infrastructure as you will be using this as your Software Update Point. The nice thing about this approach is that you already have your infrastructure set for software update management.

  1. Install Software Update Point.
    You need to install the Configuration Manager Software Update Point Site Role on top of your WSUS 3.0. This could also be on another machine which points to a remote WSUS 3.0. If you are going to install this on a server separate from your WSUS 3.0 server, you need to have the WSUS 3.0 admin console prior to installing the software update point. If you want to use your primary site server as your software update point as well, you just need to add a new role and define it as a Software Update Point. The typical configurations for WSUS 3.0 will be used for this configuration, such as if you are using a proxy server to connect to Microsoft Updates, whether you are using an upstream WSUS 3.0 server, etc. You can also enable a synchronization schedule. A recommended schedule for this is on a weekly basis and after patch Tuesday (Tuesday afternoon my time). In my case, I do manual synchronization and run once after patch Tuesday or anytme I get an email alert from Microsoft for Critical Security updates. You also define the classification - critical updates, service packs, etc. - the same way you do in WSUS. Then you specify the products which you need to configure the updates for. Since you have Microsoft as the vendor by default, you can select which products are installed within your enterprise - Office 2003, SQL Server, Windows, etc. Then, you specify the different languages you need for those updates.
    Note that the steps are similar to that of the WSUS 3.0 configuration. If you have configured yor WSUS 3.0 prior to deploying your software update point, those will be overwritten by your new configuration.
  2. Validate your configuration in the Software Update Point Component
    Any configuration you've made in setting up your software update point can be validated in the Software Update Point Component under the Component Configuration. So, if you need to do some modifications in the long run, this is the right place to do it.
  3. Configure the Software Update Point Client Installation
    At this point, we still have to deploy the SCCM client in order to use our software update point for patch management. Under the Client Installation Methods, make sure that Software Update Point Client Installation is enabled. This is to publish the SCCM client to WSUS 3.0 as a mandatory update. Together with this, the appropriate BITS component will be downloaded by the client as well.
  4. Configure the Software Update Client Agents
    Although we haven't really installed the SCCM clients at this point, we can already configure how our clients will behave like enforcing all mandatory deployments and deployment re-evaluation
  5. Configure a Group Policy for Windows Update
    Similar to how we configure a group policy to point clients to download updates from a WSUS server, we need to do the same. If you already have this in place, you can skip this portion. For a more detailed description on how to do this, check out this Microsoft TechNet documentation. Make sure that you treat servers and workstations differently so you definitely need separate GPOs for these.
  6. Import the SCCM 2007 ADM Template
    I got this from Kim Oppalfen's (Microsoft MVP for Software Distribution) blog so all credit goes to him on the ADM template and the process on how to do this. Just make sure you specify the parameters needed for this. In my case, I just used the SMSSITECODE=value parameter.
    Now, we're ready to deploy the SCCM client and our Software Update Point has been configured as well. It's like hitting two birds with one stone. The best way to test whether our configuration is to log in to one of the machines in your domain and run a group policy update (gpupdate /force for Windows XP and Windows Server 2003 or secedit /refreshpolicy machine_policy /enforce for Windows 2000) and manually run a force detect of the Windows Update client (wuauclt /detectnow) If you open your Task Manager, you will see ccmsetup.exe in the Image name under the Processes tab. Another way to find out if the SCCM client is being deployed thru WSUS 3.0 is to look at the WindowsUpdate.log file which contains information regarding the installation of Configuration Manager Client

Wednesday, September 26, 2007

Installing System Center Configuration Manager 2007

I am building my image to test System Center Configuration Manager 2007 (SCCM). SCCM 2007 is the next generation SMS 2003 and is currently on RC1. Since I was responsible for maintaining the WSUS server in our infrastructure, I decided to take a peek at what SCCM 2007 has got to do with making my life easier with patch management. I've listed down a few things I did to prepare for SCCM 2007 installation.

  1. Windows Server 2003 SP1, SP2 or R2
    Since I was doing a fresh installation, I chose Windows Server 2003 and installed SP2, although you can do this on a Windows Server 2008 as well. This will act as my domain controller, my database server, my WSUS 3.0 server and my SCCM 2007 server. In a typical setup, you would want to offload your SCCM 2007 and have a separate WSUS 3.0 server and database server (I am assuming that you do not want to run anything on your domain controller machine aside from AD).
  2. IIS 6.0
    You need to install IIS 6.0 if you want to take advantage of BITS technology for clients on low bandwidth connection. There are a lot of reasons for using IIS and this is just one of them. Make sure to enable WebDAV and install BITS Extensions for IIS. I've learned this the hard way as my SCCM 2007 installation was not making any progress because of this. Since I was concerned about security, I did not install those components which I don't need (ASP.NET, SMTP, FTP, NNTP, etc.) The ASP.NET version which I need is v2.0. The one which comes with Windows Server 2003 is v1.1. Another reason I am installing this first before any ASP.NET 2.0 component is that I no longer have to do anything related to ASP.NET 2.0 later on (like running aspnet_regiis.exe -i to install ASP.NET v2 on IIS). We just need to allow ASP.NET 2.0 later on in IIS after installing SQL Server 2005
  3. SQL Server 2005 with SP2
    This will be my database server. Since SQL Server 2005 comes with .NET Framework 2.0, this takes care of my ASP.NET 2.0. Now since I will also host my WSUS 3.0 server on this machine, I can use this as the database server as well. Most of the time, I would work on different instances to identify which one is for what function since SCCM 2007 and WSUS 3.0 would require a database server. For this particular setup, I will just install one instance which will be used by both WSUS 3.0 and SCCM 2007. This makes management a lot easier for me. SP2 is definitely a must for SCCM 2007.
    After SQL Server 2005 has been setup, ASP.NET 2.0 needs to be allowed in IIS
    If SQL Server 2005 will be on a different machine, you need to set the Service Principal Name (SPN) as well. This is discussed in detail in this
    Microsoft KB article
  4. MMC 3.0
    This will be required by both WSUS 3.0 and SCCM 2007. MMC 3.0 requires .NET Framework 2.0 which was already installed because of SQL Server 2005
  5. BITS 2.5
    This is a new download available since June 26, 2007. It's a required component for SCCM 2007 and Windows Live OneCare (which I don't really need). There are a lot of versions for this but the one I installed is the one for Windows Server 2003. We are definitely going to need the Windows XP version as well for client management
  6. WSUS 3.0
    Since I will be doing patch management with SCCM 2007, I definitely need WSUS 3.0. WSUS 3.0 is required to setup a Software Update Point. This is required for every primary site server that is managing software updates. SCCM 2007 is now tightly integrated with WSUS 3.0 for patch management. WSUS 3.0 requires MMC 3.0 and .NET Framework 2.0 which has already taken cared of
  7. Run extadsch.exe
    Similar to what you do in SMS 2003, you need to extend your Active Directory schema. You definitely need schema admins permission on your AD to do this
  8. Give the SCCM 2007 machine Full Control permissions on the System container in your Active Diectory
    This procedure will allow your SCCM 2007 machine to create the Systems management container and its necessary objects. Since by default, the System container is not shown, you have to enable the Advance Options in your Active Directory Users and Computers
  9. Install System Center Configuration Manager 2007
    Once you reach the system checker portion of the installation, it will give you some information on whether or not you can proceed with the installation. This was my hint that BITS Extensions for IIS was not installed
  10. Configure your Site Boundaries
    In order for your clients to be able to find your management point with the help of Active Directory(and vice versa), you have to define your Site Boundary. Under Boundaries, create a new boundary. You can specify whether your boundary type will be an Active Directory site, an IP subnet, an IP Address Range or an IPv6 prefix. If you select an Active Directory site, you can browse thru your AD sites and read the information from there, taking advantage of your existing AD configuration.
  11. Configure the Discovery Method
    If you will be using Active Directory as your discovery method, you need to configure this as well. Under the Discover Methods, modify the proerties of the Active Directory System Discovery. Make sure to enable Active Directory System Discovery. You can also modify the Polling Schedule but for the purpose of testing, you can check the Run discovery as soon as possible checkbox so you can see later on when you start deploying your clients whether or not it is working.


It took me a couple of days to finish my installation as I still had to configure my WSUS 3.0 to download the patches I need. September security patches from Microsoft will be the next in the queue

Making a fool out of MSDB

I was restoring a SQL Server instance on a different server for DR purposes including system databases. What I have overlooked was the fact that restoring the msdb database would mean keeping the existing settings of the old instance into the new one. While I was trying to delete the database maintenance plans and the jobs, I keep getting an MSX-related error which prevents me from deleting the jobs. I looked at the jobs by running the sp_help_job system stored procedure and found out that the originating_server column happens to be the name of my old SQL Server instance. This was the primary reason why I could not delete the jobs either from Enterprise Manager or running the sp_delete_job system stored procedure. To workaround that issue, I simply modified the originating_server column of the sysjobs table to the name of the current instance. After that, I was able to delete the database maintenance plans and the jobs. Now, my server is ready for DR. Log shipping configuration is the next thing to do.
Google