Tech News

I was watching the GroupBy talk the other day and noticed that Cláudio Silva was using arrays, or what appeared to be arrays, in his talk. That was an interesting technique, one that I haven’t used very much. A day later, I ran into an explanation on dbatools.io, that showed this code: PS C:> $columns = @{ >> Text = 'FirstName' >> Number = 'PhoneNumber' >> } That didn’t quite seem like what I wanted, so I decided to investigate more. I looked up PowerShell Arrays, and that wasn’t what I wanted. These are a list of values, as in $a = 1, 2,3 Which gives me this: >>$a 1 2 3 Useful, but not for my purposes. I need to map things together, which means a hash table. Hash Tables It turns out I need a hash table. This is a key value pair that lets me pick a name and value and store them together. The way I construct these are with the @{} structure. Inside here, I set semi-colon separated pairs, with the name=value syntax. Here’s an example I used: $ColList = @{Date="EventDate"; Event="Event"} In here I map two keys (Date and Event) to two values (EventDate and Event). For the cmdlet I am using, this allows me to map these two columns together. When I need a value, I can use the $variable.key to get the value back. I assume this is what the SqlBulkCopy cmdlet uses, which is what dbatools wraps. I ended up passing this $ColList hash table in for the –ColumnMap parameter. SQLNewBlogger A quick writeup that I used to solve a problem. I had some issues figuring this out, and some searching and experimenting got me a little better understanding of what was happening. After about 30 minutes of some work, I took 10 minutes to type this up and explain it to myself. A good example of what you could add to your blog, showing how you use this in your work. The post PowerShell Arrays and Hash Tables–#SQLNewblogger appeared first on SQLServerCentral.

I can’t remember where I saw this, but it made an interesting Question of the Day: select * from Sales where Profit !< 10000; I had never seen anything like this, in all my years of working in C, C++, Java, Lisp, APL, Pascal, Fortran, VB, C#, SQL, and more. However, there are apparently a few operators that I’ve never used: !< !> These are the not less than and not greater than. Weird, though I guess this makes sense. Personally, I think restructuring as greater than or equal to instead of not less than makes perfect sense. The post Strange T-SQL Operator Syntax appeared first on SQLServerCentral.

If you have been following me or generally topics around Azure SQL Database and security you would know that it is important to leverage Advanced Data Security (ADS) for Azure SQL Database, if you remember this meant having tools such … Continue reading ? The post Azure Defender for SQL appeared first on SQLServerCentral.

I started to add a daily coping tip to the SQLServerCentral newsletter and to the Community Circle, which is helping me deal with the issues in the world. I’m adding my responses for each day here. Today's tip is to plan a new activity or idea you want to try out this week. At least a few months ago, this was a coping strategy for many. With shortages, closings, and more time at home, people were baking bread at home for the first time. That lead to shortages of baking yeast, so the focus shifted to sour dough bread. The bread baking seems to have slowed down -- or at least people are not talking about it as much. So, this week try something new. It doesn't need to be anything extravagant. Maybe try out a new recipe or complete a repair that you've been avoiding for months.   The post Daily Coping 9 Nov 2020 appeared first on SQLServerCentral.

Introduction SQL Server deadlock one of the issues that can be happened in any SQL Server, today in this article I will not explain what is Deadlock and How we can solve it but the main purpose of this article is sharing the scripts I am using it for monitoring this kind of process let … Continue reading Monitoring and Tracking SQL Server Deadlock process The post Monitoring and Tracking SQL Server Deadlock process appeared first on SQLServerCentral.

