Scheduling PowerShell Using Jenkins (2020-12-10)

 SQLServerCentral Newsletter for December 10, 2020 Problems displaying this newsletter? [View online](. [SQL Server Central]( Featured Contents - [Scheduling PowerShell Using Jenkins]( - [More Gems from the SQL Prompt Treasure Chest]( - [Deploy Database Objects to Multiple SQL Server Instances Using PowerShell]( - [From the SQL Server Central Blogs - Would You Pass the SQL Server Certifications Please? What Do You Mean We're Out?]( - [From the SQL Server Central Blogs - Power BI – Hungry Median – Aggregations]( Question of the Day - [The TUF File in Log Shipping]( The Voice of the DBA  Daily Coping Tip Support a charity, cause, or campaign you really care about I also have [a thread at SQLServerCentral]( dealing with coping mechanisms and resources. Feel free to participate. For many of you out there working in a new way, I'm including a thought of the day on how to cope in this challenging time from [The Action for Happiness Coping Calendar](. My items will be on [my blog](, feel free to share yours. Data Sovereignty Issues Many people think about the cloud for cost reasons first, often because they can transfer the expense from a capital to operating budgets. Others think about the flexibility the cloud vendors offer to spin systems up and down, change configurations, and adapt to requirements quicker than many internal groups can. A few consider scalability, where we can grow or add systems to meet our needs. What about data sovereignty? I ran across [an article]( recently that talked about the new SAS cloud on Azure, but located in different places for different reasons. Customers in the EU can use the German version of this. Those customers in the UK, because of Brexit, might choose the UK data center. It's an interesting idea, where we might not think about just which specifications and services we use, but where they are located. That can pose challenges for architects and software developers, who may need to route customer requests to different data stores, based on their location. I'd think this is easier for database professionals, as we just duplicate stores in different places. This does mean that we'd need to manage backup, integrity, ETL flows, etc. in multiple locations, and we'd had the need to roll some data up across different data connections, but technology is making this easier all the time. The legal requirements and protections of having data in different places are still being argued and worked out. [Microsoft fought a battle](, and there was some resolution for how to handle cross border data issues, but I'm not sure this is settled law yet. Time will tell, but if there is any value to customers in where data is located, I suspect some of us will need to consider how we store data in different physical locations. Steve Jones - SSC Editor [Join the debate, and respond to today's editorial on the forums](   Featured Contents [Scheduling PowerShell Using Jenkins]( VishnuGupthanSQLPowershellDBA from SQLServerCentral Introduction PowerShell is a task-based command-line shell and scripting language; it is designed specifically for system administrators and power-users, to rapidly automate the administration of multiple operating systems (Linux, OSX, and Windows) and the processes related to the applications that run on those operating systems. For system administrators (DBAs\Wintel Admins), creating PowerShell scripts is a […] [More Gems from the SQL Prompt Treasure Chest]( Additional Articles from Redgate Join Microsoft MVP Grant Fritchey and Redgate's Owen Standage on a journey through the hidden treasures of SQL Prompt. Discover features of the unknown that will enable you to write, format, analyze and refactor your SQL effortlessly. [Deploy Database Objects to Multiple SQL Server Instances Using PowerShell]( Additional Articles from MSSQLTips.com In this article we look at a PowerShell script that can help you deploy database objects to multiple SQL Server instances. From the SQL Server Central Blogs - [Would You Pass the SQL Server Certifications Please? What Do You Mean We're Out?]( @DBA_ANDY from Nebraska SQL from @DBA_ANDY I have held various certifications through my DBA career, from CompTIA A+ certification back when I worked help desk (I'm old) through the various MCxx that Microsoft has offered... From the SQL Server Central Blogs - [Power BI – Hungry Median – Aggregations]( Joyful Craftsmen from Joyful Craftsmen Blog Introduction In my last blog post, I have addressed an issue with DAX Median function consuming a lot of memory. To refresh the result, below is the performance summary...   Question of the Day Today's question (by Steve Jones - SSC Editor):  The TUF File in Log Shipping When does SQL Server create a TUF File in Log Shipping? Think you know the answer? [Click here]( and find out if you are right.    Yesterday's Question of the Day (by Steve Jones - SSC Editor) Python Lambda Expressions I want to add a lambda expression in Python to my code. How many statements can I include inside the expression? Answer: 1 Explanation: Lambda expressions get a single expression or statement. Ref: Python Lambda Expressions (4.7.6) - [Discuss this question and answer on the forums](    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 - Administration [Error SSMS multisubnet failover]( - Good morning all , I have an on alwayson listener in multisubnet mode with 3 IP inputs if I try to connect to the listener even with the multisubnetfailover = true option I have this error [SQL Server Performance Counters]( - Here's a question that feels like I'm asking if a tree falls in the woods and nobody is there to see it, does it make a noise? With that - Performance Counters (SQL Server specific ones), do these run in the background the entire time? Or does starting up Perfmon and tracking them start them […] [SQL Server Mirroring - Principal cannot set endpoint to Mirror]( - I followed the below guide & I am stuck on the very last step where you set the endpoint to reference the mirror server. The result is the same even if I use SSMS. I am able to telnet successfully on port 5022 from principal to mirror. I am also able to telnet successfully on […] SQL Server 2017 - Development [Bulk Insert - An unexpected end of file was encountered in the data file.]( - Dear Group: I am trying to perform a BULK INSERT using the code below. However, this is causing the following errors: I know the problem, but not sure of how to solve for it (if it can be solved with BULK INSERT). The issue is that our tech department's standard is that every file has […] SQL Server 2016 - Administration [SQL Server Load]( - Hi, The application team is pushing more than 7 lakhs records per day to SQL Server. The SQL Server version is 2016 with ALWAYS ON. During one point of time the SQL Server hungs and I need to failover manually. How much load can SQL Server withstand How much connections can it take at a […] SQL Server 2016 - Development and T-SQL [How to aggregate Dimension values properly]( - Ahoi, im trying to deal with a user request to combine 2 values of a column based on values of a different attribut. I managed to make it work like this, creating 2 hidden memembers and combining those in my cube script. --Hidden 1 CREATE MEMBER CURRENTCUBE.[Order].[Ordertype].[Onlineshop Webshop] AS ([Order].[Ordertype].[All],[Order].[Belegart MDX Ohne Mandant].&[Webshop]), VISIBLE = […] SQL 2012 - General [questions about sys.sysprocesses/sys.dm_exec_sessions and sys.dm_exec_requests]( - I have some questions  about sys.sysprocesses/sys.dm_exec_sessions and sys.dm_exec_requests, please kindly give your guidance and comments, thanks! 1. under Microsoft SQL server Management Studio, when I execute select * from sys.dm_exec_sessions and select * from sys.sysprocesses, they have same pieces of records, so spid of sys.sysprocesses is the same thing with the session_id of sys.dm_exec_sessions, is […] SQL Server 2012 - T-SQL [Converting minutes to hh:mm:ss]( - I'm trying to develop a formula to convert minutes (my raw data) to hh:mm:ss format. E.g.: 526.30 minutes = 31,578 seconds which should read 08 hrs. 47 minutes and 8 seconds or 08:46:08 ~ I think (?) SQL Server 2019 - Administration [How to mantain index in SQL and size of Database]( - Hi everyone, I'm trying to figure out how to manage the maintenance of the indexes I just created in some tables of my database. Once I run the Rebuild, the size of the database increases and by shrink, the indexes are fragmented again. Is there a way to clean up the indexes without affecting the […] [Interpreting Index Usage Stats on an Availability Group]( - I've been querying sys.dm_db_index_usage_stats on the nodes of an availability group, and I've noticed an exponential difference between the number of user_seeks on the primary node versus either of the secondary nodes. On the table I'm analyzing, the primary node has many indexes with user_seeks greater than 100,000, whereas on the secondary nodes not one […] SQL Server 2019 - Development [All month/yr when no data across different column combinations.]( - was hoping to get some help as it appears I'm not close. Trying to get all year/mo combinations even when there's no data...based on year/mo, Site, Dept and Cat. Appreciate any help!  create table #Date_Dim (Date date ) Insert into #Date_Dim values ('2020-01-01') ,('2020-01-02') ,('2020-01-03') ,('2020-01-04') ,('2020-02-01') ,('2020-02-02') ,('2020-02-03') ,('2020-02-04') create table #Data (Date […] [Ideas for Auditing]( - I'm currently looking to design some auditing on my SQL application and am wondering what would be the best way. Basically when a user Inserts or Updates I would like to capture what the previous value was and what the new value is. This would be across about most of my tables and could be […] Azure Data Factory [Set Variable to lookup first row value with white space in property name]( - Hi, How can I set the value of a variable from a Lookup activity when thw property name hase a white space? I'm trying to do it this way but fails: "value": "@activity('LookupMaxRecordId').output.firstrow.Record ID#", Find the complete code below: Thanks! ************* { "name": "pipeline8", "properties": { "activities": [ { "name": "LookupMaxRecordId", "type": "Lookup", "dependsOn": [], "policy": { "timeout": "7.00:00:00", "retry": 0, "retryIntervalInSeconds": 30, "secureOutput": false, "secureInput": false […] Reporting Services [Query in SSRS report, double hop to linked server]( - I have 3 (old) existing SSRS reports that were developed and deployed on Server A. They each use the same 2 shared Data Sources (RD and DR). 1 of the Data Sources(DR) is a linked server, Server B. The reports have queries against the same datasets. 2  of the datasets  use the shared […] Data Transformation Services (DTS) [vbs failing]( - This vbscript for Excel that opens a pipe delimited file is failing srccsvfile = Wscript.Arguments(0) destxlsfile = Wscript.Arguments(1) Set objExcel = GetObject(,"Excel.Application") objExcel.Visible = false objExcel.displayalerts=false Set objWorkbook = Workbooks.OpenText (srccsvfile, Origin:=xlMSDOS, StartRow:=1, DataType:=xlDelimited, TextQualifier:=xlDoubleQuote, ConsecutiveDelimiter:=False, Tab:=False, Semicolon:=False, Comma:=False, Space:=False, Other:=True, OtherChar:="|") Set wkbTemp = ActiveWorkbook Set objWorksheet1 = objWorkbook.Worksheets(1) It's failing on the OpenText […]   [RSS Feed]([Twitter]( 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. ©2019 Redgate Software Ltd, Newnham House, Cambridge Business Park, Cambridge, CB4 0WZ, United Kingdom. All rights reserved. webmaster@sqlservercentral.com  - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - -