The Database Hive for SQL Server DBAs

1.5 Years in, U.K. Information Commissioner Christopher Graham, Delivers on the Data Protection Act

2010-12-17T10:46:00.000-08:00

A year after the Telegraph reported that Information Commissioner Graham would pledge a fierce approach to the stonewalling approach of public officials, and enforce the Data Protection Act, he has surely held his word. Two fines have been delivered, the first to the Hertfordshire County Council, for approximately $200,000 (£100 000,), regarding a facsimile of sensitive information, in fact, child sex abuse litigation, to the incorrect recipients – not only once, but twice! Christopher Graham took the right course of action to slam those who were so sloppy.

The second fine was for around $120,000 (£60 000) was dealt to an employment services company regarding the loss of a laptop (unencrypted disk, of course), which stored a 24,000 person database of those seeking legal advice in Hull and Leicester. "These first monetary penalties send a strong message to all organisations handling personal information. Get it wrong and you do substantial harm to individuals and the reputation of your business. You could also be fined up to half a million pounds", stated the Commissioner.

http://i.telegraph.co.uk/telegraph/multimedia/archive/01546/ChristopherGraham_1546629c.jpg

I blogged about last years’ Information Commissioner Office crackdown on T-Mobile, but companies are not the only target of enforcement, so are public officials, here are some of his quotes: "Freedom of Information has been 'live' for five years and some public authorities are better at addressing openness than others. But some are dragging their feet and my job is to get the slowcoaches to speed up and to realise my office will be on to them if they don't get a move on."

"The Information Commissioner has a key role at the moment. I have responsibility for encouraging the highest standards of data protection and of transparency by public bodies. I am in the business of information rights. It's a role that is only going to get more and more important," Graham stated last year to the Telegraph.

Regarding the long term goals of the ICO: "I want to run an effective operation. I don't want to be remembered for fine speeches and interesting essays. I want to have got things done. I want to ensure that people's rights under the Data Protection Act and Freedom of Information Act work. I want to ensure that problems are resolved in a timely way because justice delayed is justice denied.”

I have posted this information to a Facebook Page critical of Canadian Prime Minster Stephen Harper, as well as reminded Liberal Party of Canada Member of Parliament Justin Trudeau that there should to be a Canadian equivalent, and not just Auditor General Fraser performing a dual role of scorn for abuse by the Government itself.

Bravo once again to Christopher Graham for taking head on those who wish to thrive in anti-democratic stagecoaching.
The next step is for the amendment to the Freedom of Information Act to pass through Parliament - this will allow him to be able to over-rule the Justice Secretary Jack Straw who, by default, conceals the minutes of Cabinet meetings, whereas it should be the opposite.

Notes from Mirroring Deep Dive Session with Mohammed Sharaf: Microsoft Premier Engineer (I can never learn enough about Mirroring :)

2010-12-17T10:43:00.000-08:00

One thing I have gotten wrong before, or not elaborated on during a past presentation on mirroring, although Roman Rehak mentioned it in the Burlington, Vermont PASS chapter meeting back in May, is that we can use Reporting Services on the Mirrored copy, as long as you are Enterprise edition and Snapshots are enabled.

Ports used in Mirroring, Clarification
Mirroring uses TCP port 5022 by default, and then goes up from there to 5023 for the next Mirror Security Setup, per instance on the same server installation.
This port 502# is used for mirroring is the dedicated connection pool, and the Database Engine is responsible for the communication between Mirror Partners. To troubleshoot port issues, please see a previous post on mirroring.

You cannot use a local account for mirroring, but you can use certificates, with symmetric / asymmetric encryption (example below, after regular AD setup). Asymmetric encryption is the generation of a key pair – and you need the pair together, otherwise the data will not be useable – this is how SSL on both sides (general explanation). The private key is often sent over the network with the public, and used on the receiving end to decrypt. These keys can be used for authentication, as an alternative to a domain service account.

General Mirroring Notes:

It is recommended that a dedicated NIC is exploited for mirroring, but this is definitely an optional performance advantage. If you can reserve NICs for administrative access that is great also, but we do not always have these options at our disposal.

The Filestream feature is not supported in Mirroring, so if you are using this functionality, it may not be a good idea to continue its use if you require a High Availability solution.

The limitations of Standard Edition mean no snapshots, synchronous only, and redos single-threaded (slower).

There is no real point in having a witness server if you are using high performance mode!

You will have to pay in cost to the application being slow if you are in auto-failover mode because the 30-45 seconds for the commit to be dual, for example,

Auto failover requires high safety/synchronous and a witness: thus you cannot benefit from auto failover without these two conditions.

You can set permissions on each - first see
select * from sys.endpoints
Mirroring will be in the result set, or whatever name you use set your Instance-level mirroring session to.
Then you can decide to:
grant / deny connection to TSQL::NamedPipes
– or whatever connection type you do/don’t want.

For applications that connect to Mirrored Databases, please see my previous post on Failover Partner Connection String Setup.

Versions: you can use SQL 2008 and R2 and Express all together (hybrid environment) in a mirror quorum.

To switch easily between servers with your Mirroring Scripts:
If you want to switch between servers during a script (much easier this way), make sure you have setup Aliases within the configuration tools/manager (if readability is your desire) so you can simply do this:

:Connect ServerName

Script for that Server

GO -- must end the batch

:Connect OtherServerName

Run script for that Server

The Prerequisite : Query Execution By default, SQL Server options – use by default SQLCMD mode.

There is only one Mirroring endpoint per instance: we called ours IT_Mirror since it describes which department is using it, but this can be simply ‘Mirroring’ also.
When you have another instance, you’ll be using 5023, or 5024, etc…for the other instance, the number will need to be increased because the port cannot be shared.

There are no limits on Mirroring with SQL Server’s 64-bit Edition – but you do not want to mirror, for example, 50 sessions. That is merely too much, and too many threads will cause poor performance, unless your hardware budget it not an issue (and since we are in a recession, and people want High Availability for cheap, I am assuming NOT!).

