Cloud Computing and the DBA

I have been told before that my head was in the clouds
when normal could not count the baud...
rate in my communication technique
and YES, cloud computing makes me scream EEK!

OK - I may never make it as a poet so I guess I am glad for the talents blessed upon me that I have honed into a successful career.

For years, we have put off, side-stepped, shoo-shooed, and managed to not give in to SaaS (Software as a Service).  However, with our 5 year HRIS disaster, we had to make some choices and the three major companies we looked at only offered HRIS and Recruitment as a SaaS / Web Portal Service.

Let me say, from an end user perspective,Cloud Computing was a fabulous choice.  The HR department is happy because they are actually getting applications and have a management software that works, our applicants are happy because the application process works, and I am happy because I don't spend an hour or more a day babysitting and massaging a server or a database.

But with every little bit of magic comes a price and that price, for a Database Administrator, is high.

WE ARE CONTROL FREAKS!

DBAs like to feel they are the Master's of their Universe and that is not lost on me.  I actually consider being a control freak a strength.  As a DBA, I want to ODBC, TelNet, Code Link into my databases.  I have a passion for SQL Management Studio. I like that when I put my credentials in - BAM - total uncontrolled access.

Oh - I don't care about what is inside the tables... with the integrity standards built into the front end, as long as it's correct - who cares... I just want an inter-connective world based on backend connectivity.  I want databases that can talk to other databases and give me what I want when I want it.

With cloud computing, the price is CONTROL.  You have lost it.  While you are paying for the service, the actual data, storage, and connectivity is no longer yours.

The books, text books, teach that the major concerns for cloud computing are 24/7/365 connectivity as you must have a live Internet connection to get to your interface and data and security.  You don't own or manage the servers and therefore you have to trust they have good people, policies, and security/redundancy structure in place.  What they fail to emphasize is data control for the DBA.

Report writing becomes whatever the software interface allows - often a rough pick list type wizard which can do only basic reports. SQL Views cannot be built, re-querying no longer exists, and the handcuffs have shackled your well-paid DBA.

HR data is used in other databases for us; Med Time Management, OIG, and Medical Access Signature databases.  We can no longer connect to the tables or build necessary views to feed these databases and have to rely on hand managed tables. So data integrity is now compromised.

Does Cloud Computing have a place?  YES!  For companies who do not have the ability to cover costs associated with managing software, web portals, servers, networks, etc.

Are SaaS products beneficial to companies that have end users with limited technical skills?  DEFINITELY! SaaS products allow even the most ignorant end user to tab through a form and enter data.

Are SaaS products going to meet all of your needs?  NO!  You are limited to what they are willing to give - which is not much outside of the scope of usage.

For a DBA, a control freak, someone who spent years integrating communication between databases to maximize efficiency and protect the integrity of the data, cloud computing is a disappointment and I want the shackles removed!

The Impact of the Internet on My Educational Career

The Internet has shaped our lives and society as a whole.  The concept, which originally began as an exchange of information for the furthering of education and defense development, has come to full vision where today I work from home, attend school, teach classes and shop for my family via the Internet. My geek hero is Claude Shannon, a man who worked for Ma Bell and created the interlacing topography for phone analogue computer communications.

My college educational pursuits began while I was still in high school in 1984, so I have truly seen and been affected by the immense growth of the Internet as both student and teacher having lived without the Internet and now having a career based upon the Internet.  My relationship with the Internet hasn't always been free from controversy, but overall, I am truly grateful for its existence.

My first interaction with the Internet was 1993. It was a slow dial-up modem connection with "online" text only type exchanges used to buy/sell.  The college I went to at the time did not have the technology that I had in my home as they did not have the personnel or connection hardware available. Websites were text based articles contained in rudimentary directories.

In 1995, just two years later and at a university, I had my real first taste of the Internet for educational use and an entire world was opened. Only the computers in the universities library and few computer labs had Internet connectivity and it wasn't even all of those.  In the library, out of 25 student computers, only five had dial-up modem capabilities. Even with such limitations, students were not discouraged.  Immediately, I could access the facts I needed from other university or government websites. No longer was I limited to relying on data months or even years old from printed materials, searching through card catalogs and carrying stacks of books.

