SQL

Install Microsoft SQL on Linux – Ubuntu Server

I recently had the pleasure of installing Microsoft SQL Server on Linux – Ubuntu Server. This was a very straight-forward installed and just works. The following steps are what were taken to install and configure this server.

My Setup:

  • Ubuntu 17.10 Server – VMware Template
  • Network Connectivity
  • SQL Server Management Studio 17 – Testing connectivity to SQL Server

Prerequisites:

  • Ubuntu Linux Server – Memory: 3.25, Disk Space: 6GB, CPU (x64): 2 Cores
  • Internet Access – Offline Installs are also possible
  • Root or SU Access
  • Time – 5-6 Minutes

Steps:

  1. Log into Ubuntu Linux server via console or SSH (Preferred), su into root
  2. We need to import the repository GPG Keys by first downloading and adding it with he following command: curl https://packages.microsoft.com/keys/microsoft.asc | sudo apt-key add –
  3. Next we register the repository by entering: add-apt-repository “$(curl https://packages.microsoft.com/config/ubuntu/16.04/mssql-server-2017.list)”
  4. Next we need to upload the repository list and install SQL with the following commands: apt update | apt install mssql-server -y
  5. After the SQL Server package has completed installing.  You will be instructed to run mssql-config setup to setup the SQL Server version you will be installing, in addition to password credentials.  This is done by issue the following command: /opt/mssql/bin/mssql-conf setup
  6. Optional – Open your firewall if enabled to allow for SQL’s TCP/1433 from remote hosts.
  7. Test connecting to your newly install SQL Server via SSMS.
  8. Done!

Screenshot:

Video:

Tech Short: TSQL to find Consumed Memory

Poking around in Microsoft SQL Server looking for a way to give me some statistics on memory usage, primarily consumed memory.

My first attempt was using ‘dbcc memorystatus’.  This gives me a good snapshot of the current memory status of the SQL Server. It will take me sometime to fully understand and parse the output.

So I kept looking for more details on how to show the consumed memory. And that is where I found the following query that gives me just what I am looking for.

 

Declare @MemTable Table
( row_id INT IDENTITY PRIMARY KEY, name VARCHAR(100), value BIGINT );
INSERT INTO @MemTable EXEC ( ‘DBCC MEMORYSTATUS’ );

SELECT top 1 Name,convert(decimal(16,2),value) / 1024/1024 ‘MBUsed’
FROM @MemTable
WHERE RTRIM(LTRIM(name)) = ‘Working Set’;

So if your looking for just the working set here you go

Thank you Jason Brimhall for the tip

O365: Forefront Identity Manager & Office 365 DirSync Failing

I encountered an issue where both Forefront Identity Manager and Office 365 DirSync both failed to start.

My investigation of this after I received an email from @MicrosoftOnline.com which had informed me that Windows Azure Active Directory did not register a synchronization attempt from the Directory Sync tool.

First

I attempted to do was start the Microsoft Online Services Directory Synchronization Service. This had failed because depends on Forefront Identity Manager Synchronization Service which was also no longer starting.

Second

I attempted to start the Forefront Identity Manager Synchronization Service this failed with the following message:

Verify that the service account has permissions to the following registry key: HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\Forefront Identity Manager\2010\Synchronization Service

If the problem persists, run setup and restore the encryption keys from backup.

Third

After my verification I attempted I uninstalled the Office 365 DirSync along with Forefront Identity Manager and SQL which were all installed.  This time around I unable to even install the Office 365 DirSync

All three of my attempts had failed.

So what changed?

I rebooted the system; and after it had resumed the services which worked seemed to no longer function.

Then it *clicked* after much investigation and review.  The question I did not ask.  Could Office 365 exist on the same system that’s also running ADFS.  I soon found out the answer is ” *NO* “.

The Directory Synchronization tool cannot be installed on Active Directory Federation Service.

So I uninstalled the Office 365 DirSync, along with SQL. Followed by the removal of the ADFS Role from the server.

After the restart I installed the Office 365 DirSync again and configured it as I have done before and all is working once again.

And now I and you all know 

I hope this post help you and saves you some time.  I spent a day working on this and waiting for Microsoft to call me.  I seems like I have resolved this issue on my own; once again.

Summery

If your using Office 365 DirSync do NOT enable the ADFS Role if you do, you run the sure chance of breaking your working Office 365 DirSync.

Environment: Windows Server 2012 R2 Update 1 (x64)

– Jermal

SQL Report Server ERROR: The number of requests for domain\jermsmit has exceeded the maximum number allowed for a single user

It’s always simple when you see a error message you know.  This time its with SQL Server Report Server.  Error Message: The number of requests for domain\jermsmit  has exceeded the maximum number allowed for a single user

What does that mean?

Well to keep it simple.  The user is running a report which requires far more request that the report server is configured to handle.

So what do you do about this?

One thing would be to isolate the issue and find out why so many requests are being made and perhaps re/wright the report.  Hahaa! Joke!  That doesn’t happen often now does it.

So what will happen is this:

On the report server navigate to the following path:  C:\Program Files\Microsoft SQL Server\MSRS11.MSSQLSERVER\Reporting Services\ReportServer  *your install path may be different*

Locate the XML configuration file rsreportserver.config

Open the configuration file with notepad.

Press Crtl F and search for MaxActiveReqForOneUser you may get a result such as ‘<Add Key=”MaxActiveReqForOneUser” Value=”20″/>

Change the value to the number of request being made to remove this issue.  Then again if you didn’t correct the above throw some crazy value to it like 256 and call it a day.

What the trade off?

You just use more system resources:  cpu, memory.

More details

Being that these type of request should happen quickly; this may be a sign that something is hogging CPU and SQL time running reports. You should evaluate this rather than just tossing resources at the issue.

Ref:  RSReportServer Configuration File

– Jermal

 

Remove database from read only mode, error 5120

I posted about setting a Microsoft SQL Database into ‘read only’ mode as part of an upgrade. Today I need to go back and make some changes on the now legacy system.

To handle this request I needed to change back to its normal state, this is when I encountered the error 5120.

In the additional information of this error it tells me that an exception occurred while executing a Transact-SQL statement or batch.

Later indicating that it was unable to open the physical file to the database in question due to an “Operating system error 5”

Have fun searching for that *hint* you will not find any of the top 3 links taking you to any Microsoft Site; that’s for sure.  {{annoyed}}

Other errors indicated where insufficient memory or disk space; thanks again {{annoyed}} for sending me  down the wrong path.  I have plenty of memory, and disk space.

The last one was that I did not have permissions to the files. Seriously, I do have permissions. In most cases someone who’s doing this work does in the first place

So I found out what the issue was in my case. Here is a screenshot:

Yup! The files were set to read-only. Once this attribute was removed I was able to change my database status from read-only to read-write.

I hope this post helps you if in similar situation.

– Jermal