R And SSH Tunnels

Kevin Feasel

2016-03-16

R, Security

Steph Locke shows how to set up an SSH tunnel to connect to an external server within R:

Whilst down the rabbit hole, I discovered just in passing via a beanstalk article that there’s actually been a command line interface for PuTTY called plink. D’oh! This changed the whole direction of the solution to what I present throughout.

Using plink.exe as the command line interface for PuTTY we can then connect to our remote network using the key pre-authenticated via pageant. As a consequence, we can now use the shell() command in R to use plink. We can then connect to our database using the standard Postgres driver.

PuTTY is a must-have for any Windows box.

Get Diretory Information For SSAS

Jens Vestergaard shows us how to get the Data, Log, Temp, and Backup directories for Analysis Services using Powershell:

Just recently a reply was made to the Connect item, highlighting the fact, that the current values of the Data/Log/Temp and Backup Directories – meaning the currently configured values – is exposed through the Server.ServerProperties collection. According to the answer, only public property values are exposed.

Using PowerShell, we can now retrieve the desired information from any given instance of Analysis Services. Doing so would look something like this:

It’s good to know that this information is available via Powershell.

Compression Performance

Rolf Tesmer digs into the case of compression of building an index whose leading column has a low cardinality:

That first one is a cracker – it hit me once when compressing a SQL Server table (600M+ rows) on a 64 core Enterprise SQL Server.  After benchmarking several other data compression activities I thought I had a basic “rule of thumb” (based on GB data size and number of rows)… of which just happened to be coincidence!

This also begs the question of why would you use low selectivity indexes?  Well I can think of a few cases – but the one which stands out the most is the identification of a small number of rows within a greater collection – such as an Index on TYPE columns (ie; [ProcessingStatusFlag] CHAR(1) = [P]rocessed, [U]nprocessed, [W]orking, [F]ailed, etc)

… AND SO – lets do some testing to validate this puppy!

There’s a significant difference here, so check out Rolf’s post for the details.

Using The Default Trace

Jon Morisi shows how to use the default trace:

Often times while troubleshooting an issue, you’ll want more details than what you can find in the application log or SQL Log.  In the background, SQL Server runs a default trace which includes a lot of items to help with troubleshooting including (but not limited to) errors, warnings, and audit data.  I often run the following script as a quick way to find additional details for “ERROR” items from the default trace.

Jon notes that the default trace has been put on the deprecation list, so keep that in mind if you do use it.

Pulling Non-Clustered Index Data

Kenneth Fisher shows using a non-clustered index potentially to reconstruct corrupted data on a clustered index:

So why would you want to do this? Well lets say for example you have a table in a database where the clustered index has become corrupted. Let’s further say that no one mentioned this to you for .. say a year. (No judging!) So your only option at this point might be to use the REPAIR_ALLOW_DATA_LOSS of DBCC CHECKDB. But when you are done how much data has actually been lost? Can you get any of it back?

If you’ve lived a good life and are very lucky, you might recover all data this way.  Otherwise, it’s a good idea to run CHECKDB more frequently and check those backups regularly as well.

Identify Page Split Sources

Dennes Torres shows us how to figure out where bad page splits are occurring:

When one page becomes full and a new page is needed this is reported as a page split, but this is a regular operation with no bad consequences for our queries. The problem happens with updates and non-sequential inserts, when the row needs to be inserted in the middle of the pages of the object and there is no space for this. SQL Server creates a new page, transfers half of the page data to the new page and writes the row data. This creates page fragmentation and is very bad for performance and is also reported as page split.

We can find the bad page splits using the event sql_server.transaction_log. This event monitors all the activities in the transaction log, because that we need to use with caution. We can filter the ‘operation’ field looking for the value 11, which means LOP_DELETE_SPLIT. This is the deletion of rows that happens when SQL Server is moving rows from one page to another in a page split, a bad page split.

It’d be nice to be able to find the particular query causing the page split, and it’d also be nice to find a less resource-intensive method of displaying this information.

Documentation Survey

The SQL Server team is asking for your help:

The SQL Server documentation team is working to improve the SQL Server documentation in an effort to enable you be more successful with and gain more value from SQL Server. Please let us know your thoughts on how we can improve the documentation by taking this brief survey by March 18.

We appreciate and are listening to your feedback! Thank you.

The questions aren’t very onerous, so help out the documentation folks and fill it out.

Mobile Reports In SSRS 2016

James Anderson shows off mobile-friendly reports in SSRS 2016:

To install Mobile Report Publisher, click the down arrow in the SSRS portal and select the Mobile Report Publisher. Download the installer and click through to the end. Install the C++ redistributable. If you have used DataZen then the designer will look very familiar.

It’s nice seeing Reporting Services all grown up.

Windows Authentication On Linux

Ryan Adams shows us how to get Active Directory authentication on Linux using Samba and PAM:

Back to our question at hand.  Since the preview just got released I have not had a chance to test this out with SQL Server.  However, you can use Active Directory accounts with Linux and Unix by using Samba and PAM.  I see no reason why this implementation would not also work with SQL Server on Linux, but again I have not yet had a chance to test this out.  Since I already have some written installation instructions, that’s what I am providing here.

Chrissy LeMaire, in the comments, shows how to do it in OpenSUSE.  She also has a brand new blog post on the topic.

Parameterizing Attunity Queries In SSIS

Melissa Coates shows us how to parameterize queries if you’re using the Attunity connector for Oracle in SSIS:

The Attunity connector for Oracle used inside of the SSIS data flow looks a little different than the typical OLE DB connector we most commonly use. Specifically, the Attunity source has two options: “Table Name” and “SQL command.” What the Attunity Oracle Source doesn’t have in this dialog box is “SQL command from variable” (like we see for an OLE DB source).

Expressions are your friend.

Categories

August 2019
MTWTFSS
« Jul  
 1234
567891011
12131415161718
19202122232425
262728293031