The PASS election slate was released for 2020. The candidates and their statements are:: Hamish Watson (@thehybriddba) Joey D’Antoni (@jdanton) – Community Matters–Why I’m Running for the PASS Board of Directors Jose Rivera –  (@SQLConqueror) – Lori Edwards (@loriedwards)- Matt Gordon (@sqlatspeed) – Why I Am Running for PASS Board Robert Fonseca (@roberto_mct) – Steph Locke (@TheStephLocke) – I’m Running for the PASS Board Not everyone has a statement, though you can read their bios and applications on the PASS Site. PASS is at a turning point in the organization, with the pandemic and the move of their main fundraiser to a virtual event. Across the last two years, I’ve been a member of PASS, a supporter, and a critic with my own thoughts and ideas about the organization. However, I’m not sure where PASS should go. There is a lot of enthusiasm for the organization from some, and no shortage of criticism. I see both sides, but what I’m not sure about is what I want PASS to be or do in the future. I suspect the organization will change, but into what? I’d ask that each of the candidates outline a vision for what PASS should be. Not tactical specifics or complaints, but ideally, what do we want from the organization. What would represent the members and be a great community group? Hopefully we’ll see something before Wednesday, when voting starts. The post What’s Your Vision for PASS? appeared first on SQLServerCentral.

I started to add a daily coping tip to the SQLServerCentral newsletter and to the Community Circle, which is helping me deal with the issues in the world. I’m adding my responses for each day here. Today’s tip is to get outside and observe the changes in nature around you. I was sick recently. Worn out, tired, and took a few days off. During that time, I was inside, didn’t go out to the store, and really didn’t work very much. I didn’t really go outside for a couple days, which unusual for me. When I felt better, I told my wife I’d help her get a few things done. Walking outside, feeling the change in weather, which has been dramatic a few days this fall in Denver, and seeing animals, was good for the soul. I even took a little video and enjoyed a moment with baby Phoebe. This make most things better The post Daily Coping 6 Nov 2020 appeared first on SQLServerCentral.

