WordPress Installed on IIS 7.5 with SQL 2005 or 2008

Here is a quick layout of instructions in installing WordPress on Windows 2008 R2 IIS 7.5 with SQL 2005 or 2008

Download wp-sqlsrv-2.9.2-beta-0.8.8.zip This is a special version of Wordpress that allows you to connect to a SQL server... or cluster... :) why not?!

Unzip files to C:\inetpub\WPOnSQL

Open IIS Mgr -

Open your Web Platform Installer > Web Platform > Database > Customize > Microsoft Drivers for PHP for SQL Server 2.0. Install this thing before you create your site.

Create new site

Enter Site Information
Website:  New Web Site
WordPress application name:  /
Web Site Name: blog.boneyard.org
Physical Path:  c:\inetpub\blog.boneyard.org
IP Address:  All Unassigned
Host Name:  wponsql.boneyard.org
Click OK

****folder permissions of Modify, Read & Execute, List Folders, Read and Write on the c:\inetpub\SiteName folder to ServerName\IUSR

Go to your SQL box, open SQL Mgmnt studio and create a new database. Then create a SQL user that has dbo privs on that database. Then from your IIS, you can test your connection using ODBC manager.

Open site in a browser

The wp-config file will be missing, so let the site walk you through providing it with the database servername, database, and sql credentials

Create Configuration File > Lets Go >

Database Name: TEST

Username: sqlgod

Database Host: databaseservername\instancename

Database Type: SQL Server Using PHP Driver

Table Prefix: wp_

Click OK - the click Run the Install
Blog Title:  Wordpress on SQL Blog
Your E-mail:  admin@boneyard.org
Click Install WordPress

Login to Wordpress

Logout and do the following

http://blog.obxbill.com/?p=582

Click Dashboard and then Updates

An updated version of WordPress is available.
Click Update Automatically
After you see "WordPress updated successfully", click Go to Dashboard

SQL Server 2008 R2 - 2 node cluster build

SQL 2008 R2 on Windows Server 2008 R2 Clustered install

On both nodes

- need .NET 3.5 SP1 use the Role Management Tool

- Add Role - Application Server

Run the setup.exe - SQL Server Installation Center

- Click Installation on left > New SQL Server Failover installation

- Setup suport Rules - click OK

- Prod Key - click next

- EULA - accept - next

- Click Install

- Setup support Rules - will tell you more about what else you might want to install for this to work.

-  - MSDTC - not always needed for cluster. install if you want.

-  - MSCS - cluster validation report failure - http://support.microsoft.com/kb/953748 - if you havent validated your cluster, you get an error when trying to install - go to Failover Cluster manager and click Validate this cluster.

- prod key - click next

- EULA - accept - next

- Feature Selection - Database Services, Analysis Services, Reporting Services - next

-

Instance Config

- SQLServer Network Name - obxSQL0801

- Named Instance - SQL1

- next

Disk Space Reqs - you will need at least 4 or 5 gbs of local disk space if you're going to install the services I listed above.  I had to go back and shut down my vms and cut a D drive (thin-vhd) of 20GB on each of my nodes. then you can install all your SQL bits on the new D drive instead of on the OS drive.

- instance root directory - d:\

- next

Cluster Resource Group

- SQL Server cluster resource group name - SQL Server (SQL1)

- next

Cluster Disk Selection

- select both of your cluster disks for data and logs

- next

Cluster Network Configuration

- uncheck DHCP and put in an available non-DHCP address

- next

Cluster Security Policy

- select Use service SIDs (recommended)

- next

Server Config

- create a service account in AD obxClusterService

- use obxbill\obxClusterService

- next

Database Eng Config

- select mixed mode

- specify your sa password

- click add current user

- click Data Directories tab, make sure you're using the correct drive for the type of db file - i.e. R:\Data and L:\Logs and R:\Backup, etc, etc

- next

Analysys Services Config

- click Add current user

