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.

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.

Steps taken and scripts written were extrapolated from http://blogs.msdn.com/b/sqlserverfaq/archive/2009/03/31/setting-up-database-mirroring-in-sql-server-2008-using-t-sql-when-the-database-is-encrypted-using-transparent-data-encryption.aspx

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!

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.

Replicating a View from MSSQL to MYSQL part II

The stored procs are basically the same as when they were used for table to table replication.

One of the stored proc that we need to change is the ‘sp_addarticle’:

exec sp_addarticle @publication = N’repl_test_view’

, @article = N'repl_test_view'

, @source_owner = N'dbo'

, @source_object = N'repl_test_view'

, @type = N'indexed view logbased'

, @pre_creation_cmd = N'none'

, @ins_cmd = N'SQL'

, @del_cmd = N'SQL'

, @upd_cmd = N'SQL'

, @schema_option =0x8000000

, @status = 24

As you can see the major difference is at the @type variable, whereas we chose ‘logbased’ for the table to table replication. For indexed view replication it should say ‘indexed view logbased’, which really only makes sense.  I also had to change the @schema_option because we wanted to replicate a view, not a table.

I also added the stored proc of ‘sp_replicationdboption’

exec sp_replicationdboption @dbname= N'databasename'

, @optname = N'publish'

, @value = N'true'

GO

Easy enough, this saves the step of having to tell the MSSQL server that the database is indeed eligible for replication via the GUI.

Since I have not figured out a way for the initial replication to create the destination tables (I haven’t looked into it yet), I was forced to create the MYSQL tables to accept the replicated data. This brought me to another point of having to change the ‘datetime’ datatype from MSSQL to a ‘smalldatetime’ so that I would be able to then replicate over to MYSQL. 

For example, if I had a column named “date_turned_in”, and I wanted that as a ‘smalldatetime’ instead of the normal ‘datetime’, I would use CAST(table.date_turned_in AS smalldatetime) AS date_turned_in, I had to change the data types before replication, since the “allow data transformation” is set to false for the publication.

After all this was said and done, we are now replicating five views from a MSSQL server to 5 tables in a MYSQL server.

I am sure that there is more to come.

Replicating a View from MSSQL to MYSQL

     So what it took me to figure out MSSQL to MYSQL replication, we’re not going to use it, per se. Our goal was to condense a multidude of tables down to a single view, then replicate that view from our MSSQL server to a single table on the MYSQL side (which you can replicate a view). However the hitch in the giddy-up was the constraints of the view. The highlights were; it must have a non-clustered unique index, a view cannot contain an “outer join” (crap). There is a host of critera to make an index view in the first place so once we were past that we thought it was clear sailing. Wrong.

So here we sit, the minimal number of views that we can break the data down to, with holding to the constraints of the view is about 4-5. It looks like we will have to replicate the views as tables into MYSQL, then have the website condense them down to display on the page.

It’s not bad, just not what we wanted.

One way transactional replication from MSSQL 2005 to MYSQL 5.x

At my place of work, we ran into what should be a normal problem. We use mssql 2005 for our internal operations, however we use mysql for our database backend. We wanted to update the changes that happened on the mssql server up to the mysql server so that the website would reflect the changes.

**This how-to only applies to Windows XP, MSSQL Server 2005, MYSQL 5.1.x and MYSQL ODBC connecter 5.1**
You must first allow the account that you will be using for the connection access to the mysql database. Since I was only testing, this is the command I issued on the Linux box that is hosting the MYSQL server

mysql> GRANT ALL PRIVILEGES ON *.* to 'username@windowsmachinename.domain' IDENTIFIED BY 'password';

So if I was using "root" as the username, "lemon" as the machine name and "orange" as the domain name, with the password of "home1234" it would look like this: 

mysql> GRANT ALL PRIVILEGES ON *.* to root@lemon.orange.local IDENTIFIED BY 'home1234';

Once that is done, then you must visit the mysql ODBC driver website and download the correct driver for the MSSQL server that  you are using.

After installing the driver, you must open up the ODBC connections on your machine. Once there, you must add the MYSQL ODBC driver as a system dsn.
My configuration is shown below for the MYSQL ODBC connector.

After you have successfully tested that the connection is valid, open up SSMS, connect to the database engine. Once you are connected, expand the "replication", right-click on "Local Publications", select "Publisher Properties". Once that window is up click on "Publication Databases", then select the requested database as transactional.

Expand "linked servers", then expand providers, right click on "MSDASQL", in the pop-up, select the choices: Nested queries, level zero only, allow inprocess, supports 'like' operator

After that, fire up a new query window and copy/paste the below code.

