The Best Days

I recently asked about the worst days in your career, so today let’s turn it around. What are the best days of your career? If you can give us a list of 2 or 3 days, projects, accomplishments, etc., it might be fun to read.

Throughout my career, I have lots of little successes where I accomplish something that a client needs. Those add up, and they’re good, but by nature there are so many and the numbers tend to blur the lines in my mind. Unfortunately, the mistakes I’ve made tend to be more memorable and stick in my mind. I really have to think about what I would consider the best days.

There are a couple software deployments that I consider to be successes, though none overly memorable. Getting my code into production is great, but rarely is a change a huge win. Instead, it takes time for clients to use the code, to understand the impact, and for me to be sure that the code works as expected.

I remember getting our first automated deployment done, almost twenty years ago, when we had moved from a manual process of collecting scripts and trying to ensure they were correctly ordered and contained all the changes we needed. After weeks of working on an automated process to collect, order, and execute scripts, we tested things in QA. Then a day later in production. Overall this was a non-event, with the deployment taking minutes. The change from constantly checking scripts, results, and manually executing code was amazing and the reduction of time spent getting ready was notable.

I also remember a great day as a DBA, when we experienced a server crash and had to rebuild new hardware. I was away with family, but I knew my backup and restore procedures were ready. I walked a manager through restoring a database, using a script to automatically build the log restore scripts, and then executing those scripts. While driving with my family, completely hands off, I helped recover a database.

I hope you have some good memories of your career, and can share one or two today.

Steve Jones

The Voice of the DBA Podcast

Listen to the MP3 Audio ( 3.0MB) podcast or subscribe to the feed at iTunes and Libsyn.

 

Posted in Editorial | Tagged | 1 Comment

Format a Partial Script with #SQLPrompt

SQL Prompt is amazing and it’s my favorite tool. It’s actually what many people want first from Redgate because it makes coding much easier. One of the most used features is the reformat feature (CTRL+K,Y), since formatting really matters to developers.

One of the neat features of SQL Prompt is that you can format just a portionof your script. That means if you’re working on a large script and want to reformat just a part of it, you can.

Here’s how:

  • Highlight the code
  • Hit CTRL+K,Y

promptformat

Posted in Blog | Tagged , , | Leave a comment

Getting the Role Permissions–#SQLNewBlogger

Another post for me that is simple and hopefully serves as an example for people trying to get blogging as #SQLNewBloggers.

If you are wondering what permissions a role has, and don’t want to spend time searching and digging through Books Online, there are a few system procedures that can help. These will give you the permissions for a role, or all roles.

Let’s see how to use these.

Server Role Permissions

Let’s say that you are wondering what a Processadmin can do. There is a procedure, sp_srvrrolepermission, that you can use to get the list of permissions. This can be run with no parameters, in which case you’ll get all roles and all permissions.

2018-07-03 14_46_01-SQLQuery9.sql - (local)_SQL2016.EmptyFileTest (PLATO_Steve (74))_ - Microsoft SQ

However, often you’ll want a more manageable set of data that a person can understand. Let’s see what processadmin can do.

2018-07-03 14_45_47-SQLQuery9.sql - (local)_SQL2016.EmptyFileTest (PLATO_Steve (74))_ - Microsoft SQ

Only two permissions here, so I can see that granting this isn’t going to affect security or databases.

Database Roles

The database has a related procedure, sp_dbfixedrolepermission, that looks for database roles. This can again be run without any parameters, as shown here.

2018-07-03 14_50_24-SQLQuery9.sql - (local)_SQL2016.EmptyFileTest (PLATO_Steve (74))_ - Microsoft SQ

If we include a role, we see just those permissions.

2018-07-03 14_50_51-SQLQuery9.sql - (local)_SQL2016.EmptyFileTest (PLATO_Steve (74))_ - Microsoft SQ

SQLNewBlogger

This is a short post, really about 5 minutes, based on my need to solve another issue. However, this is something a DBA should know, and it’s something I’ll likely use again.

Posted in Blog | Tagged , , | Leave a comment

What Data is Really Needed?

The GDPR took effect in May of this year, at least with regards to enforcement. A few days after the May 25 date, a German court ruled against ICANN, the company that registers domain names on the Internet and manages the global WHOIS database. The case revolves around the information collected when you register a domain. ICANN wants multiple contacts, which they’ve required for decades. However, a company in Germany that is a partner, argued that the additional technical and administrative contacts were not required for fulfilling the business that both ICANN and EPAG (the German registrar) are engaged in. ICANN Is appealing the ruling, citing the need for clarification of what this means with regard to the law.

This is interesting to me, because a) it concerns data, and b) there is an interesting argument here to be made about what data is needed for a business purpose. I could see this being argued successfully either way, and not just in court. As a domain holder, does the registrar really need multiple different sets of personal information from me? Arguably, this is a convenience for them, one that is based on tradition. However, one could argue the other way.

