Microsoft Azure- Create Geo Redundancy and Virtual Networks (VNet to VNet)

This is part 3 of the blog series on getting Started with Microsoft Azure.
Part 1: Microsoft Azure- Getting Started
Part 2: PowerShell for Microsoft Azure- Getting Started
Part 3: Microsoft Azure- Create Geo Redundancy and Virtual Networks (This post)
Part 4: PowerShell for Microsoft Azure- Creating Storage
Part 5: PowerShell for Microsoft Azure- Upload VHD Images (In Progress)
Part 6: PowerShell for Microsoft Azure- Create Machines (Coming soon)
Part 7: Active Directory and DNS in the Cloud and Azure AD (Coming Soon)

----------

At this point we have our Azure Tenant created, and the subscription named appropriately. We also have an idea of what data centers will be hosting Contoyso's data. If you have not looked at the first blog post, this is where we are for our data hosting.

Let's Get Started

To say that Microsoft's data centers are huge, is an understatement, as the data centers are built to host, essentially, shipping containers of racked storage of servers and hardware. The biggest problem moving forward, just like any on-premises data center, is how to minimize latency between servers. To keep servers as close together to reduce latency and increase performance, Azure uses a feature called Affinity Groups. Affinity Groups aggregates the compute and storage services not just to the same data center, but into the same server cluster and was assigned to specific physical pieces of hardware. 

In the beginning, you had to tie your network to an Affinity Group, but this has caused problems within Microsoft, as the Affinity Groups were tied to hardware, and when the hardware needed to be replaced, issues arose. So, Affinity Groups are no longer required to be tied to networks. You can still associate them, but we will not be going over that in this blog post. Also, if you have an existing Affinity Group associated with a network, it will eventually be migrated to a Regional vNET. For more information read About Regional VNets and Affinity Groups for Virtual Network.

Getting Your Local VPN  IP Gateway Address

Your network administrator should be able to give this to you, or if you wish, just Bing it... "What is my ip address" to get your external gateway address. If this is a demo environment and you do not have a fixed IP, this could be a problem because you could drop your Server to Server (S2S) VPN tunnel when you glean a new IP from your provider. If this happens, all you would need to do is update Azure with your new IP address and reconnect... If this is for your production environment and you have a dynamic IP address, you should really get a static IP. 

DOCUMENT YOUR GATEWAY IP ADDRESS

Creating Your Local Networks within Azure

Now is a good time to bring back out Excel and continue with the documentation of the on-premises and Azure infrastructure.

You can download the Excel file from http://1drv.ms/1xvEfcW
The first thing that needs to be accomplished is creating the Local Networks. From the left hand navigation, select  NETWORKS, and then select LOCAL NETWORKS from the top navigation.

Once you are on the LocalNetworks page, click ADD A LOCAL NETWORK. 

This will open a modal window to fill out. Hopefully you have already filled out your table and have all of the required information handy. For you first network, enter you on-premises information, with the VPN Device IP being your IP Address for your corporate gateway.

On the next page, enter your IP Address and CIDR information, and click the check to continue.

To add the next local network, click the NEW button at the bottom of the page and select ADD LOCAL NETWORK.

Create your other two local networks, and when you have completed your local networks, you should have a list of local networks similar to this:

At this point, the virtual network IP ranges have been created, but not all the IP Addresses have been added to the VPN Gateway Addresses for East US and West Europe. Before we can add our VPN Gateway information, the virtual networks will need to be created. The gateways IP addresses will be assigned with the creation of the virtual network.

Creating Virtual Networks within Azure

You can read Microsoft's marketing about virtual networks by going to  http://azure.microsoft.com/en-us/services/virtual-network/. But essentially, an Azure Virtual Network is the infrastructure to build out your cloud network environment within or to expand your on-premises network upon. It is your Infrastructure-as-a-Service (IaaS) and Platform-as-a-Service (PaaS) all rolled into one.

Create Your First Virtual Network

After creating the Local Networks within your Azure Tenant, it is time to create the Virtual Networks. Virtual networks are the networks within the Azure Data Centers. Since the on-premises network is already up and running, only networks that need to be created at this point are the East US and West Europe virtual networks. To get started, select the NETWORKS link in the left navigation followed by the VIRTUAL NETWORKS link in the upper navigation. This brings you to the virtualnetworks page.

Click on the CREATE A VIRTUAL NETWORK link to get started. This will open up a modal window, and fill out the information appropriately (look at the table you created earlier).

On the second page, select the Configure site-to-site VPN and select the on-premises local network. Since Contoyso is planning on extending the network into the cloud, Azure will need to know about the local DNS servers.

In Azure, DNS entries are set in a priority ranking, not in a round-robin. The first entry is the first DNS server queried, so plan appropriately. Later on we will be adding the DNS server information via the NetworkConfig.xml file as well.

On the third page, click the add gateway subnet button.

Click the check mark to finish.

After the first network has been completed, your virtualNetworks page should look like this:

At this point, we need to create our gateway and get the IP address before we create any of our other networks. To do this, click on the appropriate network, in this case the Azure East US network, and select DASHBOARD from the top navigation.

At this point, since a gateway has not been created, click the CREATE GATEWAY button at the bottom of the page.

This will bring up the option for either Dynamic or Static Routing... Select Dynamic routing as static routing is not supported for the Site-to-Site (S2S) VPN tunnel that is about to be created. Select Yes to create the gateway... You will notice that the gateway is now yellow, as it is now (being) created within Azure. The gateway will turn green when the S2S tunnel is not broken.

Gateway creation takes a fair amount of time to create, so go make a pot of coffee and come back in about 30 minutes. When the gateway has been created, add the gateway IP address to the Excel file.

Your dashboard should look like this:

and your Excel table should look like this:

After the gateway has been created, go ahead and click the CONNECT button at the bottom of the page. At this point, we have not created the RRAS connection on-premises so nothing will connect, but the connection has been activated within Azure for the time we get RRAS up and running. The final step is to add the IP address to the vNet-East settings on the localNetworks page, by clicking on LOCAL NETWORKS in the top navigation, selecting the vNet-East network, and clicking the EDIT button.

Enter your IP address on the first page:

On the second page, click the check to finish adding the IP address.

Create Your Second Virtual Network

It is now time to create the second virtual network. From the virtualNetworks page, click NEW to start the process of creating the second virtual network (West Europe).

On the first page, add the appropriate name from your Excel table, and appropriate location of the virtual network.

On the second page, select the Configure a site-to-site VPN, which will add a couple of pieces of information to fill out. For this blog post, we will not be using ExpressRoute, and from the LOCAL NETWORK drop down, select the network that was just created (vNet-East). Don't forget to add the on-premises DNS server information...