The Internet and I did hit a rocky patch that year.  While researching a paper and harmlessly looking for facts on Unfunded Mandates pushed by the Federal government to State-level government, I visited a Department of Agriculture site and searched their directory for relevant materials. A few clicks further and I was in another part of the Federal governments site I was not supposed to be able to access and my attempt to get out resulted in university security appearing from nowhere. We had this great tool, but the drawback of securing that tool made it a risk.

In 1995, I could not see the vast growth and future of the Internet.  Two short years later, working at a software company, I could see the fruition of what we have today.  The Internet has impacted every aspect of my life:

• School - The Internet allows me to be able to attend classes, submit projects, do research and have any type of resource I need at my fingertips, take proctored exams, and have meetings with teachers and mentors from anywhere there is connectivity from almost any kind of electronic communication device such as phone, laptop, desktop, or tablet.
• Work - My office is four states away and now, through the Internet, I have a greater attendance rate, never miss a meeting, am immediately available to remote a machine, and have higher productivity than I did when I was working from an office.
• Communication - My oldest child lives in Germany and my youngest child is in diapers.  The Internet allows them to still know each other, talk and see each other, making live visits less traumatic.
• Teaching - I teach a couple of classes a year at a local community college.  The Internet allows my students to have almost anytime access to me through social media, instant messaging, and email.
• Basic Living - I live in a community that is remote and small yet I furnished my home, clothed my children, have access to every brand, size, and color no matter what the item thanks to the Internet. 

I will close with showing the greatest impact the Internet has made in my life.  In May 2010, I drove to my office at 7:45am and it was sunny and very bright outside.  Within an hour, we were under tornado watch and the skies had turned very dark.  Another 30 minutes, there was golf ball sized hail, winds of 60mph, and it was pitch black outside.  They moved me from my office to a central location in the building and with my laptop, continued to work until we lost electricity.  When the electricity returned, our servers did not come up and we had no inside/outside network connectivity.  I was "caught" by the Vice President playing solitaire and she was not impressed.  She told me to do my job as it would have been done before the Internet.  As respectful as possible, I stated, "Before the Internet, my job did not exist."

Creating Exchange Accounts in Powershell Remotely from a Database

So after I have a program written in VB that creates Active Directory accounts and the Exchange Email accounts for Exch2003, a new twist gets thrown in with the Exchange 2010 Upgrade.

What they don’t tell you:

• You cannot programmatically create an Exchange account in any other language than PowerShell.
• Exchange 2010 Server comes with both PowerShell and PowerShell for Exchange – they are two separate programs, do not share the same commands or the same remote connectivity criteria.
• The PowerShell script must run on the Exchange box remotely but using the tools in PowerShell for Exchange – which can be a little tricky.

There is a GREAT article for getting you through the hoops of just allowing your Application Server to talk to the Exchange Server:

http://www.binarytree.com/Blog/Blog/April-2011/Provisioning-Exchange-2010-Users.aspx

However, I disagree with Rob on his last two steps. The first is how he handles the execution policy.

set-executionpolicy remotesigned

I use:

Set-executionpolicy -scope currentuser –executionpolicy bypass –force

As a programmer and data administrator, I do not want to have to sign script every time I run it or create a formally signed .ps1 file for every new hire.

The second is the actual connection (which I am setting as a variable):

