An Introduction to PowerShell for a DBA (SQLServerCentral 12/21/2017)

[SQLServerCentral - www.sqlservercentral.com]( A community of more than 1,600,000 database professionals and growing Featured Contents - [An Introduction to PowerShell for a DBA]( - [SQL Prompt Hidden Gem: Auto-fill the GROUP BY clause]( - [Different Ways to Find SQL Server Orphaned Users]( - [How to Get Estimated Completion Time of SQL Server Database Backup OR Restore]( (From the SQLServerCentral Blogs) - [SSRS won’t bind HTTPS to new certificate — “We are unable to create the certificate binding”]( (From the SQLServerCentral Blogs) Featured Script - [Real World: SQL Server Quick Quality Check]( The Voice of the DBA Data Security Policies This editorial was originally published on Apr 7, 2014. It is being republished as Steve is on vacation. Does your organization have some policy around data security on mobile devices? Do your fellow employees care about data security? [A new study says that most organizations don't](, and potentially that's an issue. Many of us are data professionals, and we might have no idea how much data a user can access using today's modern mobile devices. Potentially we can help them understand that the $500 smartphone they use can actually contain and access much more than $500 worth of data. If their device is the cause of a data breach, the cost could easily be much closer to $50,000 than $500. Security is always a big gamble, and rather than the old models of controlling all devices and limiting access, we need to learn to educate users, work with them to secure their devices and report losses quickly. The survey shows that most employees don't even know how to report the loss of a device that might cause a data breach. At the very least, we can establish some procedures that will allow an account to be quickly turned off. And to ensure productivity doesn't suffer, we need a procedure that also engages a new account for a user quickly. My guess is a lot of security issues could be handled quicker if we ensured that users were aware of issues and penalized for ignoring them, but made sure those penalties were balanced with an understanding that there it is inevitable people will make mistakes and accidents. Forgive mistakes and ask for reports of potential issues quickly. Steve Jones from [SQLServerCentral.com]( Join the debate, and [respond to today's editorial on the forums]( ADVERTISEMENT [Database DevOps]( Continuous Delivery for SQL Server Databases Spend less time managing deployment pain and more time adding value. [Find out how with database DevOps]( [SQL Monitor]( Don’t just fix SQL Server problems, prevent them from happening SQL Monitor helps teams looking after SQL Server be more proactive. Not only does SQL Monitor alert you to current issues, it gives you the information you need to stop them happening in the future. [Download SQL Monitor now and get a 14 day free trial]( Featured Contents  [] [An Introduction to PowerShell for a DBA]( Alex Chamchourine from [SQLServerCentral.com]() An introduction to PowerShell that covers basics alone with some more advanced features. It will walk you through from the very beginning to the writing of a few useful scripts.[More »]( ---------------------------------------------------------------  [] [SQL Prompt Hidden Gem: Auto-fill the GROUP BY clause]( Once you install SQL Prompt, you become aware immediately of how it enhances the native IntelliSense features of SSMS, but this can blind you to a lot of other features, hidden in various parts of the tool. In this post I share another gem of a feature that I found myself using frequently once I realize it existed SQL Server orphaned users are a common thorny issue in auditing. In this post, Jeffrey Yao provides a few different ways of identifying orphaned users covering various scenarios.[More »]( ---------------------------------------------------------------  [] From the SQLServerCentral Blogs - [How to Get Estimated Completion Time of SQL Server Database Backup OR Restore]( Dharmendra Keshari from [SQLServerCentral Blogs]( “How much time SQL Server is going to take to complete the database Backup or Restore” – This is one of...[More »]( ---------------------------------------------------------------  [] From the SQLServerCentral Blogs - [SSRS won’t bind HTTPS to new certificate — “We are unable to create the certificate binding”]( hamish.watson8 from [SQLServerCentral Blogs]( This blog post is around the situation where you have SSRS setup to use HTTPS and thus using a certificate...[More »]( Question of the Day Today's Question (by Steve Jones): I have an identity property on the LoggerKey column in my Logger table. I decide to run this SELECT IDENT_CURRENT('Logger') What is returned? Think you know the answer? [Click here](, and find out if you are right. --------------------------------------------------------------- We keep track of your score to give you bragging rights against your peers. This question is worth 1 point in this category: IDENT_CURRENT. We'd love to give you credit for your own question and answer. To submit a QOTD, simply log in to the [Contribution Center](. ADVERTISEMENT [SQL Server Query Store In Action]( The Query Store changes the way you monitor performance on your databases and the way you tune the performance of those same databases. This book represents a deep dive into a large number of topics in and around the Query Store. [Get your copy from Amazon today](. Yesterday's Question of the Day Yesterday's Question (by Steve Jones): I've got this function in R: FindingTheMean <- function(first, second, third) { return((first + second + third )/ 3) } I decide to call it like this. FindingTheMean(f=25, t = 100, 200) What happens? Answer: I get 108.3333 as a result Explanation: R supports named parameters, which means I could call my code like this: FindingTheMean(first=25, second=200, third = 100) I can order the parameters any way I wish. R also supports partial matching, which attempts to match a partial parameter, by name, with one that exists in the function definition. In this case, f closely matches first, with no conflicts. Same for t. Note: This is deprecated and should not be widely used, but you may encounter this. R does support positional matching as well, and in this case is smart enough to know that the last parameter, unnamed, is second. You can test this as: > FindingTheMean2(f=25, t = 100, 200) [1] 200 Ref: R Functions - [click here]( --------------------------------------------------------------- [» Discuss this question and answer on the forums]( Featured Script [Real World: SQL Server Quick Quality Check]( Randeep Singh from [SQLServerCentral.com]() In this article, I would like to demonstrate a simple way to minimize your stressful day when DBAs have a frequent deadline pressure with numerous projects in progress at given time and you need to build and maintain a SQL server in real world to meet your client commitments as client comes first. In our client environment we are using mount point (SAN LUNs).Our disk layout for SQL system databases and data (Primary and Secondary) files is RAID-5 and for user database Log files and System Temp database is RAID-1.SQL server quick quality check script works from version of SQL server 2005 to SQL server 2016 and can help you to check below 14 items when you build a SQL server in real world. SQL server name, Instance name, Current Date Time ,SQL version ,Error Log file location, server authenticaton,login auditing. Names of Members in SysAdmin role, Names of members in ServerAdmin, Temp DB File Location (mdf, ldf and ndf), Physical location of your system databases and application database(s). Also, It can give you information about SQL server instance Max server Memory(GB), Min server memory (GB) and Lock Pages in Memory. The script contributed to decrease the number of hours to minutes to peer review SQL server instances and helps to improve the overall quality of our SQL server builds. Reference: [click here]( P.S:Feel free to pitch-in if you have any recommendations to modify this script. [More »]( Database Pros Who Need Your Help Here's a few of the new posts today on the forums. To see more, [visit the forums](. --------------------------------------------------------------- [SQL Server 2017]( : [SQL Server 2017 - Development]( [Help with aggregate function]( - have table: col1 col2 col3 A    B  10 B    A   5 C    D  8 Want to get: A  B  15 C  D  8 --------------------------------------------------------------- [SQL Server 2016]( : [SQL Server 2016 - Administration]( [Ola's maintenance solution taking too long on VLDB's]( - All, I am looking into optimizing Ola's maintenance solution by tweaking around some parameters that is part of the stored proc.... [Check DB failed]( - in one of my server CHeck DB failed due to the below error The operating system returned error 1450(failed to... [Restore full backup at first and restore diff backup later, can I?]( - Let me explain. I have a big database (250 gigs) that I need to restore on another machine.  Copying and restoring... --------------------------------------------------------------- [SQL Server 2014]( : [Administration - SQL Server 2014]( [Access as FE and MS SQL Server as BE]( - Hi Guys, I moved Access back-end (split database with FE and BE in Access) into MS SQL Server database using Sql... --------------------------------------------------------------- [SQL Server 2014]( : [Development - SQL Server 2014]( [SQL XML - Font colour in a case statement]( - Hi, I've written a few SQL XML statements before where I can change the font colour and size using case, when,... [Distinct of two columns?]( - Hello all, Hoping someone out there can help with this because I'm just not seeing the answer for some reason, and... [To Index a Computed Column...]( - Hi All, So in an effort to optimize a larger query (which I've posted about here before), I'm trying to utilize computed... --------------------------------------------------------------- [SQL Server 2012]( : [SQL 2012 - General]( [How to add union in between lines]( - I have to import the counts from table into an Excel and I am wondering if someone knows how to... [Login failed for user 'NT AUTHORITY\ANONYMOUS LOGON']( - One of the user who has DB owner rights on the database(dbs-sqlprod4), trying to access one of the view from... [SQL 2012 - Interview Questions (Sample)]( - Hi Guyz, Warm greetings ! I'm very happy to post some interview questions today which I'd faced recently via a Skype. Could you please... --------------------------------------------------------------- [SQL Server 2012]( : [SQL Server 2012 - T-SQL]( [Using a Column's value for the XML Path's value]( - Hi All, Using XML and T-SQL is not my strongest point. At the moment, I'm looking to try and create some... [Islands and Gaps Question]( - Hey guys, feeling kinda dumb I can't figure this one out on my own. :( Hopefully you can advise. Here's some... --------------------------------------------------------------- [SQL Server 2008]( : [SQL Server 2008 - General]( [Executing HUGE scripts with SQLCMD]( - We've got a system which generates an SQL script to be executed against some test database, and were using SQLCMD... [Replace TABLE with STAGING_TABLE. Approach?]( - I have a Stored Procedure which does some ETL "stuff" At the end of the procedure I want to replace a... --------------------------------------------------------------- [SQL Server 2008]( : [SQL Server 2008 Administration]( [Enabling RCSI]( - Hi I'd like to be able to enable RCSI on some of our databases. I know that if your code is not... --------------------------------------------------------------- [Data Warehousing]( : [Integration Services]( [script error]( - hi my target server is in 2016 ( 13.0.4206.0) to run sql server agent job . I am using visual studio 2015 to... --------------------------------------------------------------- [Data Warehousing]( : [Strategies and Ideas]( [Multiple Joins on Fact Table]( - Hi, I am building a DW which will load data from 2 separate sources. Each source will populate the Broker Dim table... --------------------------------------------------------------- [Database Design]( : [Relational Theory]( [Defining keys as sets that must not intersect instead of scalar values that must not be equal]( - I have been doing a lot of thinking about data models for the last couple of weeks. I have, just... --------------------------------------------------------------- [SQLServerCentral.com]( : [SQLServerCentral.com Website Issues]( [Please get rid of this constant, useless job alert!!!!!!!]( - I've been getting this email from the SQL Server Central website's daily for at least 2 months, notifying me of... This email has been sent to {EMAIL}. To be removed from this list, please click [here.]( If you have any problems leaving the list, please contact the webmaster@sqlservercentral.com. --------------------------------------------------------------- This newsletter was sent to you because you signed up at [SQLServerCentral.com](. Feel free to forward this to any colleagues that you think might be interested. If you have received this email from a colleague, you can register to receive it [here](. --------------------------------------------------------------- This transmission is ©2017 Redgate Software Ltd, Newnham House, Cambridge Business Park, Cambridge, CB4 0WZ, United Kingdom. All rights reserved. Contact: webmaster@sqlservercentral.com