It is a little scary that a court, with no expertise in some industry (Internet domain registration, in this case), will decide if there is an actual business need. After all, can a lawyer or judge really understand what data a business needs in their daily activities?

Maybe, maybe not, but I do think this forces businesses to actually stop and think about what data they collect, have a justification, and document that. That’s a good thing, because often I find business people just asking to collect data without any idea what they’ll do with the information. I also find technical people collecting data, not maliciously, but often to anticipate what might be asked of a system, or because they want to avoid rework and just decide to collect everything they can.

Data is precious, and while I don’t want to put many limits on what data businesses can collect, I also don’t want to them be able to collect anything, not disclose what they’ve collected, and not secure it properly. Having some limits, or at least forcing them to consider the risk of holding old, useless data, is likely a good thing for all of us.

Steve Jones

The Voice of the DBA Podcast

Listen to the MP3 Audio ( 3.7MB) podcast or subscribe to the feed at iTunes and Libsyn.

Posted in Editorial | Tagged , | Leave a comment

Maintain Your Trustworthiness

Many of us that are DBAs and/or sysadmins find ourselves with privileged access to many systems. We can often read the data that’s stored in these systems, whether that’s a relational database, a NoSQL store, or even a mail system. As a result, it is incumbent upon us to be trustworthy and maintain confidentiality with privileged information.

Overall I think most of us do this, but there are always some rogue administrators out there, some of which might take malicious actions. There have been a few people that were arrested or sued for hacking into systems, trashing backups, or causing other issues. Often those are emotional outbursts that disrupt operations, and many people are aware there is an issue. However, what if people weren’t aware they were being hacked in some way?

I ran across this story about some “admin” software being sold on a hacker forum site, which was marketed as sys admin software, but used to control other people’s computers without their knowledge. This is essentially a remote access trojan application that a developer sold to others who used it to steal data from their victims. The software developer was arrested and signed a plea agreement, knowing that it was used in a malicious manner.

For those of us that have privileged access, we might learn passwords of users as we watch them enter the value over and over. We certainly might work with their data to help them solve an issue or understand the manipulation taking place. We We may have auditing systems or logs that allow us to replay or examine the data values people have entered into applications. We do this with their permission and understanding, or at least someone’s permission. A user might not know we have substantial instrumentation, perhaps even the equivalent of a keystroke logger, but there will be some management that is aware of the existence of these tools.

While I’ve played a few pranks on people, moving keyboards or mice, I’ve always ensured they knew it was me in a short period of time. Using admin software to spy on others without their knowledge is a breach of trust and ethics, in my opinion. Even being asked to use this by management would be immoral for me.

Knowing this type of software exists, is important, and if you find it, I’d make sure you report it to management immediately, preferably to a few different people. If anyone is using tools to spy on users, they’re likely up to no good and I’d hope we would all attempt to put a stop to the practice.

Steve Jones

The Voice of the DBA Podcast

Listen to the MP3 Audio ( 4.6MB) podcast or subscribe to the feed at iTunes and Libsyn.

Posted in Editorial | Tagged | 2 Comments

The Master of the Schema

“The database … should be the master of the schema”.

It’s not often I see an application developer talk about the importance of the database, or at least that the database (and data) are very important to the success of the application, but in this case, that’s what I saw. The quote is from this blog, which talks about the problems of the code first approach to building an application. It’s from a Java development company, though it’s their jooQ product blog that actually generates Java code from the database, so they get the need to pay some attention to the database.

The thrust of the article is that using an ORM is fine, but this isn’t necessarily the best way to design your datastore if you need a relational system. There will be mistakes made in naming objects, in structuring them and ensuring indexes exist and more. The longer you go with generated database code from some code-first type system, the more issues you might have later as you try to modify both the application and database, especially the database, since you can’t drop it and recreate it.

If we could drop the tables and rebuild them, life would be great. Maybe we should keep copies of data on the clients, and just reload the necessary data after a deployment… It would make my life easier as a database professional, but I suspect this isn’t the best way to ensure data quality and consistency (not to mention completeness).

I agree that if you’re building an application, you should go database first. At least start with some basic structures and learn to modify them. The practice you get modifying code, with scripts not GUIs, will be invaluable later to both developers and DBAs/admins. You’ll start getting practice and understand what it means to deploy scripts to your database. After all, making schema changes with scripts in your development database is the first “practice” you get for deployment. If you can’t get the scripts to work here, how would you have any confidence they’ll work in production?

Steve Jones

The Voice of the DBA Podcast

Listen to the MP3 Audio ( 3.1MB) podcast or subscribe to the feed at iTunes and Libsyn.

Posted in Editorial | Tagged , | Leave a comment

Another Brick in the Wall–T-SQL Tuesday #105

