A Fine Slice Of SQL Server
Kevin Hill explains some of the basics of security using an extended metaphor:
Imagine if you will, that you own a house.
Because you are the owner, you have a key to the house. You can do anything you want in any room of the house. You decide who else gets keys, and what they can do inside. We’ll assume your significant other also has a key and equal rights to all rooms.
Read on for the rest of the story.
Comments closedBrent Ozar reports on Google cloud improvements:
Google Compute Engine is infrastructure-as-a-service (IaaS), selling virtual machines by the hour like Azure VMs and AWS EC2. You can run whatever you like in these VMs, and Google has long supported running SQL Server in GCE. You could build your own SQL Servers, or use pre-built (and licensed) instances of SQL Server 2012, 2014, or 2016 – but only Standard or Web Editions.
Today, GCE supports Enterprise Edition AND Always On Availability Groups.
We’ve got a white paper coming soon on how to build and test it, plus more cool stuff in the pipeline that DBAs will love.
We live in interesting times.
Comments closedSlava Murygin troubleshoots connectivity issues with SQL Server on Linux:
After you enter SA password you have to get “vNext” version of your SQL Server.
If you did not get the correct response you might have following problems:
I) Wrong SA password. To fix it, just re-configure SQL Server.
II) SQL Server Tools are not installed.
III) Typo. Check your syntax.
The troubleshooting process is a bit different from SQL Server on Windows, but it’s still pretty straightforward.
Comments closedAngela Henry has a script to tame percent growth on database files:
I decided I needed to do something else other than just send an email notification, I needed to take corrective action when it occurred. So I wrote a little stored procedure that will take the ALTER DATABASE statement as a parameter, parse it and take the appropriate corrective action.
Simple enough, right? Now I just need to add the call to my newly created stored procedure in my server level trigger and we are good to go. But wait, you can’t ALTER a database within an ALTER DATABASE statement (don’t believe me? Use this as a learning exercise to see what happens when you try). So what could I do? There are several things you could do, but I chose to create a table that could hold this newly created ALTER DATABASE statement and insert the record there. Then I created a SQL Agent job that runs once every hour and reads that table and executes any entries it finds, then deletes them after successfully executing.
Read the whole thing, including the disclaimer.
Comments closedAndrea Allred notes that there’s now a time zone system table, as of SQL Server 2016:
Yes, it is 132 rows of magic! Now that we have this super cool table, how do we use it? Let’s pretend that my data is time-stamped in US Mountain Standard Time, but I want to display it in Western Australia Standard Time.
Read on for table details.
Comments closedMichael Sorens has some time-saving defaults for Powershell:
Besides setting up some convenient shortcuts for use with built-in cmdlets, for developers it is also handy to be able to work some magic with your own custom cmdlets. In my shop, for example, we have one module containing a couple dozen cmdlets, many of which use a common parameter,
Mode. ThisModeparameter varies from client to client, but for any single client, every cmdlet working on their data needs to use the same value ofMode. So I have to add-Mode hist-0010-dev.testonto each cmdlet I am using, which gets very tiring/annoying very quickly. You could, of course, put that value into a variable and then just use, e .g.-Mode $myMode, which is less typing, but if less is better, then no typing at all is better still.
There are a lot of tips in this article, so take some time with it.
Comments closedDavid Smith notes that CRAN is now up to 10,000 packages:
Having so many packages available can be a double-edged sword though: it can take some searching to find the package you need. Luckily, there are some resources available to help you:
MRAN (the Microsoft R Application Network) provides a search tool for R packages on CRAN.
To find the most popular packages, Rdocumentation.org provides a leaderboard of packages by number of downloads. It also provides lists of newly-released and recently-updated packages.
R is a big language; having good heuristics for figuring out where to find appropriate packages is extremely important.
Comments closedPaul Randal explains an uncommon wait stat:
Last week I was sent an email question about the cause of LOGMGR_RESERVE_APPEND waits, and in Monday’s Insider newsletter I wrote a short explanation. It’s a very unusual wait to see as the highest wait on a server, and in fact it’s very unusual to see it at all.
It happens when a thread is generating a log record and needs to write it into a log block, but there’s no space in the log to do so. The thread first tries to grow the log, and if it fails, and the database is in the simple recovery mode, then it waits for 1 second to see if log clearing/truncation can happen in the meantime and free up some space. (Note that when I say ‘simple recovery mode’, this also included a database in full or bulk_logged, but where a full backup has not been taken – i.e. the database is operating in what’s called pseudo-simple.)
Read on for more details and a repro script.
Comments closedMy personal website is a static site: 100% HTML, JS, and CSS files with no server-side processing. I have custom code that pulls data from a variety of sources and builds updated versions of the files from templates, which are then deployed to the host. I do this to move the CPU latency of building the pages to my time, instead of charging it to visitors on each page hit. While I have a host, a strategy like this means I could also choose to host for free via github or similar services.
So there’s a great benefit to the reader and our wallet, but no server-side execution makes things like contact forms trickier. Luckily, Azure Functions or AWS Lambda can be used as a webhook to receive the form post and process it, costing nothing near nothing to use (AWS and Azure both offer a free tier for 1M requests/month and 400,000 GB-seconds of compute time).
Eli has a working example in the post, which I recommend checking out.
Comments closedRobert Davis looks at messages in the error log related to memory-optimized tables:
The server on which we are running in-memory OLTP is a really hefty server with 128 logical cores and 1.5 TB of RAM (1.4 TB allocated to SQL Server). We are limiting in-memory’s memory usage with Resource Governor, which also makes it easy to see how much it is using. Needless to say, even with a limited percentage of 1.4 TB of RAM is still a lot of memory. The highest I have seen in-memory usage for this one database reach at peak activity levels is ~43 GB. In production, when the heavy in-memory OLTP processes complete, I see the system reclaim the in-memory buffers pretty quickly, though not completely. During a normal day, I often see the in-memory memory usage hovering between 1 and 3 GB even when there is virtually no traffic.
When testing in-memory on a dev server that only I was using before deploying to production, I noticed that the memory usage would stay at whatever high level it reached. This makes me believe that in-memory buffers are cleaned up and reclaimed as needed, and if not needed, they just hang around as in-memory buffers. And it appears that some of the buffers end up hanging around. Perhaps they wouldn’t if the server was memory starved. I have not tested that theory.
It’s a conjecture, but seems pretty solid. Also worth reiterating is that they’re warnings, not errors.
Comments closed