Now that we have enabled SQLCMD, here's an example Mirroring Setup Script:

-- enable Query Execution By default, SQL Server options – use by default SQLCMD mode.

--Create endpoint on the principal server

:Connect Toronto

select name,role_desc,state_desc from sys.database_mirroring_endpoints

CREATE ENDPOINT Mirroring

STATE = STARTED

AS TCP ( LISTENER_PORT = 5022 )

FOR DATABASE_MIRRORING (ROLE=PARTNER);

--Create endpoint on the mirror server

:Connect Montreal

select name,role_desc,state_desc from sys.database_mirroring_endpoints

CREATE ENDPOINT Mirroring

STATE = STARTED

AS TCP ( LISTENER_PORT = 5023 )

FOR DATABASE_MIRRORING (ROLE=PARTNER);

--create endpoint on witness server

:Connect Ottawa

select name,role_desc,state_desc from sys.database_mirroring_endpoints

CREATE ENDPOINT Mirroring

STATE = STARTED

AS TCP ( LISTENER_PORT = 5024 )

FOR DATABASE_MIRRORING (ROLE=WITNESS);

--Security--

--Create logins on each server for the other two parties

--these are domain accounts

:Connect Toronto

USE master;

--Partner

CREATE LOGIN [SQL2008Admin\svcSQL_Montreal] FROM WINDOWS;

GRANT CONNECT on ENDPOINT::Mirroring TO [SQL2008Admin\svcSQL_Montreal];

--witness

CREATE LOGIN [SQL2008Admin\svcSQL_Ottawa] FROM WINDOWS;

GRANT CONNECT on ENDPOINT::Mirroring TO [SQL2008Admin\svcSQL_Ottawa];

:Connect Montreal

USE master;

--Partner

CREATE LOGIN [SQL2008Admin\svcSQL_Toronto] FROM WINDOWS;

GRANT CONNECT on ENDPOINT::Mirroring TO [SQL2008Admin\svcSQL_Toronto];

--witness

CREATE LOGIN [SQL2008Admin\svcSQL_Ottawa] FROM WINDOWS;

GRANT CONNECT on ENDPOINT::Mirroring TO [SQL2008Admin\svcSQL_Ottawa];

:Connect Ottawa

USE master;

--Partner

CREATE LOGIN [SQL2008Admin\svcSQL_Toronto] FROM WINDOWS;

GRANT CONNECT on ENDPOINT::Mirroring TO [SQL2008Admin\svcSQL_Toronto];

--another partner

CREATE LOGIN [SQL2008Admin\svcSQL_Montreal] FROM WINDOWS;

GRANT CONNECT on ENDPOINT::Mirroring TO [SQL2008Admin\svcSQL_Montreal];

:connect Toronto

ALTER DATABASE DB01 SET RECOVERY SIMPLE WITH NO_WAIT

ALTER DATABASE DB01 SET RECOVERY FULL WITH NO_WAIT

BACKUP DATABASE DB01 TO DISK = N'E:\Backups\DB01.bak'

WITH NOFORMAT, INIT, NAME = N'DB01-Full Database Backup', SKIP, NOREWIND, NOUNLOAD, STATS = 10

BACKUP LOG DB01 TO DISK = N'E:\Backups\DB01.trn' WITH NOFORMAT,

no_truncate, INIT, NAME = N'DB01-Transaction Log Backup', SKIP, NOREWIND, NOUNLOAD, STATS = 10

:connect Montreal

---NOW COPY DB BAK/TRN files to DB02 (UNC shared folder references could work too)

RESTORE DATABASE DB01 FILE = N'DB01' FROM DISK = N'E:\Backups\DB01.bak' WITH FILE = 1, NORECOVERY, NOUNLOAD, STATS = 10

RESTORE LOG DB01 FROM DISK = N'E:\Backups\DB01.trn' WITH FILE = 1, NORECOVERY, NOUNLOAD, STATS = 10

-- now right click on the database on DB01, Configure Security, use the domain account

-- Do Not Start Mirroring, click NO until you are sure that the FQDN is used.

-- make sure to have all the FQDN then click Start mirroring (high safety with auto failover)
-- OR, forget the darn GUI and use the below script :)

--enable mirroring first on the mirror

:Connect Montreal

ALTER DATABASE DB01

SET PARTNER ='TCP://SQL2008Admin:5022';

--then connect to the principal

:Connect Toronto

ALTER DATABASE DB01

SET PARTNER = 'TCP://SQL2008Admin:5023';

--set the witness on the principal

ALTER DATABASE Ottawa

SET WITNESS = 'TCP://SQL2008Admin:5024';

--- if every you need to (meaning you have lost the principal and witness, and need to get the Mirror to work):

:Connect Toronto

Alter Database DB01 set Partner FORCE_SERVICE_ALLOW_DATA_LOSS

If you know that a server site is isolated, and that the Witness will not be available to make the decision as to who should be the Prinicpal, only then should one use Force_Service_Allow_Data_Loss

New since 2008, thanks to Glen Berry for the script:
Check auto page repair history (again, SQL 2008 and 2008 R2 only)

SELECT DB_NAME(database_id) AS [database_name], database_id, file_id,

page_id, error_type, page_status, modification_time

FROM sys.dm_db_mirroring_auto_page_repair;

-- if you get no rows, that’s a good thing J

If ever you need to resume mirroring (after pausing for network downtime/retart/etc), and if transactions are committed at the Principal (Toronto, in this setup) but the log was not sent to the former mirror server (Montreal, in this setup) instance, the transactions will be rolled back. This can occur after the following sequence of events:

• Connection between Mirror and Principal have been lost

• Principal continues to commit transactions locally but cannot pass the log to mirror

• For business continuity reasons (DRP) the Force_Service_Allow_Data_Loss on Mirror partner was used when you accepted to lose the transactions – meaning that the Principal is not coming back (long term connection loss, etc.).

• Connection between partners is re-established, the original server becomes the mirror now, and mirroring is thus suspended.

