Abbreviations for SQL Server Components when Installing with PowerShell

I have (finally) been introduced to installing SQL Server with PowerShell and it has made those installations much easier.  Each of my installs; however, may require different SQL Server components so one of the items in my configuration file I change is the Features= section of the INI file.  To help keep all this information in one place, here is a list of items you can choose to install.  Remember, most of these will require other parameters to be set–this is only for the Features= section.  Each feature should be separated by a comma.

SQLInstalFeatureNames

SQL Server Component Install Options

This list specifies the items you can choose to install.

Database engine = SQLENGINE
Replication = REPLICATION
Full-text and semantic extractions for search = FULLTEXT
Data quality services = DQ
Analysis services = AS
Reporting services – native = RS
Reporting services – sharepoint = RS_SHP
Reporting services add-in for sharepoint products = RS_SHPWFE
Data quality client = DQC
SQL Server data tools = BIDS
Client tools connectivity = CONN
Integration services = IS
Client tools backwards compatibility = BC
Client tools SDK = SDK
Documentation components = BOL
Management tools – basic = SSMS
Management tools – advanced = ADV_SSMS
Distributed replay controller = DREPLAY_CTLR
Distributed replay client = DREPLAY_CLT
SQL client connectivity SDK = SNAC_SDK
Master data services = MDS

Happy installing. :)

Updating Default Values for Columns

To make life a little simpler, a default value can be added to a column so if the information is not passed with the row data, SQL Server can plug in some information for you.  A common scenario is to add a default DateTime of the current system time.  This image show what that looks like when you design the table in SSMS.

DefaultBinding

What happens if you wanted to change the default value option for those tables?  For example, we are preparing to move to Azure SQL and our default options need to be GETUTCDATE() instead of GETDATE().  I put together this script to help me identify all the constraints in the database that need to be updated. This code has the new lines so you should be able to run the output. Don’t forget to update your data before you made the change. You can find that example here.

[sourcecode language=”SQL”]
SELECT
‘ALTER TABLE ‘ + t.name + ‘ DROP CONSTRAINT ‘ + dc.name + ‘
GO
‘ + ‘ALTER TABLE ‘ + t.name + ‘ ADD CONSTRAINT ‘ + dc.name + ‘ DEFAULT GETUTCDATE() FOR ‘ + c.name + ‘
GO’
FROM sys.default_constraints dc
INNER JOIN sys.tables t ON dc.parent_object_id = t.object_id
INNER JOIN sys.columns c ON t.object_id = c.object_id
WHERE dc.parent_column_id = c.column_id
AND dc.definition = ‘(getdate())’
[/sourcecode]

Converting to UTC Time

In preparation for a migration to Azure SQL Database, we found we had to adjust our date formats to UTC time as this is the way dates are stored in azure because your databases could be all over the world.  For new systems, this is very useful; however, what about migrating data to Azure?  I have tables with dates based on a GETDATE() default and this time will be the time zone for the server where the record was created.  I wanted to convert my dates to UTC time and came up with this script.  It is based on servers in the EASTERN time zone.  As the date for daylight savings time changes each year, I used timeanddate.com for the historical data.

Suppose I have a table named Customers with a column called CreationDate and I wanted to update my times to UTC. I would apply this logic. As always, test before implementation. I put an ELSE statement at the end so I could check to see if any dates fell outside my range. This only goes back to 2009. You would have to consult timeanddate.com for more dates if you have older data.