Summary: In this article, we will discuss about the ‘SQL Backup Detected Corruption in the Database Log’ error. It will also describe the reason behind the error and manual workarounds to resolve it. The article also explains an alternative solution that can be used to restore the database and its transaction log backup – when the manual solutions fail.  When performing transaction log backup for a SQL database, to restore the database after network maintenance or in the event of a crash, you may find the backup job failed with the following error: Backup failed for Server xxx (Microsoft.SqlServer.SmoExtended) System.Data.SqlClient.SqlError: BACKUP detected corruption in the database log. Check the errorlog for more information. (Microsoft.SqlServer.Smo) The error message clearly indicates that the transaction log is damaged (corrupted). Checking the SQL errorlog for more details on the error shows: 2020-11-01 13:30:40.570 spid62 Backup detected log corruption in database TestDB. Context is Bad Middle Sector. LogFile: 2 ‘D:DataTestDB_log.ldf’ VLF SeqNo: x280d VLFBase: x10df10000 LogBlockOffset: x10efa1000 SectorStatus: 2 LogBlock.StartLsn.SeqNo: x280d LogBlock.StartLsn.2020-11-01 13:30:40.650 Backup Error: 3041, Severity: 16, State: 1.2020-11-01 13:30:40.650 Backup BACKUP failed to complete the command BACKUP DATABASE TestDB. Check the backup application log for detailed messages However, the FULL database backup completed successfully and even running DBCC CHECKDB integrity check didn’t find any errors. What Could Have Caused the SQL Transaction Log Backup to Fail? A transaction log (T-log) backup allows restoring a database to a certain point-in-time, before the failure occurred. It does so by taking a backup of all the transaction logs created since the last log backup, including the corrupt portion of the T-log. This causes the backup to fail. However, a FULL database backup only has to back up the beginning of the last active part of the T-log – at the time the backup is taken. Also, DBCC CHECKDB requires the same amount of log as the FULL database backup – at the time of the db snapshot was generated. This is why the full backup executed successfully and no errors were reported by DBCC CHECKDB. Manual Workarounds to Backup Detected Log Corruption in SQL Database Following are the manual workarounds you can apply to resolve the SQL backup log corruption issue: Workaround 1: Change the SQL Recovery Model from FULL to SIMPLE To fix the ‘SQL Server backup detected corruption in the database log’ issue, try switching the database to the SIMPLE recovery model. Switching to SIMPLE recovery model will ignore the corrupted portion of the T-log. Subsequently, change the recovery model back to FULL and execute the backups again. Here’s the steps you need to perform to change the recovery model: Step 1: Make sure there are no active users by stopping all user activity in the db. Step 2: Change the db from FULL to a SIMPLE recovery model. To do so, follow these steps: Open SQL Server Management Studio (SSMS) and connect to an instance of the SQL Server database engine. From Object Explorer, expand the server tree by clicking the server name. Next, depending on the db you are using, select a ‘user database’ or choose a ‘system database’ by expanding System Databases. Right-click the selected db, and then select Properties. In the Database Properties dialog box, click Options under ‘Select a page’. Choose the Simple recovery model from the ‘Recovery model’ list box, and then click OK Step 3: Now set the db back to the FULL recovery model by following the same steps from 1 till 5 above. Then, select Full as your recovery model from the list box. Step 4: Perform a FULL database backup again. Step 5: Take log backups again. Hopefully, performing these steps will help you perform the transaction log backup without any issue. Note: This solution won’t be feasible if you’re using database mirroring for the database for which you have encountered the ‘backup detected log corruption’ error. That’s because, in order to switch to the SIMPLE recovery model you will need to break the mirror and then reconfigure the db which can take significant amount of time and effort. In this case, try the next workaround. Workaround 2: Create Transaction Log Backup using Continue on Error Option To complete executing the backup of T-log without any error, try running log backup of SQL database with the CONTINUE AFTER ERROR option. You can either choose to run the option directly from SSMS or by executing a T-SQL script. Steps to run the ‘Continue on Error’ option from SSMS are as follows: Step 1: Run SSMS as an administrator. Step 2: From ‘Back Up Database’ window, click Options under ‘Select a page’ on the left panel. Then, select the ‘Continue on error’ checkbox under the Reliabilitysection.  Step 3: Click OK. Now, run the log backup to check if starts without the backup detecting an error in SQL database. Ending Note The above-discussed manual solutions won’t work if the transaction log is missing or damaged, putting the database in suspect mode. In that case, you can try restoring the database from backups or run Emergency-mode repair to recover the db from suspect mode. However, none of the above solutions might work in case of severe database corruption in SQL Server. Also, implementing the ‘Emergency-mode repair’ method involves data loss risk. But, using a specialized SQL database repair software such as Stellar Repair for MS SQL can help you repair a severely corrupted database and restore it back to its original state in just a few steps. The software helps in repairing both SQL database MDF and NDF files. Once the MDF file is repaired, you can create a transaction log file of the database and back it up without any encountering any error. www.PracticalSqlDba.com The post [Solved] SQL Backup Detected Corruption in the Database Log appeared first on SQLServerCentral.

Earlier this week I announced the release of a new Power Platform book. While I’m super excited about that book I’m also excited to announce the 2nd edition of the Power BI Quick Start Guide. As you know Power BI is in a constant state of change so this second edition is not only an update but also introduces new topics like Power BI dataflows and several of the new AI features that have been introduced into Power BI. I hope you enjoy this new book, which you can find here! The post Microsoft Power BI Quick Start Guide – Second Edition appeared first on SQLServerCentral.

