Saturday, December 31, 2011

Decimal Showdown

In setting up replication between a MSSQL publisher and a MYSQL subscriber, I have come across an annoyingly maddening issue. If there is a published column that has the decimal data type, it will cause replication to fail with an obscenely obtuse error. "Multi-step OLEDB error". Yeah try searching for that.
Did you find any reference to replication between MSSQL and MYSQL?
No?
Did you find posts about errors about data types?
Which led me to my next thought.

It came down to me doing a trial and error method going column by column changing the datatype on the MYSQL side, while leaving the MSSSQL datatype untouched. My method was, as follows:

  1. Stop replication
  2. Change data type of MYSQL column
  3. Delete all data from MYSQL table
  4. Reinitialize subscription
  5. Cross fingers

This looped for either 5 or 6 times, I have chosen to forget. Off I went searching The Google for any sort of help. I came up with a promising LiveJournal blog post. While helpful for background information of the 2006 difference between the two decimal data types in question, it's from 2006. There has been a precision change in the MYSQL decimal since then. The Books On-Line is a little thin in terms of the details of the decimal data type.

What is a novice DBA to do? Well use a different data type for MYSQL of course!

Let me forewarn, the data that is being replicated goes through no changes once it has been shipped over to the MYSQL server. All of the work of mathematics is done on the MSSQL side. The MYSQL is merely for displaying the data, not manipulating it. As such, I have elected to use the float data type. To reiterate I am doing no calculations on the MYSQL side, this must be said because using float is a really bad idea.

Don't believe me? Just ask this commentator on a StackOverflow thread. Using float to do calculations will give you a number, however you can't be sure of the exactness of it. Sure, if you using whole numbers and not worrying about accounting values, you don't have to worry about rounding. I on the other hand, do. We have gone through extensive testing in preparation for the data type change.

Our replication setup is such that the tables on the MSSQL side are just copies of the tables that are used in production. The tables on the replication server are used in no calculations, in our testing there was no change in the values on either side of the fence.

So in conclusion, don't do what I have done, do not use the float type as an equivalent data type for replication between MSSQL and MYSQL. It only works in this one situation. I am still on the search for a  resolution to my problem. Perhaps a different numeric data type will swoop in and save the day? Perhaps some DBA much smarter than myself will tell me why can't the two decimal data types get along? Until then, I'll have to deal with using float in place of decimal. If anyone out there has a solution please for the love of all that is holy, email me.

Wednesday, July 20, 2011

Learning SQL the hard way


