In this dialog box, the “TNS Service Name” drop down box should display your entries from the tnsnames.ora file. Next, enter your Oracle User ID and click “Test Connection”, at which point you’ll be prompted for your password. Everything should test successfully at this point.
Now would be a good time to restart. Unfortunately, yes you need to restart…
You can do an additional test via sqlplus. Open a windows command prompt and enter the following:sqlplus user/[email protected][addressname]
(Where addressname is one of your connections from tnsnames.ora)
I readily admit that I’m glad I don’t need to work with Oracle. Nonetheless, if you do need to integrate the two, this step-by-step guide will show you how.
On most database servers, the SQL Server service account is granted full control of the directories that host the database files. It goes without saying that the service account that SQL Server runs on should be able to create, read, write and delete database files. Looking at a sample database on my local server, the .mdf and .ldf files don’t actually inherit permissions from their folder, although the permissions are very similar to that of the folder.
This all makes sense once you read the explanation, but it’s not intuitive behavior. Read Daniel’s gotcha near the end of the post.
If you’re a regular reader of my blog, you probably know I try to approach questions from a unique angle. Instead of blogging about something cutting edge or sexy, I decided to scroll through the list of system views until I found one I didn’t recognize.
The name is pretty self-explanatory, but I never noticed this existed until now. Seems like the type of DMV that I should have known about, but I didn’t. Quick look at BOL, and I got the verbose description from Microsoft:
Andy goes on to compare the outputs from this DMV to methods he’s historically used.
The graph above has tempdb footprint (light blue) stacked on top of used query memory (dark blue) against the left vertical axis. The green period has very limited use of query memory. During the yellow period, a moderate amount of query memory was used. During the red period, a large amount of query memory was used and at a number of points operations spilled into tempdb. As query memory was used more extensively, the CPU:lookups/sec correlation was more disrupted.
Once fully considered, this makes sense: query memory is “stolen” from the database page buffer pool. References to pages in the page pool are “page lookups”, but each time stolen query memory is poked and prodded… well, that’s not a page lookup. But it has CPU cost.
Check out the whole thing; this is a thoughtful look at an interesting data oddity.
Before the client took the last ditch effort of just restarting SQL Server, I checked traces. There were 9 user traces collecting a ton of trace events.
I manually killed them all and suddenly performance returned to normal. Phew! Crisis averted.
As a follow up from that issue, I created a script that stops and then deletes all user traces. We are discussing setting up a job to run this script periodically to keep traces at bay and I am educating the group on proper trace discipline (and Extended Events).
Even Extended Events aren’t free, so the same advice applies.
Finally I’ve nailed down that topic and hopefully that will be my last post dedicated to SQL Server installations on Windows Core.
In this post I will show how it is easy to install SQL Server from a remote computer without remoting to a server, without any GUI, just by using simple command line.
I admit that setting up installation as a scheduled task on the remote machine is not something that ever came to mind before.
Walking through this, we just need to create a secure string for our password and then use the Set-AzureRmSqlServer cmdlet and pass the secure string to -SqlAdministratorPassword argument. Easy as that and we don’t even need to know what the previous password was. With this in mind, I also want to call out that you can only change the password and not the admin login name. While this is not such a big deal, be aware that once you have an admin login name, you are stuck with it.
Mike promises that his next blog post won’t take a month to publish. Here’s hoping he’s right.
I am not taking into account mirroring or AGs. I honestly am not sure how that would affect the process.
Like any time you run DBCC SHRINKFILE this is going to shred your indexes. Take that into account and re-index as needed.
Kenneth shows screen shots, has a step-by-step checklist, and includes common errors. This is a great explanation.
You are supposed to have pre-downloaded Windows Server Installation ISO image.
You can download Evaluation Windows Server from here: https://www.microsoft.com/en-us/evalcenter/evaluate-windows-server-technical-preview
For this example I’ve chosen Windows Server 2016 Technical Preview 5.
Note: Do not try to use 64-bit installation on 32-bit workstation. It won’t work.
After you specify the file click “Next”.
Read the whole thing.
In SQL Server 2016 there are a lot of goodies under Compatibility Level 130, such as performance improvements with Columnstore and In-Memory OLTP and all the Query Optimizer enhancements done over the years under Trace Flag 4199. So we are faced with a situation where there are workloads that can benefit from some enhancements that come with Compatibility Level 130, but that cannot use the new CE.
I’ve had a very positive experience with the new cardinality estimator, but I certainly appreciate the option being there just in case.