Manual Failover – What Happens When I need to do a Failover?
There may be a situation when one has to reboot / update the Mirror Principal Partner Server, therefore, this can be done via the GUI (database properties-Mirroring) by simply clicking ‘Failover’, this will be proceeded by the following warning:

Prior to doing so, I would suggest checking the Activity Monitor to see that no big transactions are in progress on the current Principal database server host. If you do not check, you might receive an error 'The mirror instance is not caught up to the recent changes in the database 'DB01.' Unable to fail over.' Microsoft SQL Server Error: 1422

Mirroring State Observations: Do Not Worry if it States Disconnected
During our recent disaster recovery exercise – where we purposefully shut off each server site from each other, and vice versa, the mirroring state was in disconnected mode. Do not worry about this if you application is running fine (thanks to the Failover_Partner in the connection string), because most likely, as soon as you go onto the actual server connections will behave properly.

Mirroing Script Setup Without Active Directory (using Certificates)

--Step 1

--Check whether we have database master key created for the master database on each server

--if you have it created, you should see a key named ##MS_DatabaseMasterKey##

:Connect Toronto

use master

Select * from sys.symmetric_keys

:Connect Montreal

use master

Select * from sys.symmetric_keys

:Connect Ottawa

use master

Select * from sys.symmetric_keys

--Step2

--Create the database master key for the master database

:Connect Toronto

USE master;

CREATE MASTER KEY ENCRYPTION BY PASSWORD = '<1_Strong_Password!>';

:Connect Montreal

USE master;

CREATE MASTER KEY ENCRYPTION BY PASSWORD = '<1_Strong_Password!>';

:Connect Ottawa

USE master;

CREATE MASTER KEY ENCRYPTION BY PASSWORD = '<1_Strong_Password!>';

--Go execute Step 1 now

--Step 3

--Create certificates in the master database of each instance

:Connect Toronto

USE master;

CREATE CERTIFICATE Toronto_cert

WITH SUBJECT = 'Toronto certificate';

:connect Montreal

USE master;

CREATE CERTIFICATE Montreal_cert

WITH SUBJECT = 'Montreal certificate';

:Connect Ottawa

USE master;

CREATE CERTIFICATE Ottawa_cert

WITH SUBJECT = 'Ottawa certificate';

--Step 4

--Check that we have certificates created

:Connect Toronto

use master;

select * from sys.certificates

:Connect Montreal

use master;

select * from sys.certificates

:Connect Ottawa

use master;

select * from sys.certificates

--Step 5

--Check whether you have endpoints already created or not

:Connect Toronto

use master

select * from sys.database_mirroring_endpoints

:Connect Montreal

use master

select * from sys.database_mirroring_endpoints

:Connect Ottawa

use master

select * from sys.database_mirroring_endpoints

--Step 5.1

--If we don't have Endpoints, we will create them

:Connect Toronto

use master;

CREATE ENDPOINT Mirroring

STATE = STARTED

AS TCP ( LISTENER_PORT = 5022 )

FOR DATABASE_MIRRORING (ROLE=PARTNER,AUTHENTICATION = CERTIFICATE Toronto_cert);

--Create endpoint on the mirror server

:Connect Montreal

use master;

CREATE ENDPOINT Mirroring

STATE = STARTED

AS TCP ( LISTENER_PORT = 5023 )

FOR DATABASE_MIRRORING (ROLE=PARTNER,AUTHENTICATION = CERTIFICATE Montreal_cert);

--create endpoint on witness server

:Connect Ottawa

use master;

CREATE ENDPOINT Mirroring

STATE = STARTED

AS TCP ( LISTENER_PORT = 5024 )

FOR DATABASE_MIRRORING (ROLE=WITNESS,AUTHENTICATION = CERTIFICATE Ottawa_cert);

--Step 5.2 if they are already created. we will modify the authentication on them to use certificates

:Connect Toronto

use master;

alter endpoint Mirroring

for Database_Mirroring (ROLE=PARTNER,AUTHENTICATION = CERTIFICATE Toronto_cert);

:Connect Montreal

use master;

alter endpoint Mirroring

for Database_Mirroring (ROLE=PARTNER,AUTHENTICATION = CERTIFICATE Montreal_cert);

:Connect Ottawa

use master;

alter endpoint Mirroring

for Database_Mirroring (ROLE=WITNESS,AUTHENTICATION = CERTIFICATE Ottawa_cert);

--Step 6

--Backup the certificates from the servers to exchange them

:Connect Toronto

use master;

BACKUP CERTIFICATE Toronto_cert TO FILE = 'C:\Backup\Toronto_cert.cer';

:Connect Montreal

use master;

BACKUP CERTIFICATE Montreal_cert TO FILE = 'C:\Backup\Montreal_cert.cer';

:Connect Ottawa

use master;

BACKUP CERTIFICATE Ottawa_cert TO FILE = 'C:\Backup\Ottawa_cert.cer';

--Step 7

--Create logins.

--One each server we will create logins to be used by other instances to login to this instance

:Connect Toronto

Create login Ottawa_login With Password='Password1'

Create login Montreal_login with password='Password1'

:Connect Montreal

Create login Toronto_login With Password='Password1'

Create login Ottawa_login with password='Password1'

:Connect Ottawa

Create login Toronto_login With Password='Password1'

Create login Montreal_login with password='Password1'

--Step 8

--Create users for these logins in the master database

--Associate these users with certificates

--Be aware of the ACL issue.

:Connect Toronto

Create user Ottawa_user for login Ottawa_login

Create user Montreal_user for login Montreal_login

CREATE CERTIFICATE Ottawa_Cert

AUTHORIZATION Ottawa_user

FROM FILE = 'C:\Backup\Ottawa_cert.cer'

CREATE CERTIFICATE Montreal_Cert

AUTHORIZATION Montreal_user

FROM FILE = 'C:\Backup\Montreal_cert.cer'

:Connect Montreal

Create user Toronto_user for login Toronto_login

Create user Ottawa_user for login Ottawa_login

