Voice of the DBA

Tag: Redgate

Using SQL Compare with Read-only Access

Recently a customer asked if SQL Compare and SQL Data Compare can be used with a read-only database as a source. It’s a good questions as I’ve seen some tools that create temp tables or do some other work in a source database, which might cause problems. Certainly someone running SQL Compare against production would want to ensure it works as a read-only application.

This post will look at SQL Compare with a read-write database but a user account with read-only access. My previous post looked at a read-only database.

This is part of a series of posts on SQL Compare.

Setup

I’ve got a couple of databases that I use for Compare demos. In this case, compare5_prod and compare1. The compare5_prod is set to read write, which is normal.

I created a new login, which will default to the Compare5_prod database.

This login maps to a user, which has read only rights in the database.

In my compare setup, I’ll use this login to connect to the database.

As you can see below, Compare works fine, even reading the various system metadata tables:

This works because SQL Compare is not writing anything to the database. We read metadata and then process that in-memory on the client before returning the results.

You can see this also works in SQL Data Compare. Here’s the connection:

And here are the results

Summary

This was a very simple example, but I find that clients always would prefer to see examples already completed and proof that something works when they are evaluating software. Hopefully this helps answer this question.

SQL Compare is an amazing tool that millions of users have enjoyed for 25 years. If you’ve never tried it, give it an eval today and see what you think.

November 20, 2024
Monday Monitor Tips: Tracking The Cost of Instances

One of the things that I’ve been asked in every operations situation is what licenses do we need for our servers. This is a rare request, often once a year, but it results in a bunch of work to figure out what’s running.

I hate these requests because they always cause delays in other work.

This is part of a series of posts on Redgate Monitor. Click to see the other posts

What’s Running?

Redgate Monitor makes this easy to track (if you’re monitoring all your servers). In the Estate tab, there is a Licensing item. If you check this out, you see something like this:

This is a quick view of our systems, letting me know what cores we have in service based on the edition. This is a quick report, and if you’re monitoring all prod systems, this gives you a pretty close look at what you need.

The exceptions here are that we are reporting all cores for all nodes in HA/DR setups, and that might be more licenses than you need. However, you can subtract all the standby nodes if you have Software Assurance on those systems since you do not need to license those.

Of course, you shouldn’t need to license dev/test systems if you run developer edition, and you should.

You can filter by groups or other items that can help you focus on part of your estate. This is the standard Redgate Monitor at the top set of filters.

There also is an “Export” button on this page, so you can save off your data as an Excel XLSX.

Summary

This section of the Estate tab gives you a quick view of your obligations to Microsoft for licensing. It’s a nice way to track this and report on it when you need to audit this information without spending a lot of time compiling the information.

Your Finance group will thank you.

Redgate Monitor is a world class monitoring solution for your database estate. Download a trial today and see how it can help you manage your estate more efficiently.

November 11, 2024
Monday Monitor Tips: Projecting Disk Space

One of the things that many DBAs struggle with is managing space across an estate. There might be one or two servers that you watch closely, or that are a constant problem, but it’s easy to run out of space on other systems.

Redgate Monitor will alert you to low space, but do you want to keep asking SAN admins for more space every week as new systems run out of space? Or do you want to project a number for the year and get that budgeted? Learn how Redgate Monitor can help

This is part of a series of posts on Redgate Monitor. Click to see the other posts

An Estate View of Storage

The estate view of disk usage shows all your disks totaled up and the projections for growth. This looks like the image below on monitor.red-gate.com. This doesn’t seem useful, but I’ve had storage admins come to me in the past asking about budgeting for the next year. How much space will I use?

The answer is often I don’t know. I’d have to take a very wild guess for them. Now, I’d use this, and maybe pad it slightly, but across lots of systems, we can probably guess on the growth that will be needed. Any one system might not be predictable, but across lots, averages work out.

Here I can see we’re using 25TB (out of 87TB). Definitely be careful showing this to storage admins, as they often want those to be closer, ubt I can see I’m projected to use 91TB in a year, so I’d need more space. The graph above shows me running out of space in Sept/Oct of 2025.

Below this I see details from machines and their disks. These show in the same groups I see on other screens. While I can’t easily filter here, I can search from a browser, or export this information. That’s useful for checking on individual systems.

The other nice thing is I can sort the columns, and if I do this by time until full, I can see the disks that might give me problems soon.

None of the test systems are likely to be problematic soon, but certainly someone needs to budget for next year.

Summary

This is a simple screen, but one that I wish I’d have had in the past. It gives me a lot of information that I rarely need, but information that always requires a lot of effort to compile. What disks are being used by my database servers and what percentage of space is in use.

In a DBA team, this is the type of data I’d want to glance at every quarter and then make adjustments to prevents issues. Proactive DBAs want a screen just like this.

Redgate Monitor is a world class monitoring solution for your database estate. Download a trial today and see how it can help you manage your estate more efficiently.

October 28, 2024
Using SQL Compare in Read-Only Databases

Recently a customer asked if SQL Compare and SQL Data Compare can be used with a read-only database as a source. It’s a good questions as I’ve seen some tools that create temp tables or do some other work in a source database, which might cause problems. Certainly someone running SQL Compare against production would want to ensure it works as a read-only application.

This post will look at SQL Compare with a database set to read-only. I’ll do a second post on a login that only has read authorization.

This is part of a series of posts on SQL Compare.

Setup

I’ve got a couple of databases that I use for Compare demos. In this case, compare5_prod and compare1. The compare5_prod is set to read only, as you see below.

My connection is as a sysadmin, but that doesn’t override a read-only database. As you can see below, Compare works fine:

This is because SQL Compare is not writing anything to the database. We read metadata and then process that in-memory on the client before returning the results.

You can see this also works in SQL Data Compare.

Summary

This was a very simple example, but I find that clients always would prefer to see examples already completed and proof that something works when they are evaluating software. Hopefully this helps answer this question.

SQL Compare is an amazing tool that millions of users have enjoyed for 25 years. If you’ve never tried it, give it an eval today and see what you think.

October 21, 2024