Mark Shulman Product Manager, Tableau Kristin Adderson November 5, 2020 - 8:48pm November 6, 2020 It’s okay to admit it—when you heard that Tableau introduced locked nested sub projects in 2020.1, it may not have given you goose bumps or sent shivers down your spine. But, we are here to say that if you’re responsible for governance and structuring yours Tableau site, it may be one of the most powerful features to come along in quite awhile. This “little” feature is easy to overlook, but has a big positive impact on minimizing the needs for additional sites, delegating admin responsibilities, and providing the flexibility that your organization needs. How do locked nested projects change my site management? Mark Wu, Tableau Zen Master, stated, “Sub projects can now be locked independently. This is a game changer for all of us!” To understand why this is a big deal, let’s go back to pre-2020.1 days. When you locked a project, the entire locked parent/child hierarchy underneath it had the exact same permissions. This limitation required you to either have a very broad, flat tree structure—or you may have worked around it by spawning unnecessary, new sites. You don’t need to do that anymore! Before: Pre-2020.1 the world was broad and flat. Locked projects drove the same permissions down to all its sub projects which triggered the proliferation of more top-level projects. User navigation of the site is much more challenging with so many projects. This limitation also led some to stand up additional sites, which has the downside of creating a “hard” boundary to sharing data sources and can make collaboration more challenging. Sites require duplicate project hierarchies, increasing the effort to create, permission, and manage across them. After: Your site structure reflects your organization’s depth. You can now lock a project at ANY level in your site’s project folder structure regardless of whether the parent is locked with different permissions.  That allows you maximum flexibility to structure and permission your site in ways not possible before Tableau 2020.1. Why would I want to use locked nested projects? Many organizations want to manage their content and permissions in ways that mimic their organizational structures. Think of all the potential benefits. Now you can empower project leaders or owners to lock sub projects with the permissions that meet their specific group needs at any level in the hierarchy. You free up admin time by delegating to the folks closer to the work, and help your Tableau site to be better organized and governed. Locked Nested Projects simplifies permissioning by allowing you to: Lock sub projects independently Simplify top-level projects Group similar projects together Ensure access consistency Ease admin burden by delegating to project owner/leaders Organizations aren't flat, and neither is the way you govern your content. You can now organize your Tableau site and projects exactly the way you want—by department, region, content development lifecycle, or perhaps a combination. Below are three common examples of organizing projects that can take advantage of Locked Nested Projects. Each color represents different group access that could be locked in place at the sub project level and below. Overall, we strongly recommend using locked projects along with group permissions rules to help ease the management of a Tableau site. Unlocked projects can promote a wild west culture, where everyone manages their own content permissions differently. In contrast, locked projects ensure consistency of permissions across content and provide the ability to delegate the admin role to project owners or leaders who know the appropriate details for each group’s content.  How do I use locked nested projects? You can apply nested projects to an existing project hierarchy regardless of when you created it. Click the three-dot Action menu - Permissions... for a project. Click the Edit link in the upper left of the Permissions dialog. Click Locked and the check Apply to nested projects. It’s very easy to overlook the new check box when you click to edit the locked settings for a project. Where can I find out more about locked nested projects? There are many resources available to get you started with locked nested projects: Tableau Help: Lock Content Permissions Tableau Help: Use Projects to Manage Content Access Tableau Blueprint: Content Governance Tableau Community: V2020.1 Nested Project Permission (Thanks to Mark Wu)

Creating a user is simple right? Yes and no. First of all, at least in SSMS it appears you don’t have a GUI. I don’t use the GUI often unless I’m working on a T-SQL command I haven’t used much before but this could be major shock for some people. I right clicked on Security under the database and went to New -> User and a new query window opened up with the following: -- ======================================================================================== -- Create User as DBO template for Azure SQL Database and Azure SQL Data Warehouse Database -- ======================================================================================== -- For login <login_name, sysname, login_name>, create a user in the database CREATE USER <user_name, sysname, user_name> FOR LOGIN <login_name, sysname, login_name> WITH DEFAULT_SCHEMA = <default_schema, sysname, dbo> GO -- Add user to the database owner role EXEC sp_addrolemember N'db_owner', N'<user_name, sysname, user_name>' GO Awesome! I did say I preferred code didn’t I? I am noticing a slight problem though. I don’t actually have a login yet. So I look in object explorer and there is no instance level security tab. On top of that when I try to create a login with code I get the following error: Msg 5001, Level 16, State 2, Line 1User must be in the master database. Well, ok. That’s at least a pretty useful error. When I connect to the master database in SSMS (remember, you can only connect to one database at a time in Azure SQL DB) I do see security tab for the instance level and get the option to create a new login. Still script but that’s fine. -- ====================================================================================== -- Create SQL Login template for Azure SQL Database and Azure SQL Data Warehouse Database -- ====================================================================================== CREATE LOGIN <SQL_login_name, sysname, login_name> WITH PASSWORD = '<password, sysname, Change_Password>' GO So in the end you just need to create your login in master and your user in your user database. But do you really need to create a login? No, in fact you don’t. Azure SQL DBs act like partially contained databases when it comes to users. I.e. if you one of these commands you can create a user that does not require a login and authenticates through the database. CREATE USER Test WITH PASSWORD = '123abc*#$' -- SQL Server ID CREATE USER Test FROM EXTERNAL PROVIDER -- Uses AAD That said I still recommend using a login in master. You can still specify the SID and that means that if you are using a SQL Id (SQL holds the password) you can create a new DB and associate it to the same login without knowing the password. The post Adding a user to an Azure SQL DB appeared first on SQLServerCentral.