- click Data Directories tab, i.e. R:\OLAP\Data, L:\OLAP\Log, R:\OLAP\Temp and R:\OLAP\Backup

- next

Reporting Services Config

- install but do not configure

- next

Error Reporting

- next

Cluster Installation Rules

- all passed - hit next

Ready to Install

- click install, go have a drink and a smoke now.

- maybe call your mom to let them know you passed the CISSP Exam...

- think about what to have for dinner

- go water some houseplants

- decide that a potbelly samich sounds easy enough

Complete

- close

now go to the obxNode04 and start the SQL install just like you did here

- setup.exe - Installation > Add node

Setup Support Rules

- all passed - ok

Prod Key - next

EULA - accept - next

Setup support files - install

Setup Support Rules

- all passed - next

Cluster node config

- select your SQL1 instance

- next

Service Accounts

- enter the password for the service acct you created earlier

- next

Error Reporting

- next

Add Node Rules

- passed

- next

Ready to Add node

- click install

Add Node Progress

- if you havent gone to go get dinner by now.  It's probably a good time. :)

- next

Complete

- success! - close

okie dokie - test your failover cluster by moving the SQL1 application, etc, etc and now go eat sandwich and watch Jeopardy or somethin.

Stop whining list

1. FreeNas...


2. Windows 2008 R2 2 node Cluster build


3. IIS 7.5
   
   
   
   1. php.iis.net
   
   
   2. WordPress 3.0 Post Setup
   
   
   3. mySQL - with phpmyadmin
   
   
   
   


4. SQL 2008 R2 Enterprise cluster build
   
   
   
   1. Majority node set with cluster, witness server
   
   
   2. Shared disk with quorum from iSCSI Target from FreeNAS
   
   
   
   


5. Self-Signed certs (SSL)


6. ISA - 443 to 80


7. RDP default port add/change - entry on http://d.patrickoneill.com/?p=83


8. MOSS 2007 to MOSS 2010 upgrade/migrate


9. get patches for Mom's computer :)

To put the entire SQL server into single user mode

This is for SQL2005, for SQL 2008, you will need to change the directory where the sqlservr.exe file is located.

Open Cluster Admin - stop SQL services

open cmd prompt and type (or copy/paste) the following:

cd "C:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\Binn"

sqlservr.exe /s SQL1 /c /m /T3608

- you will see a bunch of stuff fly by on the cmd window, this is normal

start SQL Management Studio

Click CANCEL! on the connection prompt