CREATE CERTIFICATE Toronto_Cert

AUTHORIZATION Toronto_user

FROM FILE = 'C:\Backup\Toronto_cert.cer'

CREATE CERTIFICATE Ottawa_Cert

AUTHORIZATION Ottawa_user

FROM FILE = 'C:\Backup\Ottawa_cert.cer'

:Connect Ottawa

Create user Toronto_user for login Toronto_login

Create user Montreal_user for login Montreal_login

CREATE CERTIFICATE Toronto_Cert

AUTHORIZATION Toronto_user

FROM FILE = 'C:\Backup\Toronto_cert.cer'

CREATE CERTIFICATE Montreal_Cert

AUTHORIZATION Montreal_user

FROM FILE = 'C:\Backup\Montreal_cert.cer'

--Step 9

--Grant connect permission on each EndPoint for logins for the other servers

:Connect Toronto

use master;

Grant Connect on EndPoint::Mirroring to Ottawa_login

Grant Connect on EndPoint::Mirroring to Montreal_login

:Connect Montreal

use master;

Grant Connect on EndPoint::Mirroring to Toronto_login

Grant Connect on EndPoint::Mirroring to Ottawa_login

:Connect Ottawa

use master;

Grant Connect on EndPoint::Mirroring to Toronto_login

Grant Connect on EndPoint::Mirroring to Montreal_login

--REPEAT BACKUP/RESTORE AND ALTER OPERATIONS IN PREVIOUS AD SCRIPT

As usual, I hope you enjoy Mirroring as much as I do. It is very much set it and forget it - just make sure your intensive DBs have Transaction Log backups frequently - since Full Logging has to be on for Mirroring.

For an even Deeper Dive, here is a free Book on SQL 2008 Mirroring, thanks to the great work of Robert L. Davis and Ken Simmons.

Guest Vs. Host Clustering Virtual Machines: Spoke Last Tuesday 23rd November at Montreal's Microsoft Tech Days

2010-12-17T10:31:00.000-08:00

I spoke in the place of speaker Steven Robidas on Tuesday, 23 November at Microsoft TechDays.ca Montreal. The discussion was on Managing and Securing IT Infrastructure - session INF315: Guest vs. Host Clustering Virtual Machines: What, When, and Why? Day 1 - 3:40pm - 4:45pm.
The link provides all the session content, plus videos on each step to setup a Hyper-V Cluster.

Tech Days 2010 Technology Conference was at the Palais des congres (Congress Centre, Montreal)
Microsoft Canada Events techdays.ca : a technology training conference for IT Professionals in Montreal.
For those interested in joing us in Montreal, Canada, come to learn about technologies, connect with experts and apply what you have learned.
Je remplacerai Steven Robidas mardi prochain, le 23 novembre au Microsoft Techdays.ca Montréal - discours sur Host Versus Guest Clustering: Quand, comment et pour quelle raison (?) à 15:40 - Gestion et sécurité de l'infrastructure informatique.

Intel’s X25 Generation Solid State Disk Performing Well

2010-12-17T10:26:00.000-08:00

Ever since I took myself voluntarily off company workstation support by installing Windows 7 on the corporate Dell E6400 laptop, I have noticed some instability due to Remote Desktop Connection Manager: each blue screen occurred while trying to connect to a Server within RDCMon or Powerpoint, but in the end, it was ultimately due to not installing the latest Intel Drivers related for the SSD (Sata to Chipset interface).

