Monday, 18 April 2016

Can’t RDP to Azure VM?

I ran into an interesting problem early this month. I was spinning up an Azure VM with SQL Server 2016 RC0 on it. For testing purposes. Looking at Stretch databases a new feature in SQL Server 2016

clip_image001

I run through the usual wizard to spin up a VM but I wasn't able to connect to it via RDP.

It didn't seem to make sense.

Then I realised that the port RDP was using on my VM is not necessarily a standard port. I had a look and it was using 3389. It seems the network I was on was only allowing outbound traffic on certain ports. It probably had some sort of white lists for ports you could get out on and blocked the rest.

To test this theory, using the Azure portal I changed the public port RDP was using to a common one - 443 and bang straight in.

Friday, 15 April 2016

Tuesday, 12 April 2016

Weekly Roundup–SQL Server and Related News

Happy Tuesday . Here's the latest technology based blogs and articles I have been reading this week. Mainly SQL Server themed but a couple of others in there for good measure.

SQL Server

Searching for a string in an Object definition in a database. That is find a reference to an object. Ideal of you have multiple environments (UAT/TRAIN) stacked on the same SQL Server instance.

SQL CLR not supported in Azure SQL Databases a breaking news story on brentozar.com

A nice read on Views and Simplification by Grant Fritchey -

SQL Server 2005 is out of support – This feels like the end of an era.

Big Data and Analytics

Mapping Twitter followers in R - http://www.r-bloggers.com/mapping-twitter-followers-in-r/

Enterprise Architecture

The Open Group London 2016 event is taking place in London between 25th and 28th April-

Cloud

How to configure AWS cloud watch for SQL Server performance monitoring -

From the Microsoft blog SQL Server 2016 hybrid cloud solutions - https://feedly.com/i/subscription/feed/http://blogs.technet.com/dataplatforminsider/rss.xml

Other

The Mighty Newport County could do with another win or two, to ensure league football stay at rodney parade next following a 1-0 defeat at home to Cambridge. Come on the County!

SQL Server 2005 is Now Out of Support (12 April 2016)

SQL Server 2005 is now out of support. I remember going to a SQL Server 2005 launch event at the premises of Microsoft partner is South Wales back in the day.

It feels like the end of an era.

If you still have SQL Server 2005 instances and databases you should consider migrating to a later version.

 

 

image

 

We can help you with that SQL Server 2005 migration . Check out our SQL Server Consulting page.

Wednesday, 6 April 2016

TSQL– Search for Strings in Object Definitions

I have been  working nn an environment  that had multiple copies of a database on the same SQL server instance. In this case the SQL Server was acting as both a UAT and Training environment.
The application was supported by two databases. Each database had the need to reference objects in the other using a three part name. 
When it came to to releasing the database to production I wanted to check that all references across databases  were correct that is. That is objects in the databases didn’t access or reference the wrong databases.
I thought I’d make use of the sys.dm_db_uncontained_entities stored procedure to look for objects that span the database boudary.
I came up with the following query that did what I wanted.


SELECT DISTINCT SO.NAME
 ,so.object_id
 ,sm.DEFINITION
 ,UE.class_desc
FROM sys.dm_db_uncontained_entities AS UE
LEFT JOIN sys.objects AS SO ON UE.major_id = SO.object_id
LEFT JOIN sys.sql_modules AS sm ON so.object_id = sm.object_id
WHERE sm.DEFINITION IS NOT NULL
 AND sm.DEFINITION LIKE '%SearchString%'
The above query can be modified to remove the uncontained entities reference to check for a string appearing in any object in the database.

SELECT DISTINCT so.NAME AS ObjectName
 ,so.type_desc
 ,sm.DEFINITION
FROM sys.sql_modules sm
INNER JOIN sys.objects so ON sm.object_id = so.object_id
WHERE sm.DEFINITION LIKE '%SearchString%'
ORDER BY so.type_desc
 ,so.NAME
If we can help with a SQL Server problem feel free to check out our Consultancy Page or Contact Us

Tuesday, 5 April 2016

Weekly Roundup–SQL Server and Related News

Welcome to this weeks weekly round up of links, SQL Server and other news. Its been a busy week on the work front but still made time for some reading. The #Build2016 announcements were very interesting and the most interesting are included here.

David is currently in the process of updating his eBook on the Azure SQL Databases to reflect the changes in the new portal. He will have an initial draft soon and we'll have a blog post announcing the release when its available. Stay tuned…

This weeks links include

SQL Server

SQL Server Developer Edition has been made free. SQL Server developer edition is a fully featured version of SQL Server, basically its Enterprise edition but not licensed for production. There has until this week been a small charge for the edition approximately $/£/€50 it has now be announced this will free. SQL Server 2014 developer edition and the up and coming SQL Server 2016 developer edition will be free.


Dan Buskirk talks about querying SQL Server data from Spark with Scala -

An interesting read on indexes helping indexes

Big Data and Analytics


Some useful R-Studio add-ins


Cloud


Amazon RDS for SQL introduces support for Windows Authentication 

Micrsoft Azure database team host an Ask Me Anything (AMA) session this Friday 8th April

Other

The might Newport County are 'probably' safe but a 1-0  defeat away at Yeovil was very disappointing