Thursday, June 23, 2011

Organization Culture

The Monkey Story
The experiment involved 5 monkeys, a cage, a banana, a ladder and, crucially, a water hose.

The 5 monkeys would be locked in a cage, after which a banana was hung from the ceiling with, fortunately for the monkeys (or so it seemed…), a ladder placed right underneath it.

Of course, immediately, one of the monkeys would race towards the ladder, intending to climb it and grab the banana. However, as soon as he would start to climb, the sadist (euphemistically called “scientist”) would spray the monkey with ice-cold water. In addition, however, he would also spray the other four monkeys…

When a second monkey was about to climb the ladder, the sadist would, again, spray the monkey with ice-cold water, and apply the same treatment to its four fellow inmates; likewise for the third climber and, if they were particularly persistent (or dumb), the fourth one. Then they would have learned their lesson: they were not going to climb the ladder again – banana or no banana.

In order to gain further pleasure or, I guess, prolong the experiment, the sadist outside the cage would then replace one of the monkeys with a new one. As can be expected, the new guy would spot the banana, think “why don’t these idiots go get it?!” and start climbing the ladder. Then, however, it got interesting: the other four monkeys, familiar with the cold-water treatment, would run towards the new guy – and beat him up. The new guy, blissfully unaware of the cold-water history, would get the message: no climbing up the ladder in this cage – banana or no banana.

When the beast outside the cage would replace a second monkey with a new one, the events would repeat themselves – monkey runs towards the ladder; other monkeys beat him up; new monkey does not attempt to climb again – with one notable detail: the first new monkey, who had never received the cold-water treatment himself (and didn’t even know anything about it), would, with equal vigour and enthusiasm, join in the beating of the new guy on the block.

When the researcher replaced a third monkey, the same thing happened; likewise for the fourth until, eventually, all the monkeys had been replaced and none of the ones in the cage had any experience or knowledge of the cold-water treatment.

Then, a new monkey was introduced into the cage. It ran toward the ladder only to get beaten up by the others. Yet, this monkey turned around and asked “why do you beat me up when I try to get the banana?” The other four monkeys stopped, looked at each other slightly puzzled and, finally, shrugged their shoulders: “Don’t know. But that’s the way we do things around here”…

Wednesday, December 1, 2010

Vacation

OK folks I know its been a couple of months since I have posted anything of interest. I have been busy gathering research material and consulting on various IT projects. Stay tuned I will be adding new material soon !!!!

Sunday, July 11, 2010

Implementing Database Mirroring

Implementing Database Mirroring.
Database Mirroring helps minimize planned and unplanned downtime between databases. It offers high availability (instance-aware) on a per user database by keeping a mirrored replica either at the local or remote site (disaster recovery). It is an attractive choice to Failover Cluster or Log Shipping.

Database Mirroring does not support the Simple or Bulk-logged only recovery model.

Environment-Windows 2008 AD-Integrated. -SQL Server located in the same site and domain. Computer based Certificate enrollment authentication (Enterprise-CA) Adventureworks master key self signed certificate

1. Backup the Principal database (full and transaction log) and restore (no recovery) it on the Mirror.

2. Run DBCC CHECKDB ON each principal

3. Create Database Master key

--Run this on the Principal server
USE master

CREATE MASTER KEY ENCRYPTION BY PASSWORD = 'strongpassword';
GO

4. Create the server based certificate which will be used to encrypt the mirroring endpoints

--Run this on the Principal server
CREATE CERTIFICATE servernameofprincipal_cert WITH SUBJECT = 'servername certificate for database mirroring'
GO

5. Create the Database mirroring endpoint on the Principal using the certificate for authentication.

--Run this on the Principal Server
CREATE ENDPOINT Endpoint_Mirroring STATE = STARTED
AS TCP(LISTENER_PORT = 9999, LISTENER_IP = ALL)
FOR DATABASE_MIRRORING (AUTHENTICATION = CERTIFICATE principal Servername_cert,
ENCRYPTION = REQUIRED ALGORITHM AES, ROLE = ALL);
GO

6. Verify endpoints

select * from sys.endpoints
select * from sys.certificates
select * from sys.database_mirroring_endpoints
select * from sys.database_mirroring_witnesses

7. run netstat -ao to view hostname of established connections as well as ports the server is currently listening on.


8. Create exception or rule in the Firewall for port 1433 default port and endpoint port 9999 on the Principal and the Mirror


9. Export/backup Certificate to a file

--Run this on the Principal server
BACKUP CERTIFICATE servername_cert TO FILE = 'D:\MSSQL10.MSSQLSERVER\MSSQL\DATA\servername_cert.cer';
GO

10. Copy the Certificate to the mirror and witness

Copying the certificate to the mirror and witness allows you to associate a login for authentication.

Repeat steps 3-10 on the mirror and the witness modifying only the certificate name.

11. Associate the certificate we created in Step #4 with a user

--Run this on Principal

CREATE CERTIFICATE server_name_mirror_cert
AUTHORIZATION login
FROM FILE = 'D:\MSSQL10.MSSQLSERVER\MSSQL\DATA\server_name_mirror_cert.cer'
GO

12. Grant the CONNECT permission on the login

--Run this on testServer1

GRANT CONNECT ON ENDPOINT::Endpoint_Mirroring TO [login];
GO

13. Prepare the mirror server for the mirroring session

--Run this on mirror
USE master
GO
ALTER DATABASE Adventureworks SET PARTNER = 'TCP://principal:9999';
GO

14. Prepare the principal server for the mirroring session

--Run this on principal
USE master
GO
ALTER DATABASE Adventureworks SET PARTNER = 'TCP://mirror:9999';
GO

15. Failover test. Failover a Database Mirroring Session

--Run this on principal
Use master
GO
ALTER DATABASE Adventureworks SET PARTNER FAILOVER;

16. To turn on transactional safety (full)
--Connect to the principal
ALTER DATABASE SET PARTNER SAFETY FULL

17 to turn transactional safety offl
--Connect to the principa
ALTER DATABASE SET PARTNER SAFETY OFF



To disable database mirroring

--ALTER DATABASE SET PARTNER OFF

Friday, July 2, 2010

Create Database

When creating databases consider using filegroups for large database that will have large tables. Also consider placing data and log files on separate disk and increase disk spindles in array.


USE master
GO
IF EXISTS (SELECT name FROM sys.databases WHERE name = N'CoreDB')
DROP DATABASE CoreDB
GO
USE master
GO
CREATE DATABASE [CoreDB]
ON PRIMARY
(
NAME = N'CoreDB',
FILENAME = N'D:\Test\CoreDB.mdf' ,
SIZE = 3048KB ,
FILEGROWTH = 0
)
LOG ON
(
NAME = N'CoreDB_log',
FILENAME = N'D:\Test\CoreDB_log.ldf' ,
SIZE = 1024KB ,
FILEGROWTH = 10%
)
GO

Wednesday, June 23, 2010

SQL Server 2008 Copy Database Wizard

Ok I tried to use the copy database wizard and it kept failing so I just decided to use the backup/restore "copy database only" option. Which always works !!!

Friday, June 11, 2010

Mass infection of IIS/ASP sites

Serves you right for letting web dev write shitty code that give direct access to your SQL Database (tables) instead of Stored Procedures...Blame it on rookie web devs and business to cheap to hire a real SQL DBA