The final page will require you to create a gateway subnet, by clicking the add gateway subnet button before you finish creating your new virtual network.

And, just as before, it is time to create the gateway and enable the connection.

Click the new virtual network name, click on DASHBOARD from the top navigation, click the CREATE GATEWAY on the bottom of the page, and select Dynamic Routing. Then select Yes to create the gateway, and once again, find something else to do for the next 20-30 minutes...

After everything is created, click the CONNECT link at the bottom of the page. At this time the dashboard page looks like this: 

Grab the gateway IP address and add it to your Excel Table. Your Excel table should now be complete.

Now that all the gateway addresses have been created, update the  LOCAL NETWORK VPN Gateway Addresses.

In the big scheme of things this is where we are:

Add the Local Networks VPN Gateway Addresses

Click on the LOCAL NETWORKS link at the top of the Networks page. Select the East Network and click EDIT to add the East Network's Gateway Address. 

On the second page, click the check box to continue. Then, following the same procedures, add the Gateway IP address for the West Network. When you are finished with adding the Gateway IP information, your localNetworks page should look similar to this:

Connect the Networks

Now, this is where things get a bit not so fun. At this time, it is only possible to create one connection per gateway endpoint through the Azure GUI. So to get around the limitation, we will export a copy of the network as an XML file, modify it, and upload the modified file back into Azure to complete all of our network connections.

To start, go to the NETWORKS page and at the bottom of the page, click the EXPORT link. 

This will open a modal window that will ask for the subscription you wish to export (see my blog post: Microsoft Azure- Getting Started to create a friendly subscription name). Once you click the check box to continue, a download dialog box will open. Save the NetworkConfig.xml document someplace handy then make a copy of it... just in case you blow up your network.

You can also download the file via PowerShell.

Get-AzureVNetConfig -ExportToFile c:\scripts\vNetConfig.xml

Remember what networks connect to what? The East network connects to DC and West networks, while the West network connects to DC and East networks.

At this point we are going to crack open Visual Studio. Remember that we installed it in the last blog post, along with the Azure module for PowerShell. We reviewed the procedures to get both applications in the previous blog post,  PowerShell for Microsoft Azure- Getting Started.

Open the the NetworkConfig.xml in your favorite XML editor or Visual Studio.

Within the VirtualNetworkSites schema, we are interested in modifying the ConnectionsToLocalNetwork sections by adding the appropriate reference to the LocalNetworkSiteRef.

At this point, it is time to add the other Local Network to the gateway... Making sure that East connects to West and DC while West connects to East and DC.

While you have the XML file open, let's look at the DNS entries as well. As discusses earlier, that if you wish to have the Azure servers know about the on-premises servers, Azure will need to know about your on-premises DNS. First, look for the DNS server(s), and them make sure the the server names and IP addresses are correct.

At the top of the XML file, you can see that AD-02 (DNS server) is available within the VirtualNetworkConfiguration. Next, make sure that the DNS server(s) reference is associated with each network. For example, make sure that each virtual network has the AD-02 reference. If at a later time, you need to add more or edit the DNS servers, you will need to modify the XML. 

Make sure you save your file, before you try import it back into Azure. 

Now that the DNS entries have been verified, and the network is configured correctly, it is time to upload the file back into Azure. To upload the file using PowerShell, 

Set-AzureVNetConfig -ConfigurationPath c:\scripts\vNetConfig.xml

To use the GUI, click the new button at the bottom of the page and select NETWORK SERVICES --> VIRTUAL NETWORK --> IMPORT CONFIGURATION

and import your NetworkConfig.xml file. On the next page, Azure will show you the changes that will be happening to your network so that you can validate the changes.

This page shows what is to be created after the original networks get deleted.

Above, are the original networks that need to be deleted, before the networks can be recreated.

After the new networks have been created, click into the Dashboard page of one of the Networks and see that Gateway connections are still disconnected. This is because the Gateways have private keys, and the keys need to be exchanged to start the connections. To set the keys, you need to open PowerShell and log-in to your tenant. If you do not know how to use PowerShell with Azure, please review my previous blog post PowerShell for Microsoft Azure- Getting Started.

We will need to create two sets of matching keys for the East and West Virtual Networks to the appropriate local network gateways. 

$virtualNetworkEast = "Azure East US"
$virtualNetworkWest = "Azure West Europe"
$localNetworkEast = "vNet-East"
$localNetworkWest = "vNet-West"
$localNetworkOnPrem = "Contoyso-DC"
# Set key for East to West
Set-AzureVNetGatewayKey -VNetName  $virtualNetworkEast -LocalNetworkSiteName $localNetworkWest -SharedKey "lxJeyvLdcgPutInYourOwnKeys1Aaksw1SplbnyK7YH"
# Set key for East to On-Premises
Set-AzureVNetGatewayKey -VNetName $virtualNetworkEast -LocalNetworkSiteName $localNetworkOnPrem -SharedKey "lxJeyvLdcgPutInYourOwnKeys1Aaksw1SplbnyK7YH"
# Set key for West to East
Set-AzureVNetGatewayKey -VNetName $virtualNetworkWest -LocalNetworkSiteName $localNetworkEast  -SharedKey "lxJeyvLdcgPutInYourOwnKeys1Aaksw1SplbnyK7YH"
# Set key for West to On-Premises
Set-AzureVNetGatewayKey -VNetName $virtualNetworkWest -LocalNetworkSiteName $localNetworkOnPrem -SharedKey "lxJeyvLdcgPutInYourOwnKeys1Aaksw1SplbnyK7YH"

Create Your Server to Server VPN Tunnel

After a couple of minutes your Azure East and West local networks will have connected. Now the final, and probably the easiest, thing to accomplish is to get the tunnel established from on-premises to your East and West Gateways.

Luckily for us, Azure has made that very simple for us. If you go to the Dashboard of your East Network, on the right hand side is a link labeled Download VPN Device Script.

When you click the Download link, a modal window will pop up to select the correct type of configuration script that will need to be run on-premises. For this post we will be using Microsoft's RRAS on Server 2012R2.

Clicking the check box will pop up an Open or Save dialog window. From the Save menu, select Save as, and save the file to the c:\scripts folder. After the download has been completed, rename the file to East-S2S-VPN.ps1

Once again, please open up PowerShell ISE as administrator, and open the newly downloaded file East-S2S-VPN.ps1

If you run the file as is, the required roles and features will automatically be installed. The only thing that I am not happy about with this script is that the RRAS connection name uses the IP address, not the Network name for identification. So at this point, we will modify the script to make the connection name match the Network Name.

