Had a quick interview at Microsoft on Channel 9 last month, and it’s now live. Enjoy.
https://channel9.msdn.com/Blogs/vsppstories/Including-the-database-in-your-DevOps-pipeline/player
Author: way0utwest
-
Easily Move Databases with Copy-SqlDatabase
One of the things that people have asked to be implemented for many years is an easy way to copy databases. SSIS has the Copy Database Task, but that has been problematic over time. As a result, while easy, it’s cumbersome to take a backup of a database, copy it to a new instance, and restore it. Or use the detach/copy/attach/attach method.
dbatools gives us a nice, easy Posh command to perform this task: Copy-SqlDatabase. I made a quick test recently to see how this works. Using the –Whatif option, I tried to copy a database from one instance to another on my main computer.
My first attempt didn’t do well.
I was thinking there would be a default method, but there isn’t. You must specify using the backup/restore method or the detach/attach method. I decided to try the detach/attach method.
As you can see, this looks to detach and then reattach the database, so let’s try it. First, check my QA instance. There is no DBAAdmin database.
This is a small database with just a few procs and tables in it. When I remove the –Whatif option, it works.
I saw the green PoSh flash in the middle as the file was copied from one location to the next using the BITS method. When I looked in my SQL2016_qa instance, I saw the database and objects.
All in all, this took about two minute for me to move this database. Certainly that is about the time of a backup, but I did this with one command, and it felt much, much easier.
There was a downside for me. I need to double check this, but I didn’t get the database re-attached at the source, and I had to do that myself. The files were there, just not attached. Perhaps it’s just me, so I’ll retest when I get the chance, but it seems to be a minor bug.
I also tried the backup/restore option here. I had to provide a fileshare, which I chose to be my local D: drive. Note, this has to be provided or a popup will appear. This also has to be \\server\share, not d:
This worked, with the new database appearing on my second instance.
The backup was also removed from my drive:
Overall, this is handy, and I’ll likely use this as my default method for moving databases between instances.
-
The One Metric to Rule Them All
When we want to know if our databases are running well, most of us look at metrics for our servers. Usually some combination of CPU, RAM, Disk IO are chosen, though network, user connections, batches and transactions can be a part of tracking performance. I would guess if I asked any of you that are responsible for performance whatmight be a good measurement for your most important database server, you’d have some idea of what CPU, RAM, or something else should be running at. I know I can easily check on SQLServerCentral.com at monitor.red-gate.com and usually expect to see CPU in the 20-25% range. If it’s substantially higher, I know something is going on.
I was listening to one of the DevOps Cafe podcasts recently where the subject was monitoring your infrastructure and how well it was performing. and one of the hosts mentioned listening to Werner Vogels from Amazon years ago. Apparently despite all the technical measurements that were available for Amazon’s IT infrastructure, the one measure that they most often used to track how well everything was working was the order rate. Given their size and experience, Amazon could look at order rate and decide if their systems were performing well, both hardware and software.
Most of us might not be as tightly focused on our business, but I’m wondering if any of you use business based metrics when examining the performance of your hardware and software. Is there a metric for your business that might help you determine if your hardware is actually performing as expected? Or if your code does what the business needs in an application?
At SQLServerCentral, we have various ways of analyzing our systems, but we have added some business based metrics. One of those is forum posts/hour. We should see a similar curve throughout the week that shows how many posts we’re getting. Across years of data, we have a fairly steady rate of posts that occur on a regular basis. If we’re not getting a steady set of posts, we know something might be wrong.
Perhaps you don’t have any business based metrics that you use, but are there any that might make sense for your industry? Let us know today. And if you think that there isn’t a good way to measure your hardware and software with business metrics, I’d be interested in your explanation as well.
Steve Jones
The Voice of the DBA Podcast
Listen to the MP3 Audio ( 3.8MB) podcast or subscribe to the feed at iTunes and Libsyn.
-
What Port Should I Use?–#SQLNewBlogger
Another post for me that is simple and hopefully serves as an example for people trying to get blogging as #SQLNewBloggers.
This is a quick post on checking the port for your SQL Server instance. It’s in line with Tim Ford’s request for simple, beginning blog posts this year.
How do you know what port your instance is listening on? Many of you might not know, depending on the SQLBrowser to be running. That’s not necessarily a good idea, as some security protocols insist this be disabled.
There are ways to check. First, you could use a the Configuration Manager. On my desktop, this is in the Manage tool for the computer. Once inside, I can select the Server Network Utility.
Notice that I have multiple instances here, so I need to choose one. Once I do, I see the protocols on the right. In this case, I want to look at the properties of TCP/IP, which is where I’ll get the port.
If I look at properties, I’ll start with the Protocol tab, but I want to switch to the IP Addresses tab. In here, you can see I’ll see an entry for each of the IPs my instance is listening on. I can see which ones are Active as well as the port. In my case, I have these set to dynamic ports.
This doesn’t help. If I had specific ports, I’d see them listed for each IP.
The way I tend to check the specific port is that I will go to the SQL Server Error log.
When I open this up, I see my error log entries. I want to look at the startup of my current session and scroll up slightly. At some point you will see an entry like “Server is listening on…”
In my case, you can see above that I am listening for this instance on 60087. I can verify this with a connection in SSMS.
Note that this is a named instance, and I normally connect to “.\SQL2016”. The SQLBrowser determines the port, since I send the name in, and then get a port back to where I will then connect. However, here the name doesn’t matter. I just send in the port and I can connect.
#SQLNewBlogger
Everyone working with SQL Server should know this. It’s a simple item, like checking the printer is plugged in. Know how to check this.
And blog about it.