tsqltuesdayIt’s that time again, and this month we have a good topic from Wayne Sheffield. We’re asked about getting stuck, about being blocked, about encountering a brick wall.

I have to say that I don’t think I encounter these very often, usually because I try to be effective, even if things get ugly in code. The important thing for me is to deliver value. That doesn’t mean I build slapdash applications and leave them. I do my best, and try to continue to learn to build things better. However, in the heat of the moment, I might need to do something silly that I end up either rewriting later or having someone else rebuild the solution.

With that…

The Large Aggregate

I once worked for a financial services company. This was decades ago, back in the age of spinning disks, SQL Server 6.5, and flip phones. We had a number of clients on our main application, but one large client that dominated our business.

We needed to provide quarterly summaries of activity and portfolio performance for all clients, but for this main customer, they also wanted us to add monthly reports. They were actually their own management firm, but we provided them with back end services. Within a month, they would have millions of transactions that we had to group by some organization and then calculate performance.

This wasn’t a big deal with other clients, though most of them had relatively simple calculations. For this client, however, they had separate rules for certain types of activity and the result was a calculation that we couldn’t easily get into CASE statements or other types of query workflows.

In trying to handle all their requirements, I (and my junior team), got stuck. We couldn’t come up with a set of queries that would work. We didn’t want to go down the road of writing custom queries for each organization as this would be an ongoing nightmare as new requirements were introduced.

We had delay after delay in trying to implement their monthly reporting and pressure kept mounting. Eventually we turned to using temp tables and making different passes through them to handle specific requirements. We had ugly flags in our temp tables to let us know which set of rules we’d applied and which we hadn’t.

The code worked, but it was slow. Too slow, in fact, and my boss wasn’t pleased. After a particular unpleasant dressing down, I was wondering if this was the career for me. I’d talked with friends, posted questions, but in this pre-SQLServerCentral era, there were limited places to get assistance. I had hit brick wall.

I never got to find a solution to the problems. My boss at the time was one of my worst, and he became petty. I started to get assigned various inane tasks, which were slightly annoying. However, on a night off, while on a date with my wife at a concert, he paged me with an emergency. I had to leave the show to answer and call in. When I did, he told me this was just a test to see if I would respond, then berated me for taking more than 5 minutes to do so.

I quit the next day.

Perhaps not the best reaction, but between the brick wall and poor treatment, I decided to move on. Hopefully someone else managed to solve the the performance issues and help the client.

Posted in Blog | Tagged , | 4 Comments

The New SQL Provision Dashboard

As much as I liked the ability to quickly and easily build development and test databases with SQL Provision, I thought the dashboard of cloned databases was hideous. It left a lot to be desired, and frankly, the dark theme is annoying to me. Here’s my old dashboard.

2018-08-06 10_52_18-Microsoft Edge

I wasn’t alone, as various customers were asking about enhancements and additions. The team has been listening and I talked with them a few months ago during a meeting about possible ideas and designs. I saw an early mock up, and was hoping it would be released soon.

After coming back from vacation, I saw an update was available, so I applied it. After a few minutes, I saw this:

2018-08-06 10_51_11-Socrates - VMware Workstation

Once this was done, the page refreshed, and I saw the dashboard. I know, not much has changed, but look at the upper right part of the screen. There’s a blue box that says “Preview new dashboard”.

2018-08-06 10_51_51-Socrates - VMware Workstation

Once you click this, you get a new dashboard, which thankfully doesn’t use the dark theme. What’s nice is that I also get some information at the top of what my activity is. I can see the total clones and images, and the machines that are working or having issues.

2018-08-06 10_52_28-Microsoft Edge

I also have options for resorting the clones and images. I can change the sorting, which is set by the client, not the server. This means one person can see clones by instance, while another can see clones by image.

2018-08-06 10_52_49-Microsoft Edge

If I change this, you can see that I get a new view at the bottom.

2018-08-06 16_24_40-Microsoft Edge

There are more changes needed, and some coming. There is a feedback item when you switch to give feedback to the team, and I’d encourage you to do so. Certainly I think sizes or some calculation of total sizes for images and clones is needed. It would be nice to get filters for sizing, so I can also tell if someone is using a clone to do a lot of work and growing it’s size. One of the important things here is that you ought to not get to wedded to a particular clone. We want to rebuild these as needed.

I’d also like to see some way to link images to a source and perhaps group them so that I know how many copies I have of some database, like production. While I think we definitely need a couple of images at any time for rotation, we want to get control of our systems and limit the number.

If you have other feedback, let us know, and we’ll build a better dashboard together.

Posted in Blog | Tagged , , | Leave a comment

The Worst Day