At the bottom of line 70, is the section to install the S2S VPN connection. Within this section of code, we will be replacing the name of your gateway IP address with the name of Network. Do not do a find and replace all, as we still need the IP address to connect to the gateway.

• In the Add-VpnS2SInterface replace the -Name parameter value with the name of your Network.
• In the Set-VpnS2Sinterface replace the -Name parameter value with the name of your Network.
• In both Set-PrivateProfileString locations replace the IP Address with the name of your Network.
• In the Connect-VpnS2SInterface replace the -Name parameter value with the name of your Network.

You will want to run the entire script on your RRAS server. I have never been able to run the entire script at once successfully. I suggest running the top 69 lines to install the RRAS roles and features first then running the configuration lines of the script one at a time.

After running the script on the RRAS server, go to you Routing and Remote Access Manager, to see if the VPN has connected yet. It could take several minutes to connect, and don't forget to refresh the Network Interfaces page or you could be waiting a lot longer. Your Network Interfaces page should look similar to this:

Repeat the same process for the West network. 

After the West network has been created your Network Interfaces page should look similar to this:

Now, if you go back into your Azure Networks page and go to the DASHBOARD page of your East Network, it should look similar to this:

While still on your DASHBOARD page, select the West Network, and it too should say connected with a couple of green checks:

Conclusion

At this point, you should now be able to have access to an East and a West Azure data center from your on-premises network. We have completed our goal to create a network that looks like this:

What's Next?

The next goal is to add Active Directory and DNS into the cloud. There is a bit of work that needs to be done to get there, such as setting up storage and cloud services. So stay tuned for the next blog post PowerShell for Microsoft Azure- Creating Storage (in progress)

-----

This is part 3 of the blog series on getting Started with Microsoft Azure.
Part 1: Microsoft Azure- Getting Started
Part 2: PowerShell for Microsoft Azure- Getting Started
Part 3: Microsoft Azure- Create Geo Redundancy and Virtual Networks (This post)

UPDATES
01/19/2015 Removed section on creating Affinity Groups, and added information on why they are not needed any longer. Also added the information for inputting the on-premises DNS information.

PowerShell for Microsoft Azure- Getting Started

This is part 2 of the blog series on getting Started with Microsoft Azure.
Part 1: Microsoft Azure- Getting Started
Part 2: PowerShell for Microsoft Azure- Getting Started  (This post)
Part 3: Microsoft Azure- Create Geo Redundancy and Virtual Networks
Part 4: PowerShell for Microsoft Azure- Creating Storage 
Part 5: PowerShell for Microsoft Azure- Upload VHD Images (In Progress)
Part 6: PowerShell for Microsoft Azure- Create Machines (Coming soon)
Part 7: Active Directory and DNS in the Cloud and Azure AD (Coming Soon)

----------

Out of the box, you have the ability control Azure via a GUI (through your browser) or through PowerShell. The good part is that both options are free. Having the ability to control your Azure environment through PowerShell is a pretty amazing feature, and continues to prove that Microsoft is heavily vested in PowerShell. If you thing that PowerShell is just a fad and you are waiting for it to go away so that you don't have to learn it, good luck!

Let's Get Started

The first thing that is required is to download the Azure module for PowerShell. Personally, I prefer to go through the Web Platform Installer (WPI). The current version of WPI is 5, and can be downloaded from http://www.microsoft.com/web/downloads/platform.aspx. Go ahead and run the installation and once WPI opens up, search for Microsoft Azure PowerShell. Depending on what you want to do, you may wish to install just PowerShell for Azure or you may choose to install the SDK as well. For this example, we are going to install Microsoft Azure PowerShell (Standalone) and Visual Studio Community 2013 with Microsoft Azure SDK- 2,5.

Open Up PowerShell ISE

For this demonstration, we will be using PowerShell ISE v5 on Windows 10 (beta). 

After opening ISE as Administrator, go to the commands add-on section and make sure that the Azure Module is installed.

Typically, when I work with PowerShell, the first thing that I will do is create a Scripts folder on a local drive to store my scripts, however we will be using the Scrips folder for storing passwords as well. 

Log In to Azure

There are several options for logging into Azure through PowerShell. However, once you have added your account into PowerShell, your credentials should stay. The most basic way to add your credentials to PowerShell is to use:

Add-AzureAccount

The problem is that this will open a windows login window will pop-up, and you enter your account name and password. This might actually be a good option for you if your Microsoft Account is the same as your O365 Account.

If you are not using a Microsoft Account to authenticate, another way to log-in, is to create a script that uses your stored username and password to authenticate. This will not work for @hotmail.com or @outlook.com accounts.

$userName = "pcBlogDemo@outlook.com"
$passwordLocation = "C:\Scripts\AzurePWString.txt"
function createPassword {
    Write-Host "Enter Password for $userName" -ForegroundColor Cyan
    Write-Host
    $PWInput = Read-Host -AsSecureString | ConvertFrom-SecureString
    $PWInput | Out-File $passwordLocation -Force
}
createPassword # Comment out this line after saving Password
$securePassword = Get-Content $passwordLocation | ConvertTo-SecureString
$cred = New-Object System.Management.Automation.PSCredential($userName, $securePassword)
Add-AzureAccount -Credential $cred

