A Fine Slice Of SQL Server
Jana Sattainathan has a cmdlet to retrieve instance information with Powershell:
There is not much to say except that this supports pipeline input. Take a close look. PowerShell folks don’t read text, they just focus on the code…So, here it is.
Click through for this code.
Comments closedJes Borland has a five-part series on replicating a series of databases in an Availability Group to Azure SQL Database. Part 1 involves planning:
There are tasks you’ll need to take care of in SQL Server, the AG, and the SQL DB before you can begin.
This blog series assumes you already have an AG set up – it won’t go through the setup of that. It also assumes you have an Azure SQL server and a SQL Database created – it won’t go through that setup either.
Ideally, the publishers, distributor, and subscribers will all be the same version and edition of SQL Server. If not, you have to configure from the highest-version server, or you will get errors.
Part 2 prepares the replication distributor:
The first step in this process is to set up the remote distributor. As I mentioned in the first blog, you do not want your distribution database on one of the AG replicas. You need to set this up on a server that is not part of the AG.
Start by logging on to the distributor server – in this demo, SQL2014demo.
Stay tuned for the remainder of the series.
Comments closedImportant Notes:
Start-dfs.shwill start NameNode, SecondaryNamenode, DataNode on master and DataNode on all slaves node.Start-yarn.shwill start NodeManager, ResourceManager on the master node and NodeManager on slaves.- Perform
Hadoop namenode -formatonly once otherwise you will get an incompatible cluster_id exception. To resolve this error clear temporary data location for datanode i.e, remove the files present in $HADOOP_HOME/dfs/name/data folder.
If you’d like to set up your own Hadoop cluster rather than using one of the big vendors (Hortonworks, Cloudera, MapR) or a PaaS solution like HDInsight or ElasticMapReduce, this will give you a head start.
Comments closedMichael Swart has a story on blocking due to an edge case scenario:
SQL Server was struggling to compile the procedure in time and the application wouldn’t let it catch its breath. The query optimizer was attempting to create statistics automatically that it needed for optimizing the query, but after thirty seconds, the application got impatient and cancelled the query.
So the compilation of the procedure was cancelled and this caused two things to happen. First, the creation of the statistics was cancelled. Second, the next session in line was allowed to run. But the problem was that the next session had already spent 28 seconds blocked by the first session and only had two seconds to try to compile a query before getting cancelled itself.
The frequent calls to the procedure meant that nobody had time to compile this query. And we were stuck in an endless cycle of sessions that wanted to compile a procedure, but could never get enough time to do it.
There are two important lessons here: how Michael solved the problem and also a reminder that plan cache entries are dependent upon specific application settings.
Comments closedJason Brimhall explains an error message which might be confusing at first:
Here is the error message that is quite possible to encounter while creating principals.
Msg 15006, Level 16, State 1, Line 6
‘SomeDOmain\jason’ is not a valid name because it contains invalid characters.At first look, this error makes absolutely no sense. The error states there is an invalid character somewhere in the string “SomeDomain\jason”, yet every character in that string is supported and normal for the collation. This can be a head-scratcher for sure.
Read on for the answer.
Comments closedKendra Little has a few Github gists showing how to configure the blocked process report:
I wanted a friendly way to share code to configure and manage the Blocked Process Report, so I’ve created a gist on GitHub sharing TSQL that:
Enables the Blocked Process Report (BPR)
Collects the BPR with an Extended Events trace
Collects the BPR using a Server Side SQL Trace (in case you don’t care XEvents or are running an older version of SQL Server)
Lists out the Extended Events and SQL Traces you have running, and gives you code to stop and delete traces if you wish
Click through for the code.
Comments closedMonica Rathbun explains the concept of runbooks:
Don’t try to build job security into what you do. I know many that worry about giving up the knowledge to others. Having the sole “how to” knowledge for some, gives them a sense of job security. While to a point that might be true, it also locks you in to your current position. Many that hoard their knowledge never advance because they find themselves invaluable in their current position. “We can’t move them because they are the only ones who know about such and such”. Why put yourself in that position? If you can’t ever be replaced, you also can’t move up.
As a lone dba, I find this run book to be vital. It allows me to direct someone to the book and I can walk them through running anything I need them to in my absence. It allows me to take a vacation or a day off while giving others the tools to get things done.
Exactly. It’s easy to get caught in the trap that your value is in the specific details of some process that you know, and so the company can’t get rid of you because you’re the only person who knows this. One of the counter-intuitive results of IT culture is that reputation comes from sharing information rather than hoarding it.
Comments closedKevin Hill reads the default trace to figure out who dropped a database:
We have a staging server that has a database for each client that we can test on before working in their production databases. Recently one has gone missing a few times…but nobody will own up to it.
If you want to set up an extended event to capture this information to disk separately, Jason Brimhall has you covered.
Comments closedSajib Mahmood gives various methods for debugging Spark applications running on an HDInsight cluster:
Spark Application Master
To access Spark UI for the running application and get more detailed information on its execution use the Application Master link and navigate through different tabs containing more information on jobs, stages, executors and so on.
These methods also apply for on-prem Spark clusters, although the resource locations might be a little different.
Comments closedErik Darling looks at a new DMV in vNext CTP 1.1:
It’s not exactly perfect
For instance, if you just let it loose without filters, you get a severe error. The same thing happens if you try to filter on one of the columns in the function, rather than a column in sys.stats, like this.
Very cool. It’s one step closer to us removing our dependencies on DBCC SHOW_STATISTICS.
Comments closed