Schedule Jobs on a Clustered SQL Server (2023-02-06)

SQLServerCentral Newsletter for February 6, 2023 Problems displaying this newsletter? [View online](. [SQL Server Central]( Featured Contents - [Schedule Jobs on a Clustered SQL Server]( - [Privileges and Roles in SQL Server, Oracle and PostgreSQL - Part 1]( - [The Database DevOps Transformation]( - [From the SQL Server Central Blogs - Unpivot a matrix with multiple fields on columns in Power Query]( - [From the SQL Server Central Blogs - Script to Install the Tools I Use on My Jumpbox]( Question of the Day - [Encrypting the Database Encryption Key]( The Voice of the DBA  Daily Coping Tip Get back in contact with an old friend 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. Outsourcing Operations When I first started working in technology in the 90s, it was a time of outsourcing lots of work overseas. Many large companies followed the wave of manufacturing in the 70s and 80s by many companies, including lots of semi-conductor manufacturers. I watched as a number of jobs moved overseas, though fortunately not mine. In the early 2000s, I worked at a company as a manager, where I was involved in some of the discussions about outsourcing a lot of our IT operations to another company. It was a scary time for my friends, as the move would have left a lot of them looking for work. The CIO tried to say that many would get jobs with the outsourcing supplier, but couldn't say how many. I commented in a meeting that it would have to be less than 100% of people for there to be any profit. That statement ensured I needed to look for a new position, which I happily did. The company didn't outsource at that point, but being involved in the discussions helped me realize how different management and workers often view the business of IT. There was [an article on when to outsource](, which talks about the benefits and drawbacks. For most of the benefits, the reasons why you do this are very similar to why you might move infrastructure to the cloud. There is speed and flexibility, as well as simpler internal operations, when another organization handles those functions. The downsides are that your organization needs to manage another, and you might not have the control you desire. The same thing you could say about the cloud. The costs could be lower, with outsourcing or the cloud, if you can reduce unnecessary resources. That can be hard to do internally, especially as you hire staff. Each additional person might be necessary in the short term, but if when they are no longer producing a positive ROI, it can be hard to get rid of them. Consequently, with an outsourced company, often contracts dictate the staffing levels, and those contracts aren't amended or renegotiated often. That could leave you with lacking services you need or paying for those you don't. I don't know that it's easy to decide when to outsource. My view has often been to keep some level of in-house staff, but augment them with some sort of managed service provider that can provide additional resources when you get busy. Whether this is a DBA service, like or a development effort, like [Crafting Bytes](, adding staff in limited quantities is often the best way to move forward. These smaller groups also provide opportunities for some of you that might want to find a different type of employment arrangement. Usually more flexibility, a warm, friendly atmosphere, and the chance to grow with a small business. Outsourcing might sound like a bad idea to many corporate employees, but it does provide opportunities for you. Keep working on your skills, network with others, showcase some knowledge with a blog/article/speaking slot, and you might hedge your bets in the event your employer makes a decision about outsourcing that doesn't work for you. Steve Jones - SSC Editor [Join the debate, and respond to today's editorial on the forums](   Featured Contents [SQLServerCentral Article]( [Schedule Jobs on a Clustered SQL Server]( cbk prasad from SQLServerCentral In this article, you will learn how you can schedule jobs in a clustered environment that will only execute on the primary node. [SQLServerCentral Article]( [Privileges and Roles in SQL Server, Oracle and PostgreSQL - Part 1]( Additional Articles from MSSQLTips.com This tip will review the different ways of granting privileges in SQL Server, Oracle, and PostgreSQL, the differences in the concepts of roles, schemas, and owners, and the way to grant permissions on the whole schema. [External Article]( [The Database DevOps Transformation]( Additional Articles from Redgate Digital transformation is a buzzword that is often thrown about. But what isn’t discussed in depth, is how DevOps fits into the initiative and what the pitfalls are. This on-demand session answers these questions and provides actionable takeaways from real-life database deployment automation projects. You’ll also hear how the key trends in Database DevOps have influenced Flyway product development. [Blog Post]( From the SQL Server Central Blogs - [Unpivot a matrix with multiple fields on columns in Power Query]( Meagan Longoria from Data Savvy I had to do this for a client the other day, and I realized I hadn’t blogged about it. Let’s say you need to include data in a Power... [Blog Post]( From the SQL Server Central Blogs - [Script to Install the Tools I Use on My Jumpbox]( Tracy Boggiano from Database Superhero’s Blog I wrote a blog post a few months ago about the tools I use on my jumpbox you can read here. Since then, I have The post Script to Install...   Question of the Day Today's question (by Steve Jones - SSC Editor):  Encrypting the Database Encryption Key I am configuring Transparent Data Encryption for a new database and need to create a Database Encryption Key (DEK). As I architect this, I need to secure this key. What are my options for encrypting the DEK? 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) The NULL Connection Setting I connect in SSMS to SQL Server and run this code. What happens? SET ANSI_NULL_DFLT_ON ON SET ANSI_NULL_DFLT_OFF ON GO CREATE TABLE ANSINULLTest (someid INT) GO Answer: The SET statements run and the table is created Explanation: While you can only have one of these settings set to ON at a time, you can run both of these statements. The second one overrides the first and the table is created. Ref: - SET ANSI_NULL_DFLT_ON - - SET ANSI_NULL_DFLT_OFF - [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 [migration]( - Has anyone migrated databases from on premise SQL Server to Azure Managed instance? If yes, what are the data transfer methods used? Thanks in advance. SQL Server 2017 - Development [Multiple values in a parameter]( - Dear All, I want to use these values in the parameter for further usese, but it's giving me an error WITH ctevalues(recp_name, ing_name, qty) AS ( values ('pasta pollo', 'paprika', '1') ) INSERT INTO ing(recp_id, ing_name_id,qty) SELECT rec.recp_id, ing.ing_name_id,d.qty FROM ctevalues AS d INNER JOIN ing ON d.ing_name = ing.ing_name INNER JOIN recp AS rec […] ['DATE' is not a recognized built-in function name]( - Please assist. I am getting the above error: CREATE VIEW DailyAccountBalances AS SELECT DATE(TransactionDate) AS Date, SUM(CASE WHEN TransactionType = 'Opening Balance' THEN Amount ELSE 0 END) AS OpeningBalance, SUM(CASE WHEN TransactionType = 'Closing Balance' THEN Amount ELSE 0 END) AS ClosingBalance, SUM(CASE WHEN TransactionType = 'Debit' THEN Amount ELSE 0 END) AS DebitedAmount, SUM(CASE […] SQL Server 2016 - Development and T-SQL [This code works, but am requesting help with the code.]( - Hello everyone. I don't post here often even though I am constantly on the forums reading articles and such. I was wondering if anyone who is an excellent TSQL coder and perhaps is bored can assist me. I authored the below code, and it works just fine, but it is ugly (at least I think […] Administration - SQL Server 2014 [SQL - new server - use a subnet?]( - Hi, In the process of upgrading our old SQL server hardware. Running into issues were we have years of development that references old SQL server by name. We have webservers, hundreds of SSIS packages, old ODBC clients, etc..that all reference this old server by name. To make matters worse there are some old client server […] SQL Server 2019 - Administration [Microsoft SQL over IPSEC VPN TPLink routers - 2 locations]( - Hi Experts. We recently set up a second location. our software at our first location accesses the onsite server (windows Server2022) and this software uses SQL. Everything is very snappy and works well. We then set up the second location with IPSEC to create a VPN. All the mapped drives work well, no connection or […] [Trouble moving from a 2017 SQL Express DB on one HP laptop to another HP laptop]( - I am trying to migrate a DB from an old dying laptop to a new one, both HPs. The old one had SQL 2017 on it the new one SQL Express 2019. I have created the logins and linked them to the users that were orphaned in the backup DB/Restore process as it was quicker […] [Database copies (cross environment) and sizing strategies for 2019]( - I'm sure this has been asked many, many, many times and hoping several optimal solutions exist that I can be pointed towards... A company I work for has need of production copies being restored down to DEV/TEST environments literally all the time. So much so that the entire process is automated via tickets. This all […] [In-Place upgrades]( - The company I work for is insisting on in-place upgrades to 2019. I've made all the relevant arguments and it's just where I'm at. I started with the dev environment and immediately ran into an error that I couldn't get around. It seemed like I needed to attach the iso for the old install media […] SQL Server 2019 - Development [Track Insert/Update/Delete Operations in Sql Server DB Table]( - Hi Team, I have a requirement to track any insert/update/Delete operations happened against Database table by any user, catch those changes and send alert. Please note that there are around 1000+ DB tables exists so we need to also ensure that performance is not impacted. There are many apps also which insert the data into […] Reporting Services [SSRS REPORT]( - am getting the following when run report what can I do to fix this Integration Services [parameterizing a sql server connection]( - I am parameterizing an sql server connection. There are the following fields: username password servername initialcatalog (db name) connection string. The first 4 fields are most of what makes up the connection string. In the connection, do i need to parameterize each of these values, or do i just create an expression that builds the […] [SSIS Sort Alphanumeric?]( - Hello, I'm pretty new to SSIS, but I have an Excel source that I need to bring into an SQL Server table daily. There is a column I need to preserve which is an ID field with both numeric and alphanumeric characters. If I bring them in as-is, the alphanumeric values turn to NULL and […] Replication [TSQL to determine status of subscription reinitialization]( - How do I determine the initialization status of a subscription with T-SQL -- i.e, how do I determine that initialization is still in progress applying scripts, and how can I determine that it is complete -- all scripts have been applied -- the equivalent of the "Delivered snapshot..." message in Distributor to Subscriber History in […] SQL Server 2022 - Development [How to get number of unique months from table]( - Hi guys, I want some help with a code to count the number of unique months that appears in list (use created code below). For example: ZipCode 14171 got 5 hits, but the unique months are 3 and that's the expected output. Could someone please help with that? Thanks!   CREATE TABLE #mytable […]   [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  - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - -