A Fine Slice Of SQL Server
James Serra walks through all of the Microsoft Connect(); announcements:
Microsoft Connect(); is a developer event from Nov 16-18, where plenty of announcements are made. Here is a summary of the data platform related announcements:
SQL Server 2016 SP1 was the huge announcement, but there are several other interesting announcements, including Kafka for HDInsight. Click through for the list.
Comments closedKenneth Fisher uses table partitioning to perform fast loads of data:
Now if this table is paritioned you’d use SWITCH and bring in a new partition.
For those that don’t know, when a table is partitioned, you can create a new empty partition, and a new empty table, load the table, make the table exactly match the partition (structure, check constraints, & indexes for example) and you can SWITCH it in. The SWITCH part is a metadata operation and is fast!
But what do you do if the table isn’t partitioned? Well, I was having a conversation with Andy Mallon (b/t) and he reminded me of something.
Read on for the details. The upshot is that you can take your time loading the second table and once you’re ready to swap out, it’s a quick metadata change. That’s really useful for ETL scenarios.
Comments closedDavid Bermingham looks at high availability within SQL Server on Linux:
With Microsoft’s recent release of the first public preview of MS SQL Server running on Linux, I wondered what they would do for high availability. Knowing how tightly coupled AlwaysOn Availability Groups and Failover Clustering is to the Windows operating system I was pretty certain they would not be options and I was correct.
Well, the people over at LinuxClustering.Net answered my question on how to provide high availability failover clusters for MS SQL Server v.Next on Linux with this great Step by Step article.
The linked article is amazing. It uses a piece of third-party software to perform clustering, so it’s not a free solution. We’ll see if Microsoft is able to build in a full HA solution in the first version of Linux-supported SQL Server, but if not, it looks like there’s an alternative.
Comments closedAnthony Nocentino speculates on the internals of SQL Server on Linux:
OK, so everyone wants to know how Microsoft did it…how they got SQL Server running on Linux. In this article, I’m going to try to figure out how.
There’s a couple of approaches they could take…a direct port or some abstraction layer…A direct port would have been hard, basically any OS interaction would have had to been looked at and that would have been time consuming and risk prone. Who comes along to save the day? Abstraction. The word you hear about a million times when you take Operating Systems classes in undergrad and grad computer science courses. 🙂
Anthony talks about picoprocesses, which causes me to say that containers (like Docker) are probably the most important administrative concept of the decade. If you don’t fundamentally get the concept, learning it opens so many doors.
Comments closedRight, so I am using 58% of my server memory and looking at SQL Server it is consuming approx. 105MB? Well, it’s not. It doesn’t make any sense.
Use resource monitor to really know what is going on.
Click through for the reason why.
Comments closedGrant Fritchey answers a query store question:
When I was presenting on this topic at the PASS Summit a few weeks ago, one great question came up (great question = answer is “I don’t know”), well, I defaulted to an “I don’t know” answer, but my guess was, “No.” The question was: can you take a plan from one server, let’s say a test server, export it in some way, and then import it to production? In this manner, you ensure that a plan you like gets into production without having to clear the plan from cache & generate a plan by running the query.
Great idea.
Read on for the answer, as well as ways to manipulate query store data.
Comments closedGiven the improvements and the availability of the of the programability surface for every edition (with some insignificant & logical limitations) that I have blogged about in
SQL Server 2016 SP1 – Programmability Surface for everyone!, I believe everyone using Microsoft Data Platform has rejoyced greatly. Of course, now everyone can have Columnstore Indexes on every SQL Server edition!
There are some noticeable limitations that were announced right from the start, such as the maximum size of the Columnstore Object Pool (you can find more information about it here – Columnstore Indexes – part 38 (“Memory Structures”)), but there are more limitations to the Standard Editions and inferior ones and it is extremely important to know them, to understand them in order to make the right decision – when your Business is ready/needed to upgrade to the Enterprise Edition of the SQL Server.
If you’re on Standard Edition and excited about using Columnstore, do read Niko’s post. Columnstore won’t work as fast as it does on Enterprise Edition (gotta have a reason to upgrade) but based on what he’s shown thus far, Columnstore is still a good reason to upgrade to 2016 SP1 if you’re on Standard Edition.
Comments closedJan Mulkens reviews three DLM workshops:
Before we continue, I’d like to point out that I was very skeptical about this training at first.
In general I’m not a fan of product specific training, especially not for a broad topic such as DLM.
You always risk just getting a sales person in front of you that wants to rant on about how great their product is.
If you’re unlucky you get comparisons to the leading competing products that apparently are the worst in the world.Since these workshops are part of Redgate’s training program you can imagine my fear before entering them.
I think most people in the community know Redgate as a positive company that’s very active in the SQL Server Community.
I also don’t think I’ve ever seen something bad written about them, either on a blog or just on twitter.
It sounds like it went well overall, and that’s good: understanding how to automate your administrative tasks and deployments is a critical part of modern data management.
Comments closedMichael Swart has a script to check start versus end dates to ensure there is a valid check constraint for date ranges:
Don’t blindly run scripts that you got from some random guy’s blog. Even if that someone is me. That’s terribly irresponsible.
But this query may be useful if you do want to look for a very specific, simple kind of constraint that may match your business specs. These constraints are just suggestions and may not match your business rules. For example, when I run this query on Adventureworks, I get one “missing” check constraint for
HumanResources.Shift(StartTime, EndTime)and when I look at the contents of theShifttable, I get this data:
If you wish non-blindly to run a script you got from that guy’s blog, click through for the script and more details, including a discussion of false positives.
Comments closedMax Trinidad walks through restoring a SQL Server database to a new Linux instance:
So, before you start working or trying to restore your Windows SQL Databases to SQL Server on Linux, there are a few things you may need to do. This will involved installing components which are not installed by default when first build your Linux System.
One thing I had to say with confidence! You WILL find all your answer on the internet. Any of the Linux Distro forum have your resolution to any of the issue you may encountered. Also, most of the time you Linux system will tell you (or gives a tip) what do with missing dependencies.
Other than that, there are Linux Application Updater solution to help keep your system up-to-date.
This is an amazing time to be in the SQL Server space.
Comments closed