This nuisance was minor however, since Registry Mechanic cleaned up the machine and optimised services pretty well, and I am happy with the amazing performance of the X25 drive (nice 7.2 rating, as you can see below), although I could only afford the 80GB version…I would recommend at least 120GB for a comfortable Windows 7 install because I just installed Denali CTP1 and the drive is almost full :(

To counter the lack of drive space, I have made use of Folder Compression, even on some Program File directories (after fully patched install) and it speeds up their access time across the disk at the expense of the decompression via the CPU. I would avoid this, but disk space is limited.
Next up, I will need to request some more RAM, since 2GB is pretty sparse.

Database Security Best Practices for the Vigilant Database Administrator and Developer (Unabridged Edit)

2010-11-12T11:37:00.000-08:00

The security of corporate databases and related aspects have become increasingly important as enterprises consider the data they store, purchase, or acquire as assets that are critical to operations. Consequently, those involved with responsibility and control over these resources must assume the role of stewards of the data;- otherwise, the risks to business operations involved for lack of such a pro-active attitude, could lead to commercial peril.

My interpretation on this subject of data security will consider all the issues I can reflect upon, deliberated over, have studied and blogged about over the past couple of tumultuous years, following Erasmus’ adage to leave no stone unturned. I believe the responsibility to protect data is under acknowledged by executives and management, leaving the bureaucratic obligation left in the hands of ineffectual auditors, when it is truly up to the database administrators (DBAs) principally and developers additionally, to handle data responsibly. Hence, the points I address below should enable the individuals tasked with the data security role, indirectly or directly, to straightforwardly achieve the indispensable level of responsibility, even if those imaginably at the higher ranks of the organization do not realize the direct link between proper data management (since data is a valuable asset in itself) and the organisation’s reputation.

Secure Your Data with an Effective Disaster Recovery Plan and Test It Regularly
This subject apropos an effective disaster recovery strategy can easily fill an entire book: consequently I dedicated an extensive article of over four thousand words to the topic last summer, after months of editing. The article provides a complete open-source disaster recovery plan, where readership from all sources is at almost forty thousand (40k), cumulatively from Blog sources and French translation. It will be updated shortly to include Database Mirroring as an alternative, before SQL Server 2011’s functionalities are released, in which new features will enhance High Availability greatly.

Groom Your Database of Sensitive Data to Reduce the Risk of It Being Disseminated
If you do not want those who have access to the data to be able to see all archived information—for example, human resources data that includes salary information—take the time to properly archive it. Simultaneously, however, you will want to be sure not to lose information required by regulation.

Separation of Duties with Respect to Internal Control: Maintain Compliance on DBs Used for Public Reporting
If you are a developer, it’s important to be aware of the segregation of duties within your respective organization, and to ensure that internal controls fall under the general Committee of Sponsoring Organizations (COSO) of the Treadway Commission framework, which is more explicitly defined in the Sarbanes-Oxley (SOX) Act. One of most important points of SOX compliance is the segregation, or separation, of duties, which basically means developers do not have access during production (to prevent the tendency to test there) and that specific procedures are followed when developing code before placing it in production. These procedures satisfy risk management practices under the COBIT framework (or ITIL) to avoid data being inadvertently, mistakenly, or maliciously changed due to ad hoc changes. Territorial as it may seem, it is necessary for physical DBAs to control production databases from development, and, without fail, to follow change management practices as defined by these frameworks. By following procedure and documentation by the COBIT or ITIL standards, we govern ourselves in a controlled environment, with respect to the production systems. Each task has to be validated (code walkthrough/fresh eyes) by a third person who is not writing the actual code. The database developer should not be able to execute anything in production without an independent review of the documentation/code for the work that is being performed. Typically, the role of the developer is to pass code to a DBA; however, given the cutbacks that have resulted from the current economic climate might not be readily available. If a DBA is not involved, it is important, at minimum, for a peer to conduct a code review. This ensures that the role of the developer is clearly separate.

If your organisation is in denial (worst case scenario) or lacks understanding (best case scenario) of internal controls, please remind management that databases which fall under internal controls (meaning the data used for public reporting, annual reports, et al.) are subject to the separation of duties. Furthermore, to track all changes and have recoverability, a bare minimum for a SQL Server database recovery is Bulk-Logged Recovery. Make sure to back up all your transaction logs and keep them indefinitely, which, in addition to providing a backup, will satisfy auditors (below I describe a way to read operations from the transaction log files directly).

Another point of internal control is adherence to the principle of providing the least amount of privileges, especially in production. To allow developers more access to get their work done, it is much safer to use impersonation for exceptions that require elevated privileges (see EXECUTE AS to do that temporarily). Often developers may dismiss this as “overhead” while on their path to coding glory (or out of lethargy :) Please be aware, however, that DBAs must do all that is considered responsible because they are the de facto data stewards of the organization and must comply with regulations and the law.

Group Your Objects Together Logically
You have probably all noticed a change since SQL Server 2000 with respect to object ownership: formerly DB.ObjectOwner.Object, now DB.Schema.Object since 2005. With Schemas, we can benefit from the grouping of the objects to make sense of our often complicated relational database management systems. For sure, a user can still create an object within the database as UserName.Object, that is a little messy to clean up when that person leaves the company, thus Sp_rename can be used to fix those pesky messes however, if you have to deal with them.

In order to grant schema rights to a user, here are a few examples:
Grant select, insert, update, execute on Schema::SchemaName to [UserName]
-- this is assuming that you have used a specific user UserName generated and SchemaName exists
CREATE SCHEMA [SchemaName] AUTHORIZATION [dbo] – members of DBO have control over the Schema
-- if you are using the built-in roles, then see below
CREATE USER [Domain\UserName] FOR LOGIN [Domain\UserName] WITH DEFAULT_SCHEMA= [db_datareader]

If you want a user to have read access, make the default schema db_datareader and not database owner (DBO).

If you grant the DBO rights, then you have granted DBO rights with control to all roles and schemas.

USE [DatabaseName] -- if you have to elevate their rights, then use the db_datawriter role first, instead of DBO

EXEC sp_droprolemember N'db_datareader', N'Domain\UserName'

USE [DatabaseName]

EXEC sp_addrolemember N'db_datawriter', N'Domain\UserName'

When we create an object, we can simply associate it within a schema (Create SchemaName.Object), which, in turn, makes managing the security of that group of objects easier. The other advantage is that we can place a schema within the database by the name of the application that uses it, which is a logical separation. Likewise, if multiple applications depend on a single database, schemas make it easy to understand which objects are used by what/whom and how within the database.

As you can see in the above example, it's easier to manage the security on the group of objects too, since you would set the permissions on schema level and not for each individual object, which was noticeably tedious beforehand. If you wish to maintain the principle of least privilege, giving DBO access to users is not the best solution in regard to security. A preferred workaround for elevated privileges would be by means of EXECUTE AS instead (for more, see a great article on that here).

(Note: You can also use stored procedures and views to minimize data access, but because this is more commonly known than security facets of schemas, I will not be expanding on that subject here.)

Easily Watch Over Processes Within Your Database Thanks to Activity Monitor
As part of a vigilant DBAs’ duty to best serve as a steward, and to comply with regulations—particularly if your organization is public or governmental—I would like to introduce one of my favorite SQL Server Management Studio enhancements with SQL Server 2008 that DBAs should be familiar with: Activity Monitor (AM). To use AM, right click on a SQL Server 2005/8 instance in the object explorer and take a look (or click on icon just below Tools/Window in SSMS, the toolbar icon that is the farthest right):

The latest version of Activity Monitor helps you see the processes, users, and applications that are currently using the database in the sortable/filterable Processes pane, and, of course, whether they are blocking transactions.

Even if you do not have SQL Server 2008 instances, it is still worth it to install SQL Server 2008 Management Studio complete client tools. Activity Monitor has built-in backward compatibility (except SQL Server 2000 and older); thus, you can take advantage of the dynamic management views already existing in SQL Server 2005 while connecting from the SQL Server 2008 Management Studio Activity Monitor. Previously, for example, to view the equivalent information in the Activity Monitor, one had to load information directly from the dynamic management views, or in SQL Server 2000 (exec sp_who2), in Excel sheets using an external data source connection to understand what was really going on across the a specific servers’ activity. Thankfully, this is now all built into SQL Server Management Studio because Activity Monitor provides sortable columns, which enable exceptionally swift pinpointing of problematic operations by database, through information on execution time/frequency, reads/writes, and CPU usage.

Since the initial version of SQL Server 2008 was released, you have been able to view real-time critical SQL Server process details and even sort by the worst-performing queries—whether it be by the number of times the offending code is run per minute, which login is running it, which database it is in, which application it is running from, etc. In Activity Monitor, practically everything you need to fix SQL Server issues is available, including the option to right click on a line in Recent Expensive Queries to optimize the problematic code right away. There are four panes with graphs for each, plus collapsible details, so you can view/filter processes, resource waits, disk activity, and Recent Expensive Queries.

If you cannot update your instances to SQL Server 2008 for a while, which would not be surprising considering the economic tsunami that just hit the world, then you can use the updated client tools in SQL Server Management Studio 2008, then at least you can use the updated client tools to enjoy this eye-opening and cost-effective updated feature—in my opinion, a critical step in remediation.

Set You Database Instance Security to Audit Successful and Failed Logins
The SQL Server log file may become significant in size if many users and/or applications connect to your database server, but now that disk space is cheap, do not skimp on your auditing capabilities. After all, you want to know the who/what/when of logins to your particular database instance. To set your server to audit logons to the SQL Server Instance, right-click on the server instance in SQL Server Management Studio, select properties, choose Security and click the radio button under Login auditing ‘Both failed and successful logins.’

Keep the Integrity of Your Database Secure by Querying Your Transaction Log File
All operations are recorded to your database transaction log file, especially if you are using Full Recovery Model; therefore, you can take advantage of a few simple queries to double-check who is running which command in the database. For more on this, read up on how to view the important information within your transaction log file. Some would consider this transaction log file forensics; I prefer to think of it as doing your job as a vigilant DBA. In the linked blog post, I describe how you can set up jobs to alert you of unwanted data manipulation language (DML) statements (such as inserts, updates, delete) or data definition language (DDL) (such as create, drop, alter).
Hack Your Databases Before Some Unwanted Access Occurs
As mentioned by Don Kiely in his great article “Hack Your Database Before the Hackers Do,” one should use a series of tools—at least three in my opinion (Microsoft SQL Server Best Practices Analyzer, SQL Password Checker from Idera, and Microsoft Baseline Security Analyzer)—to ensure due diligence.
A word of caution: If you are not the DBA, please make sure you have explicit authorization to gain access into production before proceeding. Even if you are on a project, do not assume that you can simply try to gain unauthorized access into production, as this would be considered an intrusion by the database administration team or miguided auditors.

Maintain Strong Passwords: There is No Excuse; the DBA Is Accountable
Use a password generator such as Password Manager Pro from ManageEngine, and keep the password in a password vault. Password strength is facilitated by free online password generators that uses a password length of 12 alphanumeric characters. Avoid default passwords or cutting and pasting a user name (or a using a password name that describes the level of access). Needless as it may be to say, these are not shortcuts you want to take, especially since simple software (e.g. SQL Password Checker, mentioned above) can verify password strength.

Validate User Access: Attempt to Evaluate Potential Risks to Database Security
Not only should you fully understand what roles, or specific permissions, are given for a specific user and/or application, but you can also confirm that access is at the desired level by logging on as that specific user. If you are making big changes to your systems that involve several user access changes, be sure to go through a significant deployment management process (short and long templates can be downloaded for free).

Account Management: Keep the Access List Updated
As mentioned by Sean Smith, who has helped to contribute to this article, it is essential to tidy up by means of disabling accounts, either for termed employees, or for employees who no longer require database access. It is amazing how many accounts remain active for such people whose role or mandate has changed.

Protect Against SQL Injection
Lack of proper protection against SQL Injection, fully described here by SQL MVP Adam Machanic, can cause immeasurable damage, whether intentional or accidental. In the previous link to his book Expert SQL Server Development, Adam describes the ways to protect against this, although it is unfortunately seldom considered or implemented.

Databases Are Not A Place To Be Lax On Security
As mentioned by Ericka Chickowski, in her article regarding DBAs Playing [an] Increasingly Crucial Role In Security, it is a vital aspect of our job. The points above should help guide you in the right direction as you work to help protect your organization’s assets and customer data. The consequences of not safeguarding data or failing to comply with regulations for data security can include significant fines, of up to one million dollars (yes $1M), as is done by the Information Commissioner’s Office in the United Kingdom vis-à-vis violations of the Data Protection Act, and can jeopardize business operations and the reputation of your organization. Failing to be vigilant with data could also result in the loss of billions of dollars of public money, a situation I unfortunately witnessed first-hand six months after I had internally provided a report detailing an audit failure at Canada’s largest public Institutionalized pension fund manager, the Quebec Deposit and Investment Fund (CDP Capital).

Additional Resources for Risk Management, IT Security and Data Governance from Microsoft:

Data Governance - Managing Technological Risk

Discusison of the core data governance capabilities related to technology.

http://www.microsoft.com/downloads/en/details.aspx?FamilyID=e4ff1afe-69cd-4bfd-aeaa-c0519ba04272

Data Governance - A Capability Maturity Model

This paper presents a blueprint for organizations to implement the capabilities needed to establish a successful DGPC program.

http://www.microsoft.com/downloads/en/details.aspx?FamilyID=1a45054b-b073-44e6-84f8-bee33c268f33

Microsoft Anti-Cross Site Scripting Library V4.0

AntiXSS 4.0 helps you to protect your applications from cross-site scripting attacks.

http://www.microsoft.com/downloads/en/details.aspx?FamilyID=f4cd231b-7e06-445b-bec7-343e5884e651

Microsoft Office Protocol Documentation

The Office protocol documentation provides technical specifications for Microsoft proprietary protocols that are implemented and used in the Microsoft Office system.

http://www.microsoft.com/downloads/en/details.aspx?FamilyID=e7a23d42-0835-440f-9400-badfe9672b21

Microsoft and Data Privacy

This paper examines trends in the evolving data management landscape and describes how Microsoft is providing leadership in protecting individuals’ personal information.

http://www.microsoft.com/downloads/en/details.aspx?FamilyID=297c2531-2621-446a-9efb-db87f76de02e

Microsoft SharePoint Products and Technologies Protocol Documentation

The Microsoft SharePoint Products and Technologies protocol documentation provides technical specifications for Microsoft proprietary protocols that are implemented and used in SharePoint Products and Technologies.

http://www.microsoft.com/downloads/en/details.aspx?FamilyID=5e94ad07-902c-422f-aadd-ff2bba9e540a

Microsoft and Data Breach Notification

Microsoft and Data Breach Notification

http://www.microsoft.com/downloads/en/details.aspx?FamilyID=b378d038-566d-47e1-8946-ba0561e0b988

TwC Enterprise Data Governance White Paper

Private enterprise privacy white paper, providing Microsoft's perspective on the role that technology plays in helping enterprises responsibly protect and manage personal information.

http://www.microsoft.com/downloads/en/details.aspx?FamilyID=53035b0d-66be-415a-aadc-ae47105af354

Data Governance White Paper

Data Governance White Paper

http://www.microsoft.com/downloads/en/details.aspx?FamilyID=5c711a4d-e7a9-44af-985e-f36774417f2b

IT GRC Process Management Pack for System Center Service Manager

The Microsoft® IT GRC Process Management Pack for System Center Service Manager(SCSM) provides end-to-end compliance management and automation for desktop and datacenter computers. Deeply integrated with SCSM the IT GRC Process Management pack translates complex regulations and standards into authoritative control objectives and control activities for the IT organization’s compliance program.

http://www.microsoft.com/downloads/en/details.aspx?FamilyID=aff058bb-628f-4a7d-b566-317cb53f666e

A Guide to Data Governance for Privacy, Confidentiality, and Compliance

Data governance is an approach that public and private entities can use to organize one or more aspects of their data management efforts, including business intelligence (BI), data security and privacy, master data management (MDM), and data quality (DQ) management. This series describes the basic elements of a data governance initiative for privacy, confidentiality, and compliance.

http://www.microsoft.com/downloads/en/details.aspx?FamilyID=8ba05a48-f46c-491d-8857-6d9d6da2d3e3

Data Governance - People and Process

This paper examines the People and Process core capability areas required to enable Data Governance for Privacy, Confidentiality and Compliance.

http://www.microsoft.com/downloads/en/details.aspx?FamilyID=b42cc616-fb59-4942-bc43-d4992dcb6f51

Microsoft and Data Breach Notification: Guidance for Enterprise Organizations

Document presenting data breach risks and concerns for organizations, and guidance for responding to a data breach.

http://www.microsoft.com/downloads/en/details.aspx?FamilyID=49151c48-dd1d-4094-8722-d1e74f821426

Privacy Guidelines for Developing Software Products and Services

This document is a set of privacy guidelines for developing software products and services that are based on our internal guidelines and our experience incorporating privacy into the development process.

http://www.microsoft.com/downloads/en/details.aspx?FamilyID=c48cf80f-6e87-48f5-83ec-a18d1ad2fc1f

IT Compliance Management Series

The IT Compliance Management Series—a combination of IT Compliance Management Libraries for Windows Server 2008, Windows Server 2008 R2, Windows 7, and Microsoft System Center—provides prescriptive guidance that helps IT pros configure Microsoft products to address specific IT governance, risk, and compliance (GRC) requirements.

http://www.microsoft.com/downloads/en/details.aspx?FamilyID=37ec588c-e1bc-415b-acaa-b9b4d494f466

Notes from MySQL Enterprise Lunch and Learn With Representatives from Sun & Oracle (MySQL 5.5 Now Downloadable)

2010-11-12T11:24:00.000-08:00

Today we sat through a pizza ‘n pop lunch while listening to a MySQL Offering and Licensing for the company I have been working for as a permanent for about a year now. Since the take-over by Oracle, the joke was that speculators were thinking that the new version of MySQL would be Oracle 11gMy or simply Oracle Lite J

The Oracle Account Manager and MySQL Enterprise Salesman described to us how MySQL AB was founded in ‘95, and that it quickly became the most popular open-source database. It has been quite a success story, with a truly global group of geeks that contributed over the years. They continued to inform us that today there are approximately 12 Million Production Installations and approximately 70,000 downloads a day – half of them to be hosted on the Windows platform. It should be noted that Facebook, with 500M users, is all MySQL backend according to the pair: Account Manager from Sales Consultant from Oracle Corporation Canada. One primary reason that MySQL has done so well is that it scales out, as did Google with its consumer hardware in the beginning, from cheaper consumer/custom build systems that were added onto, and expanded upwards.

MySQL Cluster on the NDB Engine (applicaitons requiring 99.999% uptime, auto-failover, high perf.) is the full on Enterprise version (at a cost), and Oracle’s MySQL strategy is to be open, integrated, complete, while remaining low cost/risk, and reliable. Oracle bought MySQL because it is best of breed and a complete RDBMS.

What I did not know so much, was that there is a MySQL & Oracle history of development together. In 2005 Oracle purchased InnoBase Oy, makers of InnoDB, and the following year, Oracle renewed the InnoDB license. Of the RDBMS market share, according to them, MySQL is a leader for embedded, saas, Online, ad hoc DBs, and Data Marts, whilst it holds much less market share with ERP, CRM, or DW than Oracle itself (so a niche market). Golden Gate, subject of a lot of positive buzz, is integrated also with Oracle MySQL, since it is a clear addition to Oracle’s existing database suite of products, including TimesTen, Berkely DB open source. They stated that MySQL is global business within Oracle, actually defined by them as the Oracle Open Source Global Business Unit, and this was demonstrated during Oracle OpenWorld having a day dedicated solely to MySQL: the MySQL Sunday session.

In the future, there will be more Windows platform development, as well as for the LAMP (Linux Apache MySQL PHP) platform – their primary target audiences/clients, and huge fan base obviously.
It should be noted, that If you distribute MySQL within an app, then it is mandatory to buy a commercial license (OEM license fee). Of the 12M installations, most are community server, the free version.
They claim their branch is still pure MySQL, and although the original creators have moved onto creating a new Nordic style ORM (Paul Nielsen will like this?), new flavour of MySQL, Oracle is dedicated to this database platform.

For my part, I am quite impressed with MySQL Workbench, and its updated versions, seemingly every month, since June this year. One can download the Workbench tool for all common operating systems here:
http://www.mysql.com/click.php?e=35294

As part of a group of DBAs asked to handle a large group of MySQL Instances, once you include all the test, development and production environments, the most usefull for production was this great new administrative tool. I liked it so much that I left a super positive comment on the MySQL Workbench download page while hoping not to be accused of heterodoxy by my fellow SQL Server MVPs. I am not alone, other comments from some SQL Server users are here.
Just check out the Administrative print screen from their site.

Not only does the tool have the typical SQL Editor Query window, but it also has a humble entity relationship diagram tool called Data Modelling built in just like all the larger vendors do
(or is Oracle’s influence that strong already on MySQL, I wonder?):

Just today, funnily enough, I was spammed by Oracle regarding the New MySQL 5.5 release for windows:
Their claim of being 1500% faster makes me wonder how much Oracle's better knowledge of the Windows Platorm (Lock pages in memory, etc), and how to truly optimise applications for this platform, has to do with such a mega difference between 5.1 and 5.5.

Connection Strings: Database Mirroring Failover Partner Setup & Sample Backup/Restore Preparation Script

2010-10-24T21:51:00.000-07:00

After having setup my first SQL 2008 R2 Enterprise (build level 10.50.1702) database mirror as High Safety with automatic failover, meaning synchronous commit on both servers (see operating modes), there was an obvious question that followed from those who need to employ this redundant database – which/what server do Users/Apps connect to? Sharepoint 2010 high-availability can be attained with a combination of mirroring also by the way, as documented here.
Please note, this is a follow up to a previous post on resolving issues associated with Mirroring and taking advantage of the 64-bit benefit. This time around, since the version upgrade is two generations later, where last work on Mirror set was for SQL 2005, with 2008 only in testing, I am interested now to dig down and find out if the Log compression is actually running between the two instances (it is a new feature of SQL 2008), but first the connection string:

Server=tcp:DB01.RestOfFullyQualifiedDomain;Database=MirrorTest;Connection Timeout=30;Integrated Security=SSPI;Failover Partner=DB02.RestOfFullyQualifiedDomain

I set a 30-second timeout since this is geographically redundant database mirroring.
SQL default connection port is 1433, and mirroring uses 5022 to communicate between the quorum

MirrorTest will simply be replaced with whatever the actual database name one will be using.

Back to the Mirror Setup
Below is the script used for the database setup, since the previous post has details for full script setup. This was followed by testing the push of four million rows, through direct 1M inserts into the four largest database tables; I noticed a maximum delay of around 3.5 seconds before the logs were all applied on the Mirror at a speed of 3-5MB/sec. And on another server that was Virtual Machine based, I did much the same test but with only 20ms commit overhead reported.

After some research around the web, I discovered the best way to avoid error: 1478 , after checking all of Pinal Dave’s useful list of pre-Mirroring checks also, was to do a as is shown below in the script.

n I have Partner Principal=DB01 and Partner Mirror=DB02 and
Witness=WDB01 in the Full Mirroring quorum.

USE [master]-—on DB01
GO
DBCC CheckDB(MirrorTest) -– always double-check a DB before mirroring
GO
ALTER DATABASE [MirrorTest] SET RECOVERY SIMPLE WITH NO_WAIT
GO -– we force this to ensure we do not run into error: 1478
ALTER DATABASE [MirrorTest] SET RECOVERY FULL WITH NO_WAIT
GO
BACKUP DATABASE [MirrorTest] TO DISK = N'Drive:\Backups\DB_converted.bak' WITH NOFORMAT, INIT, NAME = N'MirrorTest-Full Database Backup', SKIP, NOREWIND, NOUNLOAD, STATS = 10
GO
BACKUP LOG [MirrorTest] TO DISK = N'Drive:\Backups\DB.trn' WITH NOFORMAT, no_truncate, INIT, NAME = N'MirrorTest-Transaction Log Backup', SKIP, NOREWIND, NOUNLOAD, STATS = 10
GO

---NOW COPY DB BAK/TRN files to DB02 (or UNC shared folder references could work too)

RESTORE DATABASE [MirrorTest] FILE = N'db' FROM DISK = N'Drive:\Backups\DB_converted.bak' WITH FILE = 1, NORECOVERY, NOUNLOAD, STATS = 10

GO
RESTORE LOG [MirrorTest] FROM DISK = N'Drive:\Backups\DB.trn' WITH FILE = 1, NORECOVERY, NOUNLOAD, STATS = 10
GO

Last Steps
Once your backup/restore are okay, run the Start Mirroring Command from the GUI (after setting up with the domain account that has Admin rights on all three servers), and make sure to start the mirror with Fully Qualified Domain Names. You should find the Status now as Synchronised.

Tried to Break the Mirror While Doing Stress Testing – But Still Fine J
Once the mirror was all setup nicely, and the stress test had worked out fine, I then began to and see what would happen if I shut down the Principal Server node via a SQL Server DB Engine service shutdown at the same time as a large stress-testing insert was happening. Instantly the Mirror became the Principal thanks to the Witness and the Database Mirror Monitor stated there was a Mirroring State Disconnection, followed by a large couple of hundreds megabytes queuing in the Unsent Log from the history of the DB Mirror Monitor. Then I restarted the former Principal (now Mirror) DB Engine and the Unsent log disappeared while the new data was applied at 5MB/sec over the space of 45 seconds.
Once all was synchronised without traffic, I simply set through the GUI a Failover Mirror command to return DB01 as the Principal. The change was instant, although I was not inserting millions of records at the time. Cannot wait to see how this all pans out, in terms of Administrative intervention, over time.