Click New Query - then select your database from the drop list (it's right under the New Query button)

Paste in your DB reindex t-SQL script in the query window and execute

Set a database into Single User Mode

ALTER DATABASE [URSTUPIDDATABASE] SET MULTI_USER WITH NO_WAIT

ALTER DATABASE [URSTUPIDDATABASE] SET SINGLE_USER WITH NO_WAIT

or

EXEC sp_dboption 'URSTUPIDDATABASE', 'single user', 'false'

EXEC sp_dboption 'URSTUPIDDATABASE', 'single user', 'true'

Moving the System databases for SQL 2005 on a cluster

Background

Best practices for SQL configuration recommend moving system databases, particularly the TempDB, to a separate drive.  On a cluster, this drive must be a shared cluster resource that is, like all drives supporting the SQL instance, a dependent cluster resource.  While there is no need to separate out the data and log files for system databases, the following instructions will assist in moving system files together to a separate clustered drive.  In the examples below, assume E:\ is a clustered drive resource which can be replaced with whatever specific drive is designed for each particular instance.

1.0 Moving Model Database Files

1. In Cluster Admin, shut down SQL Server and SQL component cluster resources.


2. Navigate to a command prompt on the active node and start the SQL server using startup parameters:

"C:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\Binn\sqlservr.exe" –s<instance name> –c –m –T3608

NOTE:  Cluster resources for drives, virtual server, and IP address must be online. Also, for more information on the startup parameters, see http://technet.microsoft.com/en-us/library/ms190737.aspx and http://technet.microsoft.com/en-us/library/ms188396.aspx

1. Ensure the SQL Browser has been restarted.


2. Open SQL Server Management Studio but cancel the login dialog


3. Close the Object Explorer window.


4. Click on New Query and log in as Admin (i.e., ADMIN:ServerName\InstanceName) using sa account (assuming the sa account is system administrator account).


5. Use the following query:

use master
go
sp_detach_db 'model'
go

1. Move the Model.mdf and Modellog.ldf files from the current location to the desired location.


2. Reattach the model database by using the following commands:

use master
go
sp_attach_db 'model','E \model.mdf','E:\Sqldata\modellog.ldf'
go

(NOTE:  Replace the filepath above with the new location of the files.)

10.  Close SQL Server Management Studio

11.  Shut down SQL by typing Ctrl + C at the command prompt where you started SQL.

12.  Bring SQL Resource online in Cluster Administrator.

13.  Open SQL Server Management Studio (you should be able to log in normally as you are no longer in single-user mode).

14.  Verify the location of the model files by running the following query:

use model
go
sp_helpfile
go

2.0 Moving MSDB Database Files

1. In Cluster Admin, shut down SQL Server and SQL component cluster resources.


2. Navigate to a command prompt on the active node and start the SQL server using startup parameters:

"C:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\Binn\sqlservr.exe" –s<instance name> –c –m –T3608

NOTE: Cluster resources for drives, virtual server, and IP address must be online.

1. Ensure the SQL Browser has been restarted.


2. Open SQL Server Management Studio but cancel the login dialog


3. Close the Object Explorer window.


4. Click on New Query and log in as Admin (i.e., ADMIN:ServerName\InstanceName) using sa account (assuming the sa account is system administrator).


5. Use the following query:

use master
go
sp_detach_db 'msdb'
go

1. Move the Model.mdf and Modellog.ldf files from the current location to the desired location.


2. Reattach the model database by using the following commands:

use master
go
sp_attach_db 'model','E \msdbdata.mdf','E:\Sqldata\msdblog.ldf'
go

(NOTE:  Replace the filepath above with the new location of the files.)

10.  Close SQL Server Management Studio

11.  Shut down SQL by typing Ctrl + C at the command prompt where you started SQL.

12.  Bring SQL Resource online in Cluster Administrator.

13.  Open SQL Server Management Studio (you should be able to log in normally as you are no longer in single-user mode).

14.  Verify the location of the model files by running the following query:

use msdb
go
sp_helpfile
go

3.0 Moving Master Database Files

1. From the Start menu, point to All Programs, point to Microsoft SQL Server, point to Configuration Tools, and then click SQL Server Configuration Manager


2. In the SQL Server Services node, right-click the instance of SQL Server (for example, SQL Server [MSSQLSERVER]) and select Properties


3. In the SQL Server (instance_name) Properties dialog box, click the Advanced tab.


4. Edit the Startup Parameters values to point to the planned location for the master database data and log files, and click OK.  Moving the error log file is optional.


5. The parameter value for the data file must follow the -d parameter and the value for the log file must follow the -l parameter.  The following example shows the parameter values for the default location of the master data and log files.

-dC:\Program Files\Microsoft SQL Server\MSSQL10.MSSQLSERVER\MSSQL\DATA\master.mdf;-eC:\Program Files\Microsoft SQL Server\MSSQL10.MSSQLSERVER\MSSQL\LOG\ERRORLOG;-lC:\Program Files\Microsoft SQL Server\MSSQL10.MSSQLSERVER\MSSQL\DATA\mastlog.ldf

1. If the planned relocation for the master data and log files is E:\SQLData, the parameter values would be changed as follows:

-dE:\SQLData\master.mdf;-eC:\Program Files\Microsoft SQL Server\MSSQL10.MSSQLSERVER\MSSQL\LOG\ERRORLOG;-lE:\SQLData\mastlog.ldf

1. Stop the instance of SQL Server by right-clicking the instance name and selecting Stop


2. Move the master.mdf and mastlog.ldf files to the new location.


3. Restart the instance of SQL Server

10.  Verify the file change for the master database by running the following query:

SELECT name, physical_name AS CurrentLocation, state_desc
FROM sys.master_files
WHERE database_id = DB_ID('master');
GO

4.0  Moving TempDB Database Files

1. Determine the logical file names of the tempdb database and their current location on the disk:

SELECT name, physical_name AS CurrentLocation
FROM sys.master_files
WHERE database_id = DB_ID(N'tempdb');
GO

1. Change the location of each file by using ALTER DATABASE

USE master;
GO
ALTER DATABASE tempdb
MODIFY FILE (NAME = tempdev, FILENAME = 'E:\SQLData\tempdb.mdf');
GO
ALTER DATABASE tempdb
MODIFY FILE (NAME = templog, FILENAME = 'E:\SQLLog\templog.ldf');
GO

1. Stop and restart the instance of SQL Server


2. Verify the file change:

SELECT name, physical_name AS CurrentLocation, state_desc
FROM sys.master_files
WHERE database_id = DB_ID(N'tempdb');

1. Delete the tempdb.mdf and templog.ldf files from the original location.


2. Add additional tempDB files:

ALTER DATABASE tempdb
ADD FILE (NAME = tempdev2, FILENAME = 'X:\tempdb2.mdf', SIZE = 256);
ALTER DATABASE tempdb
ADD FILE (NAME = tempdev3, FILENAME = 'X:\tempdb3.mdf', SIZE = 256);
ALTER DATABASE tempdb
ADD FILE (NAME = tempdev4, FILENAME = 'X:\tempdb4.mdf', SIZE = 256);
GO

NOTE: Replace the path and filename with appropriate target names.

Add as many files as there are processors per MS Best Practices.  The final number is discretionary based on the demand model for the system.

Restart the server and verify the functionality and location of tempDB:

use tempdb
go
sp_helpfile
go

Note: don't forget to delete the old tempdb MDF and LDF files. SQL won't do it...

Updating the TEMPDB

Here's a quick/dirty little script to update your tempdb for SQL to 5GB for the tempdev (tempdb.mdf) file and 512MB for the templog (templog.ldf) file while at the same time turning off Auto Growth.

USE [master]

GO

ALTER DATABASE

[tempdb]

MODIFY FILE

(NAME = N'tempdev', SIZE = 5242880KB , FILEGROWTH = 0)

GO

ALTER DATABASE

[tempdb]

MODIFY FILE

(NAME = N'templog', SIZE = 524288KB , FILEGROWTH = 0)

GO

The restart your SQL Server Agent and SQL Server Services. Mine is on a cluster with the instance named SQL1.  If you are running on an MS Cluster, then you should probably do this via the Cluster Administrator console because even though the services may be online, the cluster admin console may report them to be in a failed state.

net stop SQLAgent$SQL1 & net stop MSSQL$SQL1

net start MSSQL$SQL1 & net start SQLAgent$SQL1

And yes, you can run a single command with that little ampersand (&) thingy there. :)

