Congratulations to UK Information Commissioner Christopher Graham for Taking Companies to Task About Protecting Data Privacy

The New Information Commissioner for the United Kingdom is Mr. Christopher Graham, he's working from Wilmslow, near Manchester, with a team of hundreds to bring responsibility back to private companies' [mis]management of customer data - and he already took on T-Mobile for a customer sell off by employees debacle that, which of course, they tried to conceal.  He's out to enforce Parliament's Data Protection Act, and he's sending out warrants and private investigators as he goes! Bravo, the Database Cops are coming and they are armed with the DPAJ - too bad QC couldn't follow along and enforce Bill C-198 amongst government institutions.

Hats off to the UK government's efforts to protect data the way it should be and reinforce data stewardship, for more details please see: http://www.computerweekly.com/Articles/2009/11/27/239490/ICO-publishes-guide-to-quash-business-ignorance-on-data.htm

Here's an excerpt from the BBC article, which states that T-Mobile's employees were simply: 'Exploiting data'

The Ministry of Justice has been consulting on tougher penalties for illegal trade in personal information.

The Data Protection Act bans the selling on of data without prior permission from the customer and a fine of £5,000 can be imposed following a successful prosecution."

It is amazing to see such a brave man publically speaking out against things that simply have to be stopped - data protection must be taken more seriously by the business community (as well as the government itself sometimes).  Environments that do not adhere to Auditing Compliance, the principle of least privileges or the Segregation of duties are a clear target for the Information Commissioner to start, and I will let him know personally :)

Please note that I am in transition to a permanent job currently with Canadian printing Giant Transcontinental, and am training on Oracle 11g, Release 2, thus I shall resume more frequent blogging shortly. Final French version of the DRP for SQL Server was submitted last week to Simple Talk also.

 

 

 

Troubleshooting SQL Server Database Mirroring Problems with Best Practices: Requirements, and the 64-bit Benefit

As mentioned in previous disaster recovery posts and article, during my current mandate I have been tasked with what will ultimately be mirroring between redundant data centres. Mirroring has been chosen thanks to its easy setup and automatic failover option. The following is an overview of what should be taken care of to ensure a stable Mirroring setup.

Prerequisites for Mirroring are that you ensure that your database is optimised already, because mirroring an unoptimised database is just double the potential problems (in terms of file space).  In this way, expected disk space growth should be analysed thoroughly.   As for the build level, I am just waiting to apply SQL 2005 sp3, cu6(?) before running a mirrored set up in production. I figure having the same highest-available build level is the best way to start a mirroing infrastructure.

Initially there were rumours that there was a maximum value to the number of databases mirrored, but that turned out to only be applicable to 32-bit systems (which is a platform you probably woudn't wan to have heavily used databases on still anyway), which has a limit of ten. On 64-bit database systems there is no documented limit, therefore instances that hold many databases are without issue, apart from the typical I/O, network, and processor utilisation.  In 2008, there has been an improvment on the compression of the log before it is applied to the mirror, but after testing with three times the average traffic with SQL Stress and RedGate's Data Generator, e.g. pumping three million inserts across with a bunch of large selects on the worst tables, we had only a max wait time to apply the log of only 1.2 seconds as the exception, whilst the Database Mirroring Monitor reported mostly under 200ms.

Start with a general verification that the ports are open on the remote EndPoint by running from the Run dialog box/cmd line:
telnet RemoteServerName 5022

Verifying Port Availability - When you are configuring the network for a database mirroring session, make sure the database mirroring endpoint of each server instance is used by only the database mirroring process. If another process is listening on the port assigned to a database mirroring endpoint, the database mirroring processes of the other server instances cannot connect to the endpoint.

To display all the ports on which a Windows-based server is listening, use the netstat command-prompt utility. YOU can identify the listening ports and the processes that have those ports opened, follow these steps:
1.        Obtain the process ID of the respective instance of SQL Server, connect to that instance and use the following Transact-SQL statement:
SELECT SERVERPROPERTY('ProcessID')
2.        Match the process ID with the output of the following netstat command:
netstat -ano

If ever you arrive with an error like this: Database Mirroring login attempt failed with error: 'Connection handshake failed. There is no compatible encryption algorithm. State 22.'.  [CLIENT: IPaddress...]
This is probably happening because your encryption is setup differently on the mirror/principal. The system table sys.database_mirroring_endpoints will show different algorithms- Mirror-encryption_algorithm_desc=RC4 and Principal- encryption_algorithm_desc=NONE
Therefore it is best to issue a:
Drop Endpoint Mirroring 
command on both mirroring parters (to start from scratch, beware, this blows away all mirroring on the endpoing), so that when you try and set up mirroring again, you are not stopped by a difference regarding encryption.  If DROP ENDPOINT is not possible, meaning you have other database Mirroring established already with another server, and then the other option would have been to run ALTER ENDPOINT on both instances For more information regarding Mirroring, check out the System Tables:

select * from sys.database_mirroring_endpoints

SELECT

e.name, e.protocol_desc, e.type_desc, e.role_desc, e.state_desc, t.port, e.is_encryption_enabled, e.encryption_algorithm_desc, e.connection_auth_desc
FROM sys.database_mirroring_endpoints e JOIN sys.tcp_endpoints t ON e.endpoint_id = t.endpoint_id

select * from sys.endpoints

select * from sys.database_mirroring where mirroring_state is not null

-- which ones are in the mirroring state

 

--to see who has granted Mirroring and the grantee

SELECT

EP.name, SP.STATE,  CONVERT(nvarchar(38), suser_name(SP.grantor_principal_id))  AS GRANTOR, SP.TYPE AS PERMISSION, CONVERT(nvarchar(46),suser_name(SP.grantee_principal_id)) AS GRANTEE
FROM sys.server_permissions SP , sys.endpoints EP
WHERE SP.major_id = EP.endpoint_id
ORDER BY Permission,grantor, grantee;

  I shall be updating this further once I have had success in production. I hope this helps, if you are aiming on taking advantage of  this great functionality

References and Recommendations from MSDN: http://msdn.microsoft.com/en-us/library/ms366349(SQL.90,printer).aspx

A great step by step script-based tutorial: http://www.mssqltips.com/tip.asp?tip=1705

 Dell’s guide to Mirroring