I started to add a daily coping tip to the SQLServerCentral newsletter and to the Community Circle, which is helping me deal with the issues in the world. I’m adding my responses for each day here. Today’s tip is to set a goal that links to your sense of purpose in life. My purpose in life is to help others. I do this in a few ways, but one of them that I’ve been wanting to tackle is to find a new way to volunteer locally. Today, I’m setting a reminder to take my volunteer day from Redgate this year and spend it with a local group. The post Daily Coping 5 Nov 2020 appeared first on SQLServerCentral.

This is a quick blog to help me remember what is going on with the Data Masker product. This is for the SQL Server version, but I believe the Oracle one is very similar. I added a new column to a table, and I had a masking plan already built. How do I get my masking plan to show the new column? Here is my masking plan: I added a new column to the DCCheck table, which is under rule 01-0026. If I open that mask and add a new column, I get this, but I can’t expand the dropdown. All the columns in this table are masked, and data masker doesn’t know about the new one. I need an updated schema, as the rules do not update in real time. To get this to work, I need to return to the masking plan and double click the controller at the top. This is the schema manager for my set of rules. Note: If I mask different schemas, I need different controllers. Once this opens, I can see my connection to a database. In my case, I’m building this in dev areas, so it’s pointed to the QA environment. If I click the “Tools” tab at the top, I see lots of options, one of which is to refresh. Once I pick that one, I have a bunch of more options, which gets confusing, but I can click the “refresh all tables” at the top, leaving everything alone. Once that’s done, I get a note. Once I get this, I can return to my rule, and when I add a new column, and I see it listed. This isn’t the smoothest flow, but data masker isn’t something that is likely to be in constant use. For many of us, adding new schema items is relatively rare, so we can adjust our plans as needed. The one good thing is that I can easily find where I need to add a column, as opposed to digging through a number of .SQL scripts. The post Redgate SQL Data Masker Refreshing Schema appeared first on SQLServerCentral.

In my quest to respond to every T-SQL Tuesday since the dawn of the end of 2009, it was only a matter of time before Rob Farley’s name came up. I first met Rob at his 40th birthday party, many (many!) years ago at the PASS Summit. He of course has no recollection of this-> Continue reading T-SQL Tuesday Retrospective #003: Relationships The post T-SQL Tuesday Retrospective #003: Relationships appeared first on Born SQL. The post T-SQL Tuesday Retrospective #003: Relationships appeared first on SQLServerCentral.

I started to add a daily coping tip to the SQLServerCentral newsletter and to the Community Circle, which is helping me deal with the issues in the world. I’m adding my responses for each day here. Today’s tip is to find a new perspective on a problem you face. I’m struggling with some motivation lately. This is conference season, and even with the pandemic, it seems like I have no shortage of deadlines, but I also seem to be finding lots of content to watch. I find myself constantly inspired by seeing what others are doing with technology. And yet, I am struggling to actually do something. Mostly, while I dabble, I find myself struggling to focus and generate some feeling of accomplishment. Really, I feel like I’m not really getting anywhere with a huge variety of new things happening around me. I’m falling behind. I bet many people feel the same way, but really, I am trying to turn this around. I am learning a few new things, and getting the chance to work with tools, and so I have turned my view. Rather than looking at the large group of things I’m missing, and what percentage I am not working with, I am trying to focus on the thing I am working on, and valuing the skills I do build. The post Daily Coping 4 Nov 2020 appeared first on SQLServerCentral.