[sourcecode language=”sql”]
UPDATE Customers
SET CreationDate = CASE
WHEN YEAR(CreationDate) = 2009 AND CreationDate BETWEEN ‘2009-03-08 02:00:00.0000000′ AND ‘2010-11-01 02:00:00.0000000′ THEN DATEADD(hh,6,CreationDate)
WHEN YEAR(CreationDate) = 2009 AND CreationDate NOT BETWEEN ‘2009-03-08 02:00:00.0000000′ AND ‘2010-11-01 02:00:00.0000000′ THEN DATEADD(hh,5,CreationDate)
WHEN YEAR(CreationDate) = 2010 AND CreationDate BETWEEN ‘2010-03-14 02:00:00.0000000′ AND ‘2010-11-07 02:00:00.0000000′ THEN DATEADD(hh,6,CreationDate)
WHEN YEAR(CreationDate) = 2010 AND CreationDate NOT BETWEEN ‘2010-03-14 02:00:00.0000000′ AND ‘2010-11-07 02:00:00.0000000′ THEN DATEADD(hh,5,CreationDate)
WHEN YEAR(CreationDate) = 2011 AND CreationDate BETWEEN ‘2011-03-13 02:00:00.0000000′ AND ‘2011-11-06 02:00:00.0000000′ THEN DATEADD(hh,6,CreationDate)
WHEN YEAR(CreationDate) = 2011 AND CreationDate NOT BETWEEN ‘2011-03-13 02:00:00.0000000′ AND ‘2011-11-06 02:00:00.0000000′ THEN DATEADD(hh,5,CreationDate)
WHEN YEAR(CreationDate) = 2012 AND CreationDate BETWEEN ‘2012-03-11 02:00:00.0000000′ AND ‘2012-11-04 02:00:00.0000000′ THEN DATEADD(hh,6,CreationDate)
WHEN YEAR(CreationDate) = 2012 AND CreationDate NOT BETWEEN ‘2012-03-11 02:00:00.0000000′ AND ‘2012-11-04 02:00:00.0000000′ THEN DATEADD(hh,5,CreationDate)
WHEN YEAR(CreationDate) = 2013 AND CreationDate BETWEEN ‘2013-03-10 02:00:00.0000000′ AND ‘2013-11-04 02:00:00.0000000′ THEN DATEADD(hh,6,CreationDate)
WHEN YEAR(CreationDate) = 2013 AND CreationDate NOT BETWEEN ‘2013-03-10 02:00:00.0000000′ AND ‘2013-11-04 02:00:00.0000000′ THEN DATEADD(hh,5,CreationDate)
WHEN YEAR(CreationDate) = 2014 AND CreationDate BETWEEN ‘2014-03-09 02:00:00.0000000′ AND ‘2014-11-02 02:00:00.0000000′ THEN DATEADD(hh,6,CreationDate)
WHEN YEAR(CreationDate) = 2014 AND CreationDate NOT BETWEEN ‘2014-03-14 02:00:00.0000000′ AND ‘2014-11-02 02:00:00.0000000′ THEN DATEADD(hh,5,CreationDate)
WHEN YEAR(CreationDate) = 2015 AND CreationDate BETWEEN ‘2015-03-08 02:00:00.0000000′ AND ‘2015-11-01 02:00:00.0000000′ THEN DATEADD(hh,6,CreationDate)
WHEN YEAR(CreationDate) = 2015 AND CreationDate NOT BETWEEN ‘2015-03-08 02:00:00.0000000′ AND ‘2015-11-01 02:00:00.0000000′ THEN DATEADD(hh,5,CreationDate)
WHEN YEAR(CreationDate) = 2016 AND CreationDate BETWEEN ‘2016-03-13 02:00:00.0000000′ AND ‘2016-11-06 02:00:00.0000000′ THEN DATEADD(hh,6,CreationDate)
WHEN YEAR(CreationDate) = 2016 AND CreationDate NOT BETWEEN ‘2016-03-13 02:00:00.0000000′ AND ‘2016-11-06 02:00:00.0000000′ THEN DATEADD(hh,5,CreationDate)
ELSE ‘2046-01-01 11:07:20.6500000′
END
FROM Customers
[/sourcecode]

Granting SSIS Package Administration Access to Developers

In many instances there is a development team or even a warehouse team that needs to run SSIS packages and you want to store those packages in SQL Server and run them through the SQL Agent Service.  You may not; however, want to grant those users/group sysadmin rights to the machine.  The following describes my experience in setting this up.

SSIS_DB1First, you must add the user/group to the DCOM Configuration for “Microsoft SQL Server Integration Services 11.0″ (SQL 2012), which can be found in the Component Services MMC.  Right click and choose properties.

 

 

SSIS_DB2You can now add the groups to two security sections–“Launch and Activation Permissions” and “Access Permissions”.  It was my experience I had to have them in both groups.  I granted full rights in each group.

The final component is to add each user/group to the local group “Distributed COM Users”.  It seems like it won’t even authenticate a user for the groups you set earlier unless they are in the local group.  This will allow users to connect the Integration Services Instance.SSIS_DB3

At this point you should restart the Integration Service services.

If you haven’t already, you can go ahead and create your Catalog Database.  We won’t go over those steps here–perhaps in another post.

The last step is to allow them to deploy and manage the packages on the servers.  I add the group to the SSISDB and grant the role of ssis_admin.  Now your teams should be able to function completely within the SSIS parameters and not have permissions to drop database objects or modify security settings.

SSIS_DB4

The Local Node is Not Able to Communicate With the WSFC Cluster

I was setting up an availability group in SQL Server 2012 on a pair of servers and came across the following error.  “the local node is not able to communicate with the wsfc cluster”  Interestingly enough, I had already installed an availability group on these two servers in another domain.  I was changing domains (long story) and had decommissioned the cluster and wanted to set it back up.  I knew I had already done the following

  • Installed the latest Service Pack (2)
  • Validated the cluster

The fix was simply to remove the Availability Group option, re-apply it and then restart the SQL Server Service.  After that, I was off and running.

LocalNodeCannotCommunicate

T-SQL Tuesday 59–My Hero

Today’s T-SQL TuesTsql Tuesdayday is being hosted by Tracy Mckibben (t|b) and he has invited us to talk about our heroes with it being Ada Lovelace Day and all.  As Ada is created with being the first programmer and this topic got me thinking about firsts in my family.

My grandmother came to the United States in the early sixties looking for a better life for her family.  She left behind four children who would join her over the next four years.  She worked as a maid to a family in Connecticut and earned $50 a week–$45 of which would go back to her family in Costa Rica.  Those efforts led the family here, where my father earned his bachelor’s degree from BYU and then later earned a masters from Clemson University; the first in the family.  She continued to work until her body would not let her and always encouraged us to do our best.  Her sacrifices those 55 years ago led to all the blessings my family now enjoys.  Grandma is one my heroes.

Of course, I would not be able to pursue independent employment as I know it today if it weren’t for my family–especially my wife who has supported me and my sometimes crazy whims.  I am grateful for their support and am glad I have a partner for all eternity. :)

Also, I need to give a shout out to Andy Leonard, who helped start the Richmond SQL Server Users Group.  Before we even knew what PASS was, Andy was there with a group of folks in Richmond talking shop and his even though he hasn’t had direct interaction with the group for some time, his influence is still felt and the core leadership team are involved because of Andy’s example.  The opportunity to belong to that group has been all the difference in what I have been able to do professionally.