(Yes, I borrowed that format from Learn Python the Hard Way http://learnpythonthehardway.org/)
I am currently studying for the 70-432 (SQL 2008, Implementation and Maintenance). If you had asked me two years ago if I was going to be studying for a SQL certification that answer would be a large no. Yet here I am. Am I ready for this? Is it going to matter? I certainly hope.

I totally fell into being the DBA for the company I work for. I came into the company as a “help desk” type of work, all I needed to know about SQL was how to restart the services if it ever failed. There were no backups, no rebuilding of indexes, no updating statistics. There was only hoping it didn’t fail, and crossing of fingers that we didn’t need to restore data. 

I wish I could say I became a DBA with guns blazing, kicking slow-queries, and taking traces. I wish I could say I don’t have to worry about a mortgage. I must give a major thank you to the company, they sent me to classroom training for 3 days a week for 4 weeks about maintenance and implementation of SQL 2005. That opened my eye to what I didn’t know, which was a hell of a lot.  After the class was over, I came back to work charged with fresh ideas and ways to actually use what I learned. I had maintenance plans, I was running DBCC checks once a week, I was doing transaction logs every fifteen minutes, I was doing nightly backups, fulls on the weekends. There was an index, or a stat that I didn’t rebuild, reorg or update. I was a man on fire. I had a road map for the SQL infrastructure; I was reading blogs, forums anything I could get my hands on.
Then I had to take off my DBA hat because everything was running fine.

On went the Sys Admin hat. We had to make the switch from physical infrastructure to a virtual one, and that wasn’t going to switch itself. I have kept up with my DBA personality, we are upgrading to SQL 2008R2 for our environment. Which brings me back to studying for the MCTS 70-432 test, I had to learn on the fly for SQL 2005. As much as I liked the whole “trial-by-fire” thing, I’d much rather to try knowing what is coming at me.

Friday, April 8, 2011

Database Mirroring with Transparent Data Encryption and You

I was presented with a challenge at my place of work. We are in need of using TDE to cover ourselves and our data, we also needed to setup database mirroring to keep inline with our DR/BC model. So I was tasked with making these two things work with one another.

Now I have successfully gotten both technologies to work before, so the challenge was how to get them to work together. Below is what I have figured out.

This website served as the foundation of the TDE+Mirroring, but the author left out key steps that I had to scrounge to fill in.

Overview:
Setup TDE on Principle DB
Restore TDE keys/certs from Principle DB to Mirror DB
Setup Mirroring on Mirrored DB
Setup Mirroring on Principle DB


Below are the individual steps I have taken to setup TDE and Mirroring.

The steps are setup so that the t-SQL (in italics) is on top with an explanation underneath (in bold).

On Principle:
USE MASTER
GO
CREATE MASTER KEY ENCRYPTION BY PASSWORD = 'SomePassword';
go

Creating the “master key” for the SQL server that houses the principle database, make sure that the password is a strong one
-------------------------------------------------------------------------------------------------------------------

On Principle:
USE master;
OPEN MASTER KEY DECRYPTION BY PASSWORD = ‘SomePassword’;
BACKUP MASTER KEY TO FILE = ‘Location\MasterKeyName.key' ENCRYPTION BY PASSWORD = 'SomePassword';
GO

Right after creating the “master key” we issue the command to back that up to a safe location. Most commands that you issue on the principle database or master key, will have to be prefaced with opening the master key by the password.
-------------------------------------------------------------------------------------------------------------------

On Principle:
USE Master
CREATE CERTIFICATE CertName WITH SUBJECT = ‘CertFriendlyName’, EXPIRY_DATE = '3500-Jan-01';
Go

Here we are creating a certificate that will sign the keys, with no expiration date.


-------------------------------------------------------------------------------------------------------------------

On Principle:
BACKUP CERTIFICATE CertName TO FILE = ‘Location\CertName.cer'
          WITH PRIVATE KEY ( FILE = 'Location\CertKeyName.key', ENCRYPTION BY PASSWORD = 'SomePassword');
GO

Again, right after creating the cert we are backing it up. We also create yet again another key this time to encrypt the cert. Make sure that the key for the cert is NOT the same key as the “master key”, the code will fail and not with a helpful error.
-------------------------------------------------------------------------------------------------------------------

On Principle:
USE DatabaseName
CREATE DATABASE ENCRYPTION KEY
WITH ALGORITHM = AES_256 ENCRYPTION BY SERVER CERTIFICATE CertName;
GO

This step creates the database encryption key using the cert we created above. You can change the type of encryption; just check Books Online for options.
-------------------------------------------------------------------------------------------------------------------

On Principle:
USE master;
GO
ALTER DATABASE DatabaseName SET ENCRYPTION ON
GO

So finally we turn TDE on for the principle database.


We now turn our efforts to the server that will host the mirrored database.

On Mirror:
USE master
RESTORE MASTER KEY
    FROM FILE = 'Location\MasterKeyName.key'
    DECRYPTION BY PASSWORD = 'SomePassword'
    ENCRYPTION BY PASSWORD = 'SomePassword';
GO

We are restoring the backed-up master key file that we created on the principle server to the mirror server
-------------------------------------------------------------------------------------------------------------------

On Mirror:
USE Master;
OPEN MASTER KEY DECRYPTION BY PASSWORD = 'SomePassword'
CREATE CERTIFICATE CertName   
FROM FILE = 'Location \CertName.cer' WITH PRIVATE KEY ( FILE = 'Location \CertKeyName.key', DECRYPTION BY PASSWORD = 'SomePassword');
GO


We are restoring the cert to the mirror, using the cert name and passwords that were created on the principle.
-------------------------------------------------------------------------------------------------------------------

On Mirror:
OPEN MASTER KEY DECRYPTION BY PASSWORD = 'SomePassword'
ALTER MASTER KEY ADD ENCRYPTION BY SERVICE MASTER KEY
GO

This is my favorite part. This little three line snippet took me 3 days to figure out. I found it in the comments of the website linked above. Here we are altering the master key with the service master key of the mirrored server.
-------------------------------------------------------------------------------------------------------------------

Now that we have all of the TDE stuff in place on both the principle and the mirror, we need to backup the principle db, and restore it, and then enable mirroring!

On Principle:
USE DatabaseName
GO
BACKUP DATABASE DatabaseName
TO DISK = 'Location\DatabaseName_FULL.bak'
          WITH COMPRESSION,
                    NAME = 'Full Backup of DatabaseName';
GO

--T-Log Backup of TDE
USE DatabaseName
GO
BACKUP LOG DatabaseName
TO DISK = 'Location\DatabaseName_LOG.trn'
          WITH COMPRESSION,
               NAME = 'Log backup of DatabaseName’
GO

The backing-up of the principle database is standard full backup and transaction log backup.
-------------------------------------------------------------------------------------------------------------------

On Mirror:
USE MASTER
OPEN MASTER KEY DECRYPTION BY PASSWORD = 'SomePassword'
RESTORE DATABASE DatabaseName
   FROM disk = 'Location\DatabaseName_FULL.bak'
      WITH NORECOVERY,
       REPLACE,
       MOVE 'LogicalDatabaseName' TO 'Location\DatabaseName.mdf',
      REPLACE,
      MOVE 'LogicalDatabaseLogName' TO 'Location\DatabaseName.ldf'
CLOSE MASTER KEY
GO

So when performing the restore make sure that you open the master key, then close it when done. If you don’t know the “logical” names of the files, right-click on the database name, then select “files”, look at the first column for the “logical” names of the .mdf and .ldf files. Also make sure that you specify “WITH NO RECOVERY”, because we want to restore the transaction log after this.
-------------------------------------------------------------------------------------------------------------------


On Mirror:
OPEN MASTER KEY DECRYPTION BY PASSWORD = 'SomePassword'
RESTORE LOG TDE
          FROM DISK = 'Location\DatabaseName_LOG.trn'
          WITH NORECOVERY;
CLOSE MASTER KEY
GO

For database mirroring to work, you must specify to restore the log backup “WITH NORECOVERY” the database has to be in a restoring state to initialize mirroring.
-------------------------------------------------------------------------------------------------------------------

Now on to the setting up of the database mirroring

On Principle/Mirror:
USE MASTER
SELECT * FROM sys.database_mirroring_endpoints
GO

This command will display the endpoints that are already on the server.
-------------------------------------------------------------------------------------------------------------------

On Principle/Mirror:
DROP ENDPOINT EndPointName

Issuing this command will drop the end point if need be.
-------------------------------------------------------------------------------------------------------------------

As a rule, I look for any existing endpoints to make sure that I am not duplicating any names.

On Principle/Mirror:
CREATE ENDPOINT EndPointName
          STATE = STARTED
          AS TCP ( LISTENER_PORT = PortNumber )
    FOR DATABASE_MIRRORING (
                   ROLE = PARTNER
                   );
GO

For my endpoint port number I use 7025 on the principle, and 7022 for the mirror.
-------------------------------------------------------------------------------------------------------------------
 
On Principle/Mirror:
SELECT dme.endpoint_id
,dme.name
,dme.principal_id
,dme.state_desc
,dme.role_desc
,dme.connection_auth_desc
,dme.certificate_id
,dme.encryption_algorithm_desc
,te.port
,te.ip_address
FROM sys.database_mirroring_endpoints dme
INNER JOIN sys.tcp_endpoints te
 ON dme.endpoint_id = te.endpoint_id AND te.type = 4
GO
This will check to make sure that the endpoints are started. This command was pulled from the comments section of the link posted above
-------------------------------------------------------------------------------------------------------------------

After both endpoints are created and started, it is time to issue the commands to start the mirroring.

On Mirror:
USE MASTER
OPEN MASTER KEY DECRYPTION BY PASSWORD = 'SomePassword'
ALTER DATABASE Databasename
SET PARTNER = 'TCP://PrincipleServerName.Domain:PortNumber'
GO
CLOSE MASTER KEY
GO

As a rule, I always issue the “start” code on the mirror to connect to the principle first. The connection has to be the FQDN, eg, server.domain.local etc… I am not sure if the open/close master key is needed, but it doesn’t error out.
-------------------------------------------------------------------------------------------------------------------

On Principle:
USE MASTER
ALTER DATABASE DatabaseName
SET PARTNER = 'TCP:// MirrorServerName.Domain:PortNumber'
GO

This command will start database mirroring on the principle.
-------------------------------------------------------------------------------------------------------------------


So, after issuing this code on the principle server, you should have TDE and Mirroring all setup. To check, refresh the database list in SSMS, the databases should read “Principle, Synchronized” and “Mirror, Synchronized/Restoring”

You can right click on the principle database, and choosing tasks-> mirroring monitor to check it out!

Monday, January 24, 2011

Sunday, Rainy Sunday

Got a call at Noon on Sunday. Went like this:
<boss>: <myname> I just got a call from <co-worker> we've got water in the suite, start shutting things down.
<me>: Will do, I'll log in now. Are you on your way to the office?
<boss>: Yup, I'll call you.
<me>: Okay.
10 minutes later
<boss> Yeah, we've got water, shut it down.
<me>: Doing it now, almost all off.
<boss> Okay. We've got standing water in the suite. Not good.
<me> Damn. How's our office?
<boss> Wet.
<me> Alright. See you in a bit


And from then on it was a cold shower. No, really. A pipe in one of our conference rooms, blew the sprinkler head off. Water was gushing down, water water every where, not a drop to drink.

Luckily our office is no longer the server room. So the bank of batteries that got wet have been off for 2 months. I got everything shutdown, thank God for VMWare ESXi. It was awesome to just log into the vCenter server and see all of my servers right there.

This morning wasn't too much of a hassle either. All of my users were back up and running at 9AM. There was still some cleanup to do of old equipment that was on the floor. Although we may have lost one of our new projectors it could've been worse.