A final way is to download the Azure Publish Setting File, and log in with those credentials (certificate). There is a bit more work involved because you have to download and import the file first. Per Microsoft (http://msdn.microsoft.com/en-us/library/dn495224.aspx),"The publishsettings file contains the credentials (unencoded) that are used to administer your Windows Azure subscriptions and services. The security best practice for this file is to store it temporarily outside your source directories (for example in the Libraries\Documents folder), and then delete it after the settings have been imported. A malicious user gaining access to the publishsettings file can edit, create, and delete your Windows Azure services." To download the publishsettings file run:

Get-AzurePublishSettingsFile

This will open up a web page that will start a download and continue with instructions to finish importing the file. For this example, the document will be stored in the c:\Scripts folder.

After the file has been downloaded, run the Import-AzurePublishSettingsFile:

Import-AzurePublishSettingsFile "C:\scripts\Contoyso Azure-1-16-2015-credentials.publishsettings"

To validate the account running the PowerShell commands, run

Get-AzureAccount

If you have multiple Azure subscriptions, you will have to set the Azure subscription that you are working on, so that you do not accidentally do work in the wrong place.

Select-AzureSubscription -SubscriptionName "Contoyso Azure"

Conclusion

You have learned several ways (good and bad) to log into Azure. At this point you should now be able to log in and run PowerShell against your tenant. 

What's Next

Your options on what to do within Azure are truly limitless as long as you stay within your $200 free trail or within the budget of your organization. Most organizations start simple using Azure for High Availability and Disaster Recovery of AD and DNS services. So, the next steps would be to map out what data centers you plan on utilizing to host your servers and document a network class and IP range for each region. You can find information about data center locations at http://azure.microsoft.com/en-us/regions/

Geo Redundant Strategy and Subnets

Since Contoyso has determined that it will be using Azure for high availability and disaster recover for Active Directory and DNS, here is a map of the data centers chosen and IP ranges selected.. 

Having your mappings done ahead of time will make life easier when it comes to configuring your networks.

You can download the Excel file from http://1drv.ms/1xvEfcW

Stay tuned for the next blog post on creating geo-redundancy and virtual network in Azure (the picture above)...

Microsoft Azure- Getting Started

This is part 1 of the blog series on getting Started with Microsoft Azure.
Part 1: Microsoft Azure- Getting Started (this post)
Part 2: PowerShell for Microsoft Azure- Getting Started  
Part 3: Microsoft Azure- Create Geo Redundancy and Virtual Networks
Part 4: PowerShell for Microsoft Azure- Creating Storage 
Part 5: PowerShell for Microsoft Azure- Upload VHD Images (In Progress)
Part 6: PowerShell for Microsoft Azure- Create Machines (Coming soon)
Part 7: Active Directory and DNS in the Cloud and Azure AD (Coming Soon)

----------

Microsoft's cloud offering Azure, pronounced ˈaZHər, is a viable competitor to Amazon's Amazon Web Services (AWS). Azure's offerings and differences change way to quickly to actually keep this blog up to date. You can find out more about Azure here, and AWS' product offerings here. Microsoft also has a nice read on why they think you should use Azure over AWS, here.
There are many blog posts on getting started with Azure, but I figure one more cannot hurt, and hopefully you will have an easier time working within Azure after learning from my trials and tribulations.
This series of blog posts on Azure will be taken from the perspective of Contoso and Tail Spin Toys merging to form Contoyso, who, in the desire to ease the merge of the two IT networks, wants to move into a Hybrid environment.
This first post will be about setting up your Azure account and getting things set up so that managing your environment will be as easy as possible as your environment(s) grow.

Let's Get Started

The first thing you need to do have is either an Organizational account or a Microsoft account so that you can log into Azure. Try not to use an email address that is the same for both an Organizational Account and a Microsoft Account (eg: OU= jsmith@contoyso.com and Microsoft= jsmith@contoyso.com). You will need this account to create your subscription, and having an account that is in an OU and MS will make management through PowerShell a bit trickier (we will discuss this in a later blog post). To can create a free subscription within Azure go to http://azure.microsoft.com and click on the Free Trial link.
To sign up for the free trial, Microsoft does need a credit card, but will not bill your card unless you remove the preset spending limit (currently $200). You will also need to agree to the Windows Azure Agreement, Offer Details, and Privacy Statement. Once you click the Agree button, Microsoft will provision your subscription. It could take around 15 minutes to provision Azure subscription, and when your subscription has been created your subscription statement page will load:

Change Your Subscription Name

At this point, it is helpful to give your subscription a useful name. The reason being, that if you are handling more than one subscription with the same credential, then there is a good chance that you will have subscription names that are the same. For example, Enterprise subscriptions are all called Enterprise by default. To change the subscription name, click on the Edit subscription detail link and give your subscription a more descriptive name.

Notice that we are using a corporation name for our subscription name, but a Microsoft account as the service administrator. At this time Azure has no idea about the Contoyso corporate infrastructure, however, once the Contoyso on-premises AD is tied into Azure AD (later blog post) you can change the Service Administrator to a more appropriate user such as azureAdmin@contoyso.com.

Welcome to Azure

Once your account has been provisioned, your https://manage.windowsazure.com site should look like this:

By selecting the ALL ITEMS in the left menu, it will show you your Default Directory for your Azure Tenant. This is your Azure Active Directory, and is not to be confused with your domain active directory as they are 2 different directories at this point.

Validate Subscription Name

At the bottom of the left hand menu is the SETTINGS menu. By selecting the settings menu, you will be able to see all the subscriptions that you have available to the currently logged in account.

If your subscription name is still not what you are want, click on the current user account in the upper right hand corner and select, View my bill,

or you can go to https://account.windowsazure.com/Subscriptions. Once you are within Subscriptions page, click on the subscription name you wish to change. In this case, click on the Free Trial subscription.

This will pull up the subscription statement page, the same one from earlier in the blog. Click the Edit subscription details link, and modify your Subscription name, as described earlier. If you take a look at your subscription setting page, the summary title should have changed.

Once you have validated that the subscription name has change on the billing side, then your subscription name should have changed on the Azure Settings side as well.

What's Next

Before creating anything or setting up anything in Azure, the next step is to get the PowerShell module for Azure installed on a local machine so that you can log in to your Azure Tenant and get work done.

Please continue to the next blog post, PowerShell for Microsoft Azure- Getting Started to see how to install the PowerShell module for Azure and review a couple of ways to log in to Azure through PowerShell.

Run a PowerShell v3 Script From a SQL Server Agent Job

In a previous blog post, Synchronize Active Directory With SQL pt II, I mention creating a Scheduled Task to run the PowerShell script to keep AD synchronized to SQL. Then a colleague of mine, Don Kirkham (@DonKirkham) asked if it was possible to run a PowerShell script from within SQL. Well Don, the answer is "Yes", but there is a caveat... While SQL Server 2012 is running on Windows Server 2012, PowerShell for SQL is at version 2 and Windows Server 2012 is running PowerShell v3. We will address this issue later in the blog.

The other issue to be aware of is the warning from Microsoft, which says, "Each SQL Server Agent job step that runs PowerShell with the sqlps module launches a process which consumes approximately 20 MB of memory. Running large numbers of concurrent Windows PowerShell job steps can adversely impact performance."  So be careful not to choke your server.

Objective

Create a SQL Server Agent Job that runs a PowerShell script at a specific time and interval. In other words, a SQL job that mirrors the Scheduled Task created in my other blog post on how to Create a Scheduled Task With PowerShell.

PowerShell- SQL v2 vs Server v3 

As was just mentioned, PowerShell within SQL runs Version 2. If you were unaware that you could run PowerShell from within SQL Server Management Studio (SSMS), right-click on a folder, and select "Start PowerShell". Microsoft was nice enough to not make the option available for every folder, so for this blog, we are going to use the Jobs folder.

This opens up a SQL Server PowerShell window and if you type: 

$PSVersionTable

you will see that SQL 2012 PowerShell is running V2.

Here is where the problem begins. When you try to run the Sync AD to SQL script, it will error out, because the ActiveDirectory module is for PowerShell version 3, and when you try to import the module into version 2, your window will fill with RED.

Instead of hacking things up and having different versions of the Sync AD to SQL script, we are simply going to try to open a PowerShell v3 window and execute the script within the new window. The first thing we want to try is to actually see if we can open a new instance of a PowerShell v3 window from within out SQL v2 window. So within the SQL Server PowerShell window type:

Start-Process PowerShell

and in the new window, type:

$PSVersionTable

So now, all we need to do is tweak the script a bit to run the Sync AD to SQL script when the new v3 PowerShell window opens. The script that will be run is:

Start-Process PowerShell -ArgumentList "& 'C:\Scripts\SQL\Sync AD to SQL.ps1'"

Notice the single quotes around the location of the file that we are going to run.

Create The Accounts

Now that we have the proper syntax to get our v3 script run from within SQL, it is time to create the Job.

The nice thing about using PowerShell to create a Scheduled Task is the ease to create the RunAs account for the Scheduled Task. Within SQL, there are a couple of hoops that you will need to jump through to create the RunAs account for the Job.

The first thing that will need to be accomplished is to create a Credential to use as the RunAs account. Under the Security folder, right-click Credentials, and select New Credential...

Within the New Credential window, fill out the boxes appropriately:

or you can run a SQL Query to create it:

USE [master]
GO
CREATE CREDENTIAL [pcDemo\spAdmin] WITH IDENTITY = N'PCDEMO\spAdmin', SECRET = N'YourPassword'
GO

Next, we have to create a Proxy for the account to run a specific service. For this Job, we want the spAdmin account to run PowerShell, so we need to add that account as a Proxy. To accomplish this task, expand SQL Server Agent --> Proxies and right-click the PowerShell folder and select New Proxy...

Within the New Proxy Account window, fill out the boxes appropriately:

Or run a SQL Query to create the proxy:

USE [msdb]
GO
EXEC msdb.dbo.sp_add_proxy @proxy_name=N'PS spAdmin Proxy',@credential_name=N'pcDemo\spAdmin',@enabled=1
GO
EXEC msdb.dbo.sp_grant_proxy_to_subsystem @proxy_name=N'PS spAdmin Proxy', @subsystem_id=12
GO

Now that we have the Credential and the Proxy accounts created, the Job can be created.

Create The Job

From within SSMS connect to the SQL Server that is going to be running the Agent Job, and expand the object explorer to show the Jobs folder.

Right-click the Jobs folder and select: New Job...

This will open up the New Job window. Under the General page, you will want to give the Job a friendly name, and make sure that your Job is Enabled.

From within the Steps page, we will create our one and only step for this post, which will be to execute the Sync AD to SQL.ps1 files in a new PowerShell v3 window using the spAdmin Account.

At the bottom of the window click the New... button, which will open up yet another window, the New Job Window. 

• Step name: Something user friendly and descriptive
• Type: PowerShell
• Run as: Select the proxy account you created earlier.
• Command: 

  Start-Process PowerShell -ArgumentList "& 'C:\Scripts\SQL\Sync AD to SQL.ps1'"

Click OK when finished 

The Steps page should now look like this:

Next, click on the Schedules page, and click the New... button at the bottom of the Schedule List. This will open up a New Job Schedule window.

• Name: Add a user friendly name, such as Daily 2:00am Job
• Frequency: Change to Daily
• Daily Frequency
• Occurs once at: 2:00am
• Click OK when finished

Your Schedules page should look similar to this:

Click OK for the new job when finished. You can also run the following SQL query to create the same Job:

USE [msdb]
GO
DECLARE @jobId BINARY(16)
EXEC msdb.dbo.sp_add_job @job_name=N'Sync AD to SQL Job',
     @enabled=1,
     @notify_level_eventlog=0,
     @notify_level_email=2,
     @notify_level_netsend=2,
     @notify_level_page=2,
     @delete_level=0,
     @category_name=N'[Uncategorized (Local)]',
     @owner_login_name=N'PCDEMO\spAdmin', @job_id = @jobId OUTPUT
select @jobId
GO
EXEC msdb.dbo.sp_add_jobserver @job_name=N'Sync AD to SQL Job', @server_name = N'SQL2012-03'
GO
USE [msdb]
GO
EXEC msdb.dbo.sp_add_jobstep @job_name=N'Sync AD to SQL Job', @step_name=N'Run Sync AD to SQL PowerShell Script',
     @step_id=1,
     @cmdexec_success_code=0,
     @on_success_action=1,
     @on_fail_action=2,
     @retry_attempts=0,
     @retry_interval=0,
     @os_run_priority=0, @subsystem=N'PowerShell',
     @command=N'Start-Process PowerShell -ArgumentList "& ''C:\Scripts\SQL\Sync AD to SQL.ps1''"',
     @database_name=N'master',
     @flags=0,
     @proxy_name=N'PS spAdmin Proxy'
GO
USE [msdb]
GO
EXEC msdb.dbo.sp_update_job @job_name=N'Sync AD to SQL Job',
     @enabled=1,
     @start_step_id=1,
     @notify_level_eventlog=0,
     @notify_level_email=2,
     @notify_level_netsend=2,
     @notify_level_page=2,
     @delete_level=0,
     @description=N'',
     @category_name=N'[Uncategorized (Local)]',
     @owner_login_name=N'PCDEMO\spAdmin',
     @notify_email_operator_name=N'',
     @notify_netsend_operator_name=N'',
     @notify_page_operator_name=N''
GO
USE [msdb]
GO
DECLARE @schedule_id int
EXEC msdb.dbo.sp_add_jobschedule @job_name=N'Sync AD to SQL Job', @name=N'Daily 2:00am Job',
     @enabled=1,
     @freq_type=4,
     @freq_interval=1,
     @freq_subday_type=1,
     @freq_subday_interval=0,
     @freq_relative_interval=0,
     @freq_recurrence_factor=1,
     @active_start_date=20140101,
     @active_end_date=99991231,
     @active_start_time=20000,
     @active_end_time=235959, @schedule_id = @schedule_id OUTPUT
select @schedule_id
GO

Testing

After Refreshing the Object Explorer, you should now see the Sync AD to SQL Job in the Jobs folder. Right-click the job and select Start Job at Step...

Since there is only one step associated with the Job, so a new window will popup and your Job will start right away. Hopefully you will see a window that looks like this:

If not, SQL have a very nice way to view the history of the Job. If you right-click the Job name, and select View History, the Log File Viewer window will popup with the Job History for the appropriate Job already selected. If you expand the failed job, you will receive insight into why your Job as failed:

Conclusion

You should now be able to create a SQL Server Agent Job instead of a Scheduled Task to run a PowerShell script. Thank you again Don for the blog idea.

Create a Scheduled Task With PowerShell

Going into the GUI and creating a scheduled task is not rocket science, and now, either is creating a scheduled task via PowerShell. There is now a ScheduledTasks PowerShell module to help with automating this task. Below is a script for creating a Scheduled Task named Sync AD to SQL. The task runs daily at 6:00am and executes a PowerShell .ps1 file name Sync AD to SQL.ps1. This task is run as a specific domain user.

# Name of Task to create
$taskName = "Sync AD to SQL"
# Location of .PS1 file
$fileLocation = "C:\Scripts\SQL\Sync AD to SQL.ps1"
# UserName to run .PS1 file as
$user = "domain\userName"
# Password for above user
$password = "userPassword" 
# Create Task
$argument = "-Noninteractive -Noprofile -Command &'" + $fileLocation + "'"
$action = New-ScheduledTaskAction -Execute "PowerShell.exe" -Argument $argument  
$trigger = New-ScheduledTaskTrigger -Daily -At 6am  
$settings = New-ScheduledTaskSettingsSet  
$inputObject = New-ScheduledTask -Action $action -Trigger $trigger -Settings $settings 
Register-ScheduledTask -TaskName $taskName -InputObject $inputObject -User $user -Password $password

If you are not Running the Task as a different user, comment out before the -User as seen below:

Register-ScheduledTask -TaskName $taskName -InputObject $inputObject # -User $user -Password $password

There are a bunch of properties that can be tweaked when creating a scheduled task. Below are the links to the appropriate technet articles for each cmdlet used:
New-ScheduledTaskAction
New-ScheduledTaskTrigger
New-ScheduledTaskSettings
New-ScheduledTask
Register-ScheduledTask

Results:

Synchronize Active Directory With SQL pt II

One of the big complaints about querying Active Directory from within SQL is the lack of ability to easily return more that 1,000 items. In this post, we are going to solve that dilemma by throwing in some PowerShell to get around the Page Size limit. In this blog, I will demonstrate how to take 28,000+ users from Active Directory, put them into a SQL Table, and update the Production SQL Table. I will also go through some of the lessons learned from this experience, because it was not as easy as I had originally had hoped. Why synchronize AD to SQL? From a user manageability perspective, I think this is a great way to allow users to maintain their own information, such as their Address and Phone Numbers, or any corporate information without bothering HR or the IT staff to update AD profiles.
Objective
The objective of this post is to take all users from Active Directory, along with their lastLogon times, and synchronize them into a SQL table. This is going to be accomplished through the use of PowerShell and SQL.
Requirements
I have created this in PowerShell ISE version 3.0 on Windows Server 2012 and SQL Server 2012. The person running the script should have permissions to create and write into SQL, and read items from AD. You will need to have the SQLPS module and the ActiveDirectory modules installed. The SQL module should be installed when you install SSMS, and you can add the ActiveDirectory module by activating the feature through the Add Roles and Features Wizard:

You should also have a database and a table in place to save all of your Production User Data. I created a database name "pcDemo_Personnel" and this is the table that I will  use for this post:

USE [pcDemo_Personnel]
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
SET ANSI_PADDING ON
GO
CREATE TABLE [dbo].[pcDemo_SystemUsers](
     [RowID] [int] IDENTITY(1,1) NOT NULL,
     [UserLogin] [varchar](500) NOT NULL, 
     [Password] [varchar](200) NULL,
     [UserFullName] [varchar](500) NULL,
     [UserLastName] [varchar](1000) NULL,
     [UserFirstName] [varchar](1000) NULL,
     [UserCompany] [varchar](200) NULL,
     [UserOfficeLocation] [varchar](200) NULL,
     [Department] [varchar](50) NULL,
     [UserTitle] [varchar](500) NULL,
     [Manager] [varchar](500) NULL,
     [UserPhone] [varchar](200) NULL,
     [UserEmail] [varchar](150) NULL,
     [CreatedDate] [datetime] NULL,
     [System_Role] [varchar](50) NULL,
     [ReadOnly] [varchar](1) NULL,
     [lastLogon] [datetime] NULL,
     [userAccountControl] [varchar](50) NULL,
     [TwitterHandle] [varchar](50) NULL,
     [BlogAddress] [varchar](500) NULL,
     [FacebookPage] [varchar](500) NULL,
     [SSN] [varchar](12) NULL
) ON [PRIMARY]
GO
SET ANSI_PADDING OFF
GO
ALTER TABLE [dbo].[pcDemo_SystemUsers] ADD  CONSTRAINT [DF_SystemUsers_CreatedDate]  DEFAULT (getdate()) FOR [CreatedDate]
GO

Getting User Information from AD

To query Active Directory, we will be using the Get-ADUser cmdlet, and as we learned from my previous post, Sync Active Direcory to SQL, lastLogon time is stored on each Domain Controller so we will need to query each server to get each person's lastLogon time. We will query each server by creating an array of Domain Controller server names, and query each server in the array. For example, to query the first server in the array, you would use the following command which will return all properties for each user and put the returned data into the Variable $Users:

$Users = Get-ADUser -Filter * -Server $OUs[0] -Properties *

We will go through the -Filter and -Properties parameters later in this post.
Do not take querying your servers lightly. You should talk to your AD team BEFORE querying your domain controllers, and see if they have a preferred server that they wish you to query and/or specific times that they wish you to run your queries. Obviously, if they do not want you to query each server, then you will not be able to get accurate lastLogon times. Also, this information could be important to them as a health check of their servers.
Now that you have your User Information, we will be manipulating the information and passing it into SQL.
Putting User Information into SQL (Attempt #1)
As in the previous post, I started by creating a table in SQL called Temp_ADUsers.  Since we are now able to write a SQL query within PowerShell to insert information into SQL, I figured I would just loop through each individual user and insert them and their information into my table. Then I would query the second OU and update the table with each user's lastLogon time, and then the final server.
The SQL query looked like this (we will talk about the setLastLogon Function shortly):

foreach ($user in $Users)
{
   if ($user.SamAccountName -ne "Administrator" -and $user.SamAccountName -ne "Guest" -and $user.DisplayName.Length -gt 1)
   {
      $sAMAccountName = $user.sAMAccountName
      $firstName = $user.GivenName
      $lastName = $user.Surname
      $displayName = $user.displayName
      $company = $user.company
      $department = $user.department
      $title = $user.title
      $manager = $user.manager
      $telephoneNumber = $user.telephoneNumber
      $mail = $user.mail
      $userAccountControl = $user.userAccountControl
      $lastLogon = setLastLogon($user.lastLogon)
      $query1 = "INSERT INTO [dbo].[Temp_ADUsers]
      (
         [UserLogin]
        ,[FirstName]
        ,[LastName]
        ,[DisplayName]
        ,[UserAffiliation]
        ,[UserOrganization]
        ,[UserTitle]
        ,[Manager]
        ,[UserPhone]
        ,[UserEmail]
        ,[userAccountControl]
        ,[DC01_lastLogon_Raw]
      )
      VALUES
      (
         '$sAMAccountName'
        ,'$firstName'
        ,'$lastName'
        ,'$displayName'
        ,'$company'
        ,'$department'
        ,'$title'
        ,'$manager'
        ,'$telephoneNumber'
        ,'$mail'
        ,$userAccountControl
        ,'$lastLogon'
       )"
       Invoke-Sqlcmd -query $query1 -database "pcDemo_Personnel" -ServerInstance "sql2012-03"
   }
}

Within the script, I put some time tracking in an Out-File so that I could track the Get-ADUser query time (proof for the AD Team if needed), as well as the time it takes to insert/update SQL. My times returned were pretty horrible...

This method of running the insert and update SQL queries also brought up another issue:

Because there are people in Active Directory with single apostrophes in their name like Mr. O'Connor, I would have to come up with a work around for any property value that has an apostrophe in it. Luckily, the time to completion is so horrible that I figured solving for an apostrophe should be pretty simple and will address this issue at a later date.
If you recall, another issue with dealing with the lastLogin property is that the lastLogon time is "number of 100 nanosecond intervals since January 1, 1601 (UTC)" To solve this issue, I created a function with a foreach to take care of cleaning up the lastLogon time and converting it to a dateTime in UTC (Zulu):

foreach ($user in $users)
{
    if (!$user.lastLogon)
       {
          $user.lastLogon = 0
       }
    else
       {
          $user.lastLogon = [datetime]::FromFileTime($user.lastLogon).ToString('u')
       }
}

Putting User Information into SQL (Attempt #2)
The short lived Attempt #2 was based off a multiple item insert so that I could get rid of the foreach loop. It was a short lived idea because you can only bulk insert up to 1,000 items into SQL. Then I started looking into SQL's Bulk Insert Query, which led me to a question posed in StackOverflow, which led me to the Hey, Scripting Guy! Blog titled Use PowerShell to Collect Server Data and Write to SQL. After reading both articles, it became evident to me that every Server or Network Administrator now has the ability to take Enterprise Information and dump it into SQL. In my opinion, THIS IS HUGE!!!  Thank you again Chad Miller (@cmille19) for creating and sharing your SQL Functions!
Putting User Information into SQL (Attempt #3)
After reading through both blog posts mentioned above, I had an understanding of what needed to be done. I added the new functions to my script and commented out the foreach lastLogon cleanup loop to pass just raw data into the DataTable and into SQL. I gave it a run, and ran into an issue:

Taking a look at the errors, the columns not belonging in the table are found in the returned properties of the Get-ADUser cmdlet. The current query looks like this:

# Get AD Properties
$properties = ("sAMAccountName", "displayName", "mail", "telephoneNumber", "physicalDeliveryOfficeName", "department", "userAccountControl", "company", "title", "lastLogon", "manager", "givenName", "Surname")
# Get Users
$users = $null
$users = Get-ADUser -Filter * -Server $ouServer -Properties (foreach{$properties})

To get around this error, I piped the output into a new table:

$users = Get-ADUser -Filter * -Server $ouServer -Properties (foreach{$properties}) | Select (foreach{$properties})

Ran the script again and received a new error:

I un-commented out the foreach lastLogon cleanup loop and tried again. Things seemed to be running, so off I went for coffee, and when I returned, the script had run its course... cleanly. SQL looked like this:

Now it did not take me 3 hours to drink my coffee, so lets look at the actual run time:

Holy COW look at those numbers! This process saved 3 hours, 4 minutes, and 18 seconds!
Now that we are able to load User data into SQL, it is time to put the rest of the script together, like Drop the Temp Tables, Set the Get-ADUser -Filter, and move the data from the Temp Tables into the Master AD Users Table.
Dropping Existing SQL Tables
Because the functions that create the SQL data tables do not automatically check to see if the tables exist before creation, it is necessary to delete existing tables so that we can import the data from AD.

# Drop table if it exists
$query1 = "IF OBJECT_ID('dbo.$tableName', 'U') IS NOT NULL DROP TABLE dbo.$tableName"
Invoke-Sqlcmd -Query $query1 -Database $databaseName -ServerInstance $dbServer 

Filtering Out Unwanted Users
There are accounts within AD that you probably do not want to surface, synchronize, and expose their properties, such as the Administrator account, Service accounts, and the krbtgt account. The quickest way is to use the Filter parameter. Typically these accounts do not have displayNames, so filtering out empty values should return People. This may not be true for your organization, however, your organization should have some sort of governance over the naming structure of service accounts. My Get-ADUser cmd looks like this:

$users = Get-ADUser -Filter {displayName -like "*"} -Server $ouServer -Properties (foreach{$properties}) | Select (foreach{$properties})

Clean SQL Table from the 1st Domain Controller 
Since I had to put a zero in for null values for the lastLogon time, it is necessary to clean up the table. Luckily it is pretty easy to set all 0 values back to null.

$query2 = "UPDATE [dbo].$tableName SET lastLogon = NULL WHERE lastLogon = '0'"
Invoke-Sqlcmd -Query $query2 -Database $databaseName -ServerInstance $dbServer

Update Current Users in Production Table
Now that all data from all Domain Controllers has been returned, and placed into Temporary SQL tables, we can update the users' information in the Production Table:

$tempTableName = "temp_" + $OUs[0] + "_Table"
$query11 = "UPDATE active
            SET
               active.UserLogin = LOWER(temp.sAMAccountName),
               active.UserFullName = temp.displayName,
               active.UserLastName = temp.Surname,
               active.UserFirstName = temp.givenName,
               active.UserCompany = temp.company,
               active.UserOfficeLocation = temp.physicalDeliveryOfficeName
               active.UserTitle = temp.title
               active.Manager = temp.manager,
               active.UserPhone = temp.telephoneNumber,
               active.UserEmail = temp.mail,
               active.lastLogon = CONVERT(DATETIME, temp.lastLogon),
               active.userAccountControl = temp.userAccountControl
               active.Department = temp.department
            inner join " + $tempTableName + " temp
               on active.UserLogin = temp.sAMAccountName
            WHERE LOWER(active.UserLogin) = LOWER(temp.sAMAccountName)
Invoke-Sqlcmd -Query $query11 -Database $databaseName -ServerInstance $dbServer

Inserting New Users in Production Table
After updating current users, it it time to insert new users into the production table. I run the Update query first to save time, granted only by the number of new users  to be inserted, but time is time. This is how I add any new users into the Production Table:

$query12 = "INSERT INTO [" + $databaseName + "].[dbo].[" + $activeTableName + "]
(
   [UserLogin],
   [UserFullName],
   [UserLastName],
   [UserFirstName],
   [UserCompany],
   [UserOfficeLocation],
   [Department],
   [UserTitle],
   [Manager],
   [UserPhone],
   [UserEmail],
   [System_Role],
   [ReadOnly],
   [lastLogon],
   [userAccountControl]
)
   SELECTLOWER(sAMAccountName),
   [displayName],
   [givenName],
   [Surname],
   [company],
   [physicalDeliveryOfficeName],
   [department],
   [title],
   [manager],
   [telephoneNumber],
   [mail]
   [System_Role] = 'User',
   [ReadOnly] = 'Y',
   CONVERT(DATETIME, [lastLogon]),
   [userAccountControl]
FROM " + $tempTableName + " AS temp
WHERE sAMAccountName <> '' and not exists
(
   SELECT LOWER(UserLogin)
   FROM " + $activeTableName + " AS active
   WHERE LOWER(active.UserLogin) = LOWER(temp.sAMAccountName)
)"
Invoke-Sqlcmd -Query $query12 -Database $databaseName -ServerInstance $dbServer

Getting lastLogon From More Than 1 Domain Controller
For each Domain Controller, we are going to create a column in a new table called temp_lastLogonTimes to store the lastLogon times for each user, then we update the columns with that information:

foreach ($OU in $OUs)
{
   # Create OU Columns
   $columnName = $OU + "_lastLogon"
   $query5 = "ALTER TABLE temp_lastLogonTimes ADD " + $columnName + " varchar(1000)"
   Invoke-Sqlcmd -Query $query5 -Database $databaseName -ServerInstance $dbServer
}
# Insert and Update Times Into Temp Table
$counter = 0
foreach ($OU in $OUs)
{
   if ($counter -lt 1)
      {
      # Insert Names and Times
      $query6 = "INSERT INTO [dbo].[temp_lastLogonTimes]
                   ([sAMAccountName]
                    [" + $OU + "_lastLogon])
                 Select
                     sAMAccountName
                    ,lastLogon
                 FROM
                     temp_" + $OU + "_Table"
      Invoke-Sqlcmd -Query $query6 -Database $databaseName -ServerInstance $dbServer
      }
   # Update OU lastLogon Times
   $query7 = "UPDATE [dbo].[temp_lastLogonTimes]
              SET " + $OU + "_lastLogon = lastLogon
              FROM temp_" + $OU + "_Table
              WHERE temp_lastLogonTimes.sAMAccountName = temp_" + $OU + "_Table.sAMAccountName"
   Invoke-Sqlcmd -Query $query7 -Database $databaseName -ServerInstance $dbServer
   $counter ++
}

Now that the temp_lastLogonTimes table has the lastLogon times for each user from each Domain Controller, it is time to compare the times and find the true lastLogon time. To accomplish this in PowerShell, we will query the new temp_lastLogonTimes table, add a column for the lastLogon time to the temp_lastLogonTimes datatable, compare values, and update the row.

# Get Table and Update Last Logon Value
$str_OUs = @()
foreach ($OU in $OUs)
{
   $str_OUs += "ISNULL(" + $OU + "_lastLogon, 0) as " + $OU + "_lastLogon"
}
$str_OUs = $str_OUs -join ", "
$query8 = "SELECT sAMAccountName, " + $str_OUs + " from temp_lastLogonTimes"
$arrayLLT = @()
$arrayLLT = Invoke-Sqlcmd -Query $query8 -Database $databaseName -ServerInstance $dbServer
$arrayLLT | Add-Member -MemberType NoteProperty -Name "lastLogon" -Value ""
$arrayLength = $arrayLLT[0].Table.Columns.Count - 1
$counter = 0
foreach ($sAM in $arrayLLT.sAMAccountName
{
   $max = $arrayLLT[$counter][1..$arrayLength] | Measure -Maximum
   $arrayLLT[$counter].lastLogon = $max.Maximum
   $counter ++
}

Updating lastLogon Time in Production Table
Like before, we will take our datatable, create a final temp table in SQL, and move the data to the production table.

# Get DataTable
$arrayLLT = $arrayLLT | Select sAMAccountName, lastLogon
$dt2 = $arrayLLT | Out-DataTable
# Create SQL Table
Add-SqlTable -ServerInstance $dbServer -Database $databaseName -TableName $tableNameLLT -DataTable $dt2
# Write DataTable into SQL
Write-DataTable -ServerInstance $dbServer -Database $databaseName -TableName $tableNameLLT -Data $dt2
$query13 = "UPDATE [dbo].[" + $activeTableName + "]
            SET " + $activeTableName + ".lastLogon = temp_lastLogons.lastLogon
            FROM temp_lastLogons
            WHERE LOWER(temp_lastLogons.sAMAccountName) = LOWER(" + $activeTableName + ".UserLogin)"
Invoke-Sqlcmd -Query $query13 -Database $databaseName -ServerInstance $dbServer

Now that the solution is completed, let's look at the time to query 3 domain controllers:

 And if we take a look at the single DC query:

So based off the data above, there is a run time average of approximately 1.75 minutes per DC queried when dealing with 28782 users.
You can download the complete code from GitHub:
Create The Timer Job
Now that we can synchronize all users from AD into SQL, we need to create a scheduled task to run our PowerShell script to keep SQL synchronized on a regular basis. Now, this can be run daily or every couple of hours pending on how your organization works.
Please review my post on Creating a Scheduled Task With PowerShell.
However, what if the server Admin will not allow you to create a timer job using the Task Scheduler? Luckily, a colleague of mine, Don Kirham (@DonKirkham) posed a similar question on our company's Yammer site. Don wanted to know if it was possible to use SQL Server Agent jobs to run the job instead. The answer is, yes you can, but it is not as straight forward as one would like. You can read how to run this the Sync AD to SQL script from a SQL Server Agent Job from my blog post called Run a PowerShell v3 Script From a SQL Server Agent Job
UPDATES:
11/29/2013 Added link to Creating a Scheduled Task Blog, and added more kudos to Chad Miller for his contribution of the SQL Functions.
01/01/2014 Added link to blog post: Run a PowerShell v3 Script From a SQL Server Agent Job