Here's an example of setting a TempDB with the following:

mdf file at 15GB, max size of 17.5GB, and growth at 10%

ldf file at 1.5GB, max size of 1.8 GB, and growth set to 10%

USE [master]
GO
ALTER DATABASE [tempdb]
MODIFY FILE ( NAME = N'tempdev',
SIZE = 15728640KB ,
MAXSIZE = 18350080KB,
FILEGROWTH = 10% )
GO
ALTER DATABASE [tempdb]
MODIFY FILE ( NAME = N'templog',
SIZE = 1572864KB ,
MAXSIZE = 1835008KB,
FILEGROWTH = 10% )
GO

Special note: If you copy/paste the code from the block-quotes above, and then try to run them in SQL Management Studio, you might notice them failing.  Try updating the single quotes from  around the file names, tempdev and templog.

Installing SQL 2008 in a 2 node cluster

Woo Hoo! :)

Below are the basic steps from SQL 2005,  as I perform the actual installation for my new SQL 2008 cluster, I'll replace the steps and stuff :)

Here's a link to the SQL 2008 cluster installation:
******************************************************

Prerequisites for SQL 2008 cluster installation:

1. Add\Remove Programs > Windows Components > Application Server


2. select - Enable asp.NET


3. select - Enable network COM+ access


4. select - Enable network DTC access