I challenged people to write about their daily work a few weeks ago. I haven’t see a lot of posts, but I am still hopeful some of you will document your day, as Iris Classon has done a few times. It’s not that I expect a lot of the same posts, but rather, I’d like you to talk about the way a specific day has flowed for you. Did you work on a problem? Just pick up tickets and perform routine scripting? Learn a specific thing? Give us some details. I expect everyone’s day to be different.

Jon Shaulis broke his series in a few parts, one of which was his worst days as a DBA. I thought that was interesting and it brought back from memories for me. Overall, I’ve had mostly good days, and even the crisis situations weren’t that bad.  That being said, there are some tough days, both physically and mentally, and worse, tough for my family.

This week, I wanted to ask you what was the worst day in your career? Or maybe the worst event, since some of the tough times I’ve had actually spanned multiple days. Let us know what happened, but also, what the challenge was for you?

I’ve got a couple items. My first exposure to SQL Server was as a network admin. I helped a group of 4 other FTEs run a large Novell Netware environment of over 1000 nodes. We handled the servers (6 or 7), email, and more for hundreds of employees. We managed Netware servers, Windows (and DOS) desktops, and way too many printers. As a part of a government mandated change, we rolled out a new system at midnight on Jan 1, backed by this new database, SQL Server. We installed an OS/2 server at the end of December and prepared for the cutover from the old system. Since this was a mandated change, we weren’t going to roll back. Ever.

I arrived at work on Dec 31, around 6pm. I planned on getting setup, checking with developers, and being ready. We cut over at midnight and people began using the system. Within 30 minutes, we had problems, including an overloaded SQL Server that would freeze up. We ended up babysitting the server to reboot it regularly, as well as trying to determine the problems. I left work on Jan 2, around noon. That was a bad day.

Another bad way was with a SQL Server 6.5 instance that ran financial services. We detected corruption in a table and got on the phone with Microsoft one afternoon. I worked with support, being handed off from the west coast to Asia to North Carolina throughout the night, trying to debug the problems and extract data. A nice 30+ hour, high stress day for me. After that I kept a pillow and blanket in my desk, which I used a few more times that year.

Those were tough times, but most of my days are great. Some are long, some stressful, but overall, I’ve enjoyed my time doing database work. Can you say the same thing?

Steve Jones

The Voice of the DBA Podcast

Listen to the MP3 Audio ( 5.3MB) podcast or subscribe to the feed at iTunes and Libsyn.

Posted in Editorial | Tagged | 1 Comment

Granting CONTROL on a database–#SQLNewBlogger

Another post for me that is simple and hopefully serves as an example for people trying to get blogging as #SQLNewBloggers.

I wanted to grant a login the CONTROL permission on a database. This wasn’t a simple as I expected. I had a login (JoeDev) with no user mappings or server roles, and a database (EmptyFileTest) that I wanted to grant them permissions on.

My first attempt was this:

GRANT CONTROL on EmptyFileTest to JoeDev

This didn’t work.

2018-07-03 11_20_21-SQLQuery3.sql - (local)_SQL2016.master (PLATO_Steve (60))_ - Microsoft SQL Serve

I’m a sysadmin, so it’s not permissions. Maybe it’s qualification. The database isn’t found as an object, so let’s fix that.

GRANT CONTROL ON DATABASE::EmptyFileTest TO JoeDev

Aha, I’m making progress.

2018-07-03 11_21_22-SQLQuery3.sql - (local)_SQL2016.master (PLATO_Steve (60))_ - Microsoft SQL Serve

The database is found, but the user isn’t. That implies this is an internal permission in the database that can’t be granted to the login. One more try:

GRANT CONTROL ON DATABASE::EmptyFileTest TO LOGIN::JoeDev

Nope.

2018-07-03 11_24_41-SQLQuery3.sql - (local)_SQL2016.master (PLATO_Steve (60))_ - Microsoft SQL Serve

Let’s add the user.

CREATE USER JoeDev FOR LOGIN JoeDev

Now we’ll try this again.

GRANT CONTROL ON DATABASE::EmptyFileTest TO JoeDev

As you can see, the context is the master database. This won’t work.

2018-07-03 11_26_07-SQLQuery3.sql - (local)_SQL2016.master (PLATO_Steve (60))_ - Microsoft SQL Serve

Let’s change the context and execute this again. Once I do that, things work.

2018-07-03 11_27_29-SQLQuery3.sql - (local)_SQL2016.EmptyFileTest (PLATO_Steve (60))_ - Microsoft SQ

We can see this worked in the database properties for our database, under the Permissions tab.

2018-07-03 11_28_29-Database Properties - EmptyFileTest

SQLNewBlogger

Security and permissions are important. A series of short pieces on different aspects of managing, assigning, or using security will teach you a lot and show that you’re cognizant of the need for security for your databases.

This took about 10 minutes to write. Note that I’m showing my process of learning and progress, not just writing about what is required. Think about writing your story, not just the information.

Posted in Blog | Tagged , | 2 Comments