$Session = Enter-PSSession -ConnectionURI (http://CAS Server Hostname/powershell) -ConfigurationName Microsoft.Exchange


Getting this far still doesn’t get you to the ballgame. You will make the connection and have no functionality with Exchange script. It’s misleading.

One very important line of code is this:
Import-PSSession $Session


Now – you can use PowerShell commands remotely to create the necessary accounts.

My code from the VB program – once you have the Active Directory account set up:

Dim testTarget1 As String
Dim PSScript2 As String
Dim FileNum As Integer

testTarget1 = Dir("c:\scripts\NewMailbox.ps1")
If Not testTarget1 = "" Then
testTarget1 = "c:\scripts\NewMailbox.ps1"
Kill testTarget1
End If

testTarget1 = "c:\scripts\NewMailbox.ps1"

FileNum = FreeFile()

PSScript2 = "$Session = New-PSSession -ConfigurationName Microsoft.Exchange -ConnectionUri http://exch2010/PowerShell/" & vbNewLine
PSScript2 = PSScript2 & "Import-PSSession $Session" & vbNewLine
PSScript2 = PSScript2 & "Enable-Mailbox -identity '" & MyName & "'" & vbNewLine
PSScript2 = PSScript2 & "Set-Mailbox -Identity '" & MyName & "' -ApplyMandatoryProperties"

'Create File on Exch
Open testTarget1 For Append As #FileNum
Print #FileNum, PSScript2
Close #FileNum

'Call Powershell and run connection
Call Shell("powershell -file ""c:\scripts\NewMailbox.ps1""", vbMaximizedFocus)

Part I – Using VBA to create an Active Directory/Exch Email Account

This pre-install process is for Server/RDP (Windows Server 2003/2008 x64 bit) based

access where the IS/IT component is a generic Domain Admin account (“Admin”) for a

2003 Exchange Server connection. This is a necessity when creating the "bridge" to talk to an Exchange Server that also hosts Active Directory.

• Requires the CDOEXM for Microsoft Exchange Server. If you are running Exchange 2003, download file from this website (it’s OK that it says for Vista – we can get around that)

http://www.microsoft.com/download/en/details.aspx?displaylang=en&id=17836

Exchange 2010 users – there is a new download available here:

http://www.microsoft.com/download/en/details.aspx?displaylang=en&id=1004

• Unzip and move the ESMVISTA.EXE and ESMVISTA.MSI files to your

C:\Documents and Settings\Login_Name\My Documents folder

• At a command line on the server, type the following to get past the Vista detection warning which comes with this .MSI package:

msiexec /i esmvista.msi /q

• The files will install in the C:\Program Files(x86)\exchsrvr\bin folder. If running a 32-bit server – you are done.
  
  
• For 64-bit server – Copy all DLLs from C:\Program Files(x86)\exchsrvr\bin to C:\Windows\syswow64\ DO NOT overwrite existing libraries!
  
  
  
• Hand register your libraries at a command line
• Regsvr32 c:\windows\syswow64\cdoexm.dll
• Regsvr32 c:\windows\syswow64\exadmin.dl

Talking Server to Server in Powershell – Remote Scripting

First – let me say this is not just Server to Server but also Desktop/Laptop to Server –

wherever you have PS capabilities.

A couple of things have to be considered before you attempt to force script to run on a

remote machine:

1. You have permissions on the remote machine to run script

2. You have security permissions on the remote machine to make the changes

After that, it’s a simple line of code from the Powershell command line:

Invoke-Command –ComputerName MyComputerName –FilePath C:\FileLocation\MyFile.ps1

MyComputerName = Name of remote machine

C:\FileLocation\MyFile.ps1 = The local path of the PS file from the machine you a

Running Powershell Script without the Headache of Signing Scripts

Powershell can have a direct effect on Windows and therefore has some built in security.

This security can be a real PAIN IN THE BUTT when it comes to just wanting your
script to run.

You will see a warning :

File C:\scripts\test.ps1 cannot be loaded because the execution of
scripts is disabled on this system. Please see "gethelp about_signing" for more details.

Now, to create your own signature certificate is a headache all unto itself.

This is how you can get around it:

At a Powershell command line type: Get-ExecutionPolicy

Possible return values are: Restricted, Unrestricted, RemoteSigned, AllSigned, Bypass

To run Powershell script without a signature requirement, the Execution Policy must be
set to Bypass.

You could type Set-ExecutionPolicy bypass, but chances are it will squawk at that and
tell you that changes could not be made to the registry.

1. Login to the machine where your script will run. (If on a server, you may want to
use a domain account)

2. Open Powershell (Start/Accessories/WindowsPowershell)

3. Type at the command line:
Set-ExecutionPolicy –scope CurrentUser –executionpolicy bypass –force

This command allows you to force the Execution Policy to Bypass for any code the User wants to run.