5. select - Internet Information Services (IIS)


6. Start > Run > Services.msc


7. Distributed Transaction Coordinator - set to Manual


8. HTTP SSL - set to Automatic


9. Start > Run > cmd > iisreset


10. Install Windows 2003 Server SP1


11. Install configure .NET Framework 3.5

6.1 Default Instance SQL Installation
1. On the SQL Server 2005 Disk1 folder, run Setup.exe to begin the installation.
2. At the End User License Agreement page, accept the license and click Next.
3. At the Installing Prerequisites page when it says Click Install to continue, click Install and when done click Finish.
4. At the Welcome to the Microsoft SQL Server Installation Wizard, click Next.
5. At the System Configuration Check page after the check completes, verify there are no errors and click Next.
6. At the Registration Information page, enter your name and company name click Next.
7. For Components to Install, select SQL Server Database Services, Create a SQL Server failover cluster, Notification Services, Integration Services and Workstation Components…
- Click Advanced.
- Expand Client Components.
- Select to have Notifications Services, Integration Services, and Client Components installed completely to the D:\ drive and click Next.
11. Once each component has been specified to install to the D:\ drive of the server, click Next.
12. For the instance name of the first instance, leave it set to Default instance and click Next.
13. For the Virtual Server Name – enter the name of your virtual Server name for the default instance of SQL on the MS Cluster and click Next.
14. For Virtual Server Configuration, Network to use, enter Primary For the IP Address, enter xxx.xxx.xxx.xxx and for the Network subnet, enter 255.255.255.0 , click Add, and then click Next.

15. For Cluster Group Selection, select the group that contains the drive letter(s) you configured for your SQL data files and click Next.
16. For Cluster Node Configuration, add the available nodes to the selected nodes and click Next.
17. For Remote Account information, enter credentials for a user that has administrator privileges for the system and click Next.
18. For Service Account, enter the credentials for the SQL Server Service Account and click Next.
19. For the Domain Groups for Cluster Services enter your AD Service Accounts Group click Next.
20. For Authentication Mode, select Mixed Mode enter a strond password and click Next.

21. At the Collation Settings page, leave the default settings and click Next.
22. At the Error and Usage Settings, leave the default settings and click Next.
23. At the Ready to Install page, click Install
24. You can view the progress of the installation by selecting/changing the Node on the Setup Progress window.  Once setup has completed, click Next.
25. At the Completing Microsoft SQL Server 2005 Setup page, click Finish.

See the number of SQL connections to a database

SELECT db_name(dbid) as DatabaseName, count(dbid) as NoOfConnections,
loginame as LoginName
FROM sys.sysprocesses
WHERE dbid > 0
GROUP BY dbid, loginame

Change the location of the TEMPDB

Use the ALTER DATABASE statement, specifying the logical file name as follows:

use master
go
Alter database tempdb modify file (name = tempdev, filename = 'E:\Sqldata\tempdb.mdf')
go
Alter database tempdb modify file (name = templog, filename = 'E:\Sqldata\templog.ldf')
go

You should receive the following messages that confirm the change:
Message 1
File 'tempdev' modified in sysaltfiles. Delete old file after restarting SQL Server.
Message 2
File 'templog' modified in sysaltfiles. Delete old file after restarting SQL Server.
Using sp_helpfile in tempdb will not confirm these changes until you restart SQL Server.
Stop and then restart SQL Server

Antigen: To remove the SybariEnterpriseManagerDist database

First you need to remove the replication publications and subscriptions.

Then you can run the following command:
USE master
GO
EXEC sp_dropdistributor @no_checks = 1