November 26, 2016

Question: I’ve had a request to install SQL Server Express to support a Production application. It’s a lightweight application , and doesn’t require some of the added features , such as High Availability , encryption, compliance reporting for audit. Also , the system only requires 4 cores and 1 GB memory.

What is your view on using SQL Server Express on a Production environment?

Answer: Firstly , SQL Server Express is free to download , use and distribute. That is a tempting proposition for a cost – conscious organisation. But you need to look deeper and at free. It may initially be free , but long-term there are hidden costs of using SQL Server Express in the Production environment

I’m hard line on not using SQL Server Express in the Production environment. These are some reasons for my view

In most large SQL Server environments , patching is an important aspect of DBA administration. Unless you have a central area for patching , how will you deal with vulnerabilities when discovered?

Software currency – if there is no central group inventory for SQL Server Express , then maintaining software currency can easily be missed

If it’s a Production application then Production level infrastructure and support is required.

Maximum database size for SQL Server Express is 10 GB. That’s it , no more. Think about how quickly you may get to 10 GB – there are no warnings.

November 25, 2016

Generally I work every day on database server problems. But I don’t work in a vacumn. I work with groups of people – some are IT professionals , others are customers, suppliers , managers. All these people have different levels of motivation and ambition. They approach work from their perspective but we all end up working in a structure of authority.

I started reading on the subject of social conformity and to what extent we question authority. Zimbardo and Milgram led two classic experiments , exploring the subject of social conformity and authority. I’m not jumping to any conclusions about my workplace , but it’s interesting reading

Zimbardo’s aim was to investigate how readily people would conform to the roles of guard and prisoner in a role-playing exercise that simulated prison life.

Zimbardo converted a basement of the Stanford University psychology building into a mock prison. He advertised for students to play the roles of prisoners and guards for a fortnight.

More than 70 applicants answered the ad and were given interviews and personality tests to eliminate candidates with psychological problems, medical disabilities, or a history of crime or drug abuse. The study ended up involving 24 male college students who were paid $15 per day to take part in the experiment.

Participants were randomly assigned to either the role of prisoner or guard in a simulated prison environment. The prisoners were treated like any criminal, being arrested from home and held in prison with just a number as an identity (deindividualization).

All guards were dressed in identical uniforms of khaki, and they carried a whistle around their neck and a billy club borrowed from the police. Guards also wore special sunglasses, to make eye contact with prisoners impossible. Guards were told they were in charge and instructed to do whatever they thought was necessary to maintain law and order in the prison and to command the respect of the prisoners. However, no physical violence was permitted.

The prisoners were humiliated with insults and petty orders, they were given pointless and boring tasks to accomplish, and they were generally dehumanized. E.g. cleaning the toilet with their hands.

Zimbardo had planned for the experiment should run for a fortnight, but on the sixth day it was stopped. Christina Maslach, a recent Stanford Ph.D. was brought in to conduct interviews with the guards and prisoners, strongly objected when she saw the prisoners being abused by the guards. She said, "It's terrible what you are doing to these boys!" she was the only one that questioned what Zimbardo’s experiment was doing to the prisoners and guards. On the contrary, Zimbardo later on married Christina Maslach!

One of the main issues with the Zimbardo prison study is that it has low ecological validity meaning that it is less of a realistic situation due to the participants knowing it was an experiment the whole time. Unlike other situations where it isn’t an experiment but has real-life consequences

Overall, the prison study shows that people conform to their role to the extreme and quickly conform also. This can also translate in to a working environment when someone is given a higher role or put into a leadership position. People with important roles can get carried away and they can end up with many people disliking them due to being treated inadequately.

Stanley Milgram, a psychologist at Yale University, conducted an experiment focusing on the conflict between obedience to authority and personal conscience. The experiments began in July 1961.

Milgram was interested in researching how far people would go in obeying an instruction if it involved harming another innocent person.

The procedure was that the participant was paired with another person and they drew lots to find out who would be the ‘learner’ and who would be the ‘teacher’. The draw was fixed so that the participant was always the teacher, and the learner was one of Milgram’s confederates (pretending to be a real participant).

The learner was taken into a room and had electrodes attached to his arms, and the teacher and researcher went into a room next door that contained an electric shock generator and a row of switches marked from 15 volts (Slight Shock) to 375 volts (Danger: Severe Shock) to 450 volts (XXX).

Two rooms in the Yale Interaction Laboratory were used - one for the learner (with an electric chair) and another for the teacher and experimenter (with an electric shock generator.)

The teacher is told to administer an electric shock every time the learner makes a mistake, increasing the level of shock each time.

The learner gave mainly wrong answers (on purpose) and for each of these the teacher gave him an electric shock.

When the teacher refused to administer a shock the experimenter was to give a series of orders to ensure they continued.

There were 4 orders and if one was not obeyed then the experimenter (Mr. Williams) read out the next prod, and so on.

Prod 1: Please continue.

Prod 2: The experiment requires you to continue.

Prod 3: It is absolutely essential that you continue.

Prod 4: You have no other choice but to continue.

Issues with Milgram’s experiment is deception; the participants actually believed they were shocking a real person, and were unaware the learner was a confederate of Milgram's. Also some people say that participants could be left with psychological damage after the experiment and being put through the trauma of thinking they were having to torture an innocent person.

Ordinary people are likely to follow orders given by an authority figure, even if it leads to killing an innocent human being. Obedience to authority is learnt at a very young age e.g. with teachers at nursery and school. People tend to obey orders from other people if they recognize their authority as morally right and / or legally based for example the police.

I’m not suggesting that I face these dilemmas .But these are thought-provoking topics and have made me consider my own motivations and decisions I make on a daily basis - both as a DBA and in my social life.

November 15, 2016

Question: Do you have any powershell code listing the sql server instances running on a server host. The aim is to report on the service status. The script should cover multiple instances on a server.

Answer: This powershell script can execute on a server, returning all SQL Server instances . The script utilises the Get-Service cmdlet. Customise the script to various search parameters. In the example below, I'm using MSSQL$ , MSSQLSERVER and SQL Server.

You'll be thinking about whether you'd prefer to run the script directly on the server or execute remotely. Powershel Get-Service can access remote servers - Powershell script - Get the SQL Server Instance status with Get-Service . So with a few minor tweaks to the script about it is possible to run this script from a local script repository and call remote servers.

You could even go one step further and iterate through a whole list of servers.