The steps below
--step 1
-- Adding the transactional publication
use [repl_test]
exec sp_addpublication @publication = N'Repl_test'
, @description = N'Transactional publication of database'
, @sync_method = N'concurrent_c'
, @retention = 0
, @allow_push = N'true'
, @allow_pull = N'false'
, @allow_anonymous = N'true'
, @enabled_for_internet = N'false'
, @snapshot_in_defaultfolder = N'true'
, @compress_snapshot = N'false'
, @ftp_port = 21
, @allow_subscription_copy = N'false'
, @add_to_active_directory = N'false'
, @repl_freq = N'continuous'
, @status = N'active'
, @independent_agent = N'true'
, @immediate_sync = N'true'
, @allow_sync_tran = N'false'
, @allow_queued_tran = N'false'
, @allow_dts = N'false'
, @replicate_ddl = 0
, @allow_initialize_from_backup = N'false'
, @enabled_for_p2p = N'false'
, @enabled_for_het_sub = N'true'
, @autogen_sync_procs = 'false'
GO
--add the article to the publication
exec sp_addarticle @publication = N'Repl_test'
, @article = N'TestTable'
, @source_owner = N'dbo'
, @source_object = N'TestTable'
, @type = N'logbased'
, @pre_creation_cmd = N'none'
, @ins_cmd = N'SQL'
, @del_cmd = N'SQL'
, @upd_cmd = N'SQL'
, @schema_option = 0x20025081
, @status = 24
GO

--add all of the columns to the article
exec sp_articlecolumn @publication = N'Repl_test'
, @article = N'TestTable'
, @refresh_synctran_procs = 1
GO

--end step1

--step2
--add the publication snaphot
exec sp_addpublication_snapshot @publication = N'Repl_test'
, @frequency_type = 4
, @frequency_interval = 4
, @frequency_relative_interval = 1
, @frequency_recurrence_factor = 0
, @frequency_subday = 4
, @frequency_subday_interval = 1
, @active_start_time_of_day = 0
, @active_end_time_of_day = 235959
, @active_start_date = 0
, @active_end_date = 0
, @job_login = null
, @job_password = null
, @publisher_security_mode = 1
GO
--end step2

--step3
--add the subscriber(s)
use [repl_test]
exec sp_addsubscription @publication = N'Repl_test'
, @subscriber = N'mysqltest'
, @destination_db = N'repl_test'
, @subscription_type = N'Push'
, @sync_type = N'automatic'
, @article = N'all'
, @update_mode = N'read only'
, @subscriber_type = 3
GO

--add the pushing subscription agent
exec sp_addpushsubscription_agent @publication = N'Repl_test'
, @subscriber = N'mysqltest'
, @subscriber_db = N'repl_test'
, @job_login = null
, @job_password = null
, @subscriber_security_mode = 0
, @subscriber_login = N'root'
, @subscriber_password = 'PASSWORD'
, @subscriber_provider = N'MSDASQL'
, @subscriber_datasrc = N'mysqltest'
, @frequency_type = 64
, @frequency_interval = 1
, @frequency_relative_interval = 0
, @frequency_recurrence_factor = 0
, @frequency_subday = 0
, @frequency_subday_interval = 0
, @active_start_time_of_day = 0
, @active_end_time_of_day = 235959
, @active_start_date = 20101202
, @active_end_date = 99991231
, @enabled_for_syncmgr = N'False'
, @dts_package_location = N'Distributor'
GO
--end step3

A brief breakdown of the arguments is as follows, I'll only be touching on the ones that I think are big.

@publication is the name that you want to use for the publication, must be the same throughout the stored procs.

In sp_addpublication
@sync_method should be concurrent_c, that will get  transactional replication
@allow_push must be true in order for the push subscription to be true
@enable_for_het_sub must be set to true, enable for heterogeneous subscriber

In sp_addarticle
@type must be logbased if you want transactional repl (which we do as we set the @sync_method to concurrent_c)
@pre_creation_cmd must be set to none. If this is not set to none, replication will drop the mysql table, then error out stating "no such table"
@schema_option You will have to look at the BoL for the various options, use google to add them together to get the final product.
The 3 command variables we just want to mirror the SQL commands

In sp_articlecolumn
Not sure if this is actually needed or not :)

In sp_addpublication_snaphot
These should be set to your requirements.

In sp_addsubscription
@subscriber should be the system dsn that you set up, at the start of this page
@update_mode should be set to read only, that ensures one-way replication from MSSQL to MYSQL
@subscriber_type should be set to 3(OLE DB) I have not tried it set to 1(ODBC)

In sp_addpushsubscription
@subscriber should be the system dsn again
@subscriber_login should be set to the login from the "grant all privileges" step up above
@subscriber_provider should be MSDASQL
@subscriber_datasrc should be set to the system dsn  again
@enable_for_syncmgr set to false
@dts_package_location set to Distributor since this is a push replication, everything is run from the publisher/distributor


*** Please this is what worked on my box, if it doesn't work, you can try posting here and we may be able to work it out. ***
Please check out BoL for the full list of options for all of the stored procs
If you are on twitter, try the hashtag #sqlhelp
Try posting to ServerFault