On-Premises Gateway

We are living in a digital transformation to O365, but by the time we are doing this, our customers still need to connect their On-Premises data like SharePoint or SQL to Office 365 applications (PowerApps, Microsoft Flow, Logic Apps, Power BI).

For that reason, Microsoft has introduced on-premises data gateway for providing a quick and secure connection between on premise data and Office 365 apps.

We must keep in mind that there are two different types of gateways, one of them is On-Premises and the other gateway, is the personal gateway, which only allows to connect to PowerBI and can be used as an individual without any administrator configuration.

In the following image, we can see the differences between the personal gateway and the On-Premises gateway:

Moreover, the current supported On-Premises applications are SharePoint 2016 and SQL 2016, as you can see in the diagram below, there are multiple O365 connections to On-Premises services:

The installation process is straightforward, and only is needed a server with the following requirement to use the gateway:

Hardware Requirements:

Hardware Requirements	Value
CPU	8 Cores
RAM	8 GB

Software Requirements:

Software Requirements	Value
.NET Framework	Version – 4.5
Operating System	Windows 7 / Windows Server 2008 R2, or later

If you require more info about the gateway, you can follow the links:

https://flow.microsoft.com/es-es/blog/on-premises-data-gateway/

https://powerbi.microsoft.com/es-es/documentation/powerbi-gateway-onprem/

https://docs.microsoft.com/es-es/azure/analysis-services/analysis-services-gateway

Advertisement

SQL how to change SA password

Did you lost your SA password? Do yo need to do some actions in your SQL server?

If the answer is yes, this post is for you. Following this procedure, will be very easy to change the SA password from the SQL. This steps works for SQL 2005, 2008, 2008R2, 2012 and 2012R2. The only requirement, is that you’ll need to be an OS administrator.

First of all, open a CMD window as administrator

Execute osql -L which will show all the SQL servers in the domain

Choose the SQL Server that you want to change the password, and execute the following command: osql -S ServerName -E

sp_password NULL, NewSAPassWord, ‘sa’
go

And you’re ready to go!!

SharePoint 2013The EXECUTE permission was denied on the object

Hi,

Today I bring a problem that was pretty easy to get solved, in this case I installed a new feature from a colleague in a SharePoint farm, but when we tried to access to the feature to configure it, we were receiving the following error message:

Getting Error Message for Exception System.Web.HttpUnhandledException (0x80004005): Exception of type ‘System.Web.HttpUnhandledException’ was thrown. —> System.Data.SqlClient.SqlException (0x80131904): The EXECUTE permission was denied on the object ‘proc_GetTimerJobLastRunTime’, database ‘SharePoint_Config’, schema ‘dbo’.

So, to solve it, we had to execute the following SQL query:

use [SharePoint_Config]

GO

GRANT EXECUTE ON [dbo].[proc_putObjectTVP] TO [WSS_Content_Application_Pools]

GRANT EXECUTE ON [dbo].[proc_putObject] TO [WSS_Content_Application_Pools]

GRANT EXECUTE ON [dbo].[proc_putDependency] TO [WSS_Content_Application_Pools]

GRANT EXECUTE ON [dbo].[proc_GetTimerJobLastRunTime] TO [WSS_Content_Application_Pools]

GO

That’s all, hope it helps!

How to move SQL System Databases

One day or another comes the day to move the location or System Databases in your work as a DBA. Could because you need to fix the initial installation or because you are doing a storage layout reorganization.

System Databases are four:

• Master
• Model
• MSDB
• Temp DB

Since, this is a delicated operation we should move them one by one, and I strongly recommend the following order: TempDB, MSDB, Model and last Master.

In the following steps E:/DATA is the destination path, you will have to change it to your new system databases location.

1. Move Temp DB database:

USE master;

GO

ALTER DATABASE tempdb

MODIFY FILE (NAME = tempdev, FILENAME = ‘E:DATAtempdb.mdf’);

GO

ALTER DATABASE tempdb

MODIFY FILE (NAME = templog, FILENAME = ‘E:DATAtempLog.ldf’);

GO

Stop the instance, move the mdf and ldf files to the new location and start the instance.

1. Move MSDB Database

USE master;

GO

ALTER DATABASE msdb

MODIFY FILE (NAME = MSDBData,FILENAME = ‘E:DATAMSDBData.mdf’);

GO

ALTER DATABASE msdb

MODIFY FILE (NAME = MSDBLog, FILENAME = ‘E:DATAMSDBLog.ldf’);

GO

Stop the instance and SQL Server Agent, move the mdf and ldf files and start the instance. Since MSDB is related to SQL server agent jobs, try to view the history jobs or run a job to verify the MSDB is working properly.

1. Move Model Database

USE master;

GO

ALTER DATABASE model

MODIFY FILE (NAME = modeldev,FILENAME = ‘E:DATAmodel.mdf’);

GO

ALTER DATABASE model

MODIFY FILE (NAME = Modellog,FILENAME = ‘E:DATAmodelLog.ldf’);

GO

Stop the instance and SQL Server Agent, move the mdf and ldf files and start the instance. Since the model database is used for creating new databases a good test is to create a new one.

1. Move MASTER Database

Open SQL Server Configuration Manager, locate the SQL Server Service, double-click and edit the Advanced Properties tab:

Change the startup parameters to the new location.

• “-d” flag refers to mdf data destination
• “-l” flag refers to ldf log destination
• “-e” flag refers to errorlog folder destination

PD: After moving tempDB, MSDB and/or Model I came across this error when trying to modify a job step:

After restarting the server, it works normally. I need to confirm that this is normal

SQL Best Practices for SharePoint Farm

It is important to set Latin1_General_CI_AS_KS_WS collation for both SharePoint 2010 and SharePoint 2013 databases – Case insensitive, Accent sensitive, Kana sensitive, Width sensitive.

SharePoint uses the Latin1_General_CI_AS_KS_WS collation because the Latin1_General_CI_AS_KS_WS collation most closely matches the Microsoft Windows NTFS file system collation – http://support.microsoft.com/kb/2008668

You can validate Server Collation from General tab of SQL Server instance properties using SQL Server Management Studio

2) You must set max degree of parallelism (MAXDOP) to 1 for instances of SQL Server that host SharePoint databases to make sure that a single SQL Server process serves each request.

Important:

Setting the max degree of parallelism to any other number can cause a less optimal query plan to be used that will decrease SharePoint Server 2013 performance.

Also is it possible to configure the MaxDOP by a SQL query:

USE AdventureWorks2012 ;
GO 
EXEC sp_configure 'show advanced options', 1;
GO
RECONFIGURE WITH OVERRIDE;
GO
EXEC sp_configure 'max degree of parallelism', 8;
GO
RECONFIGURE WITH OVERRIDE;
GO

Reference: http://technet.microsoft.com/en-us/library/hh292622(v=office.15).aspx

3) You must configure the user accounts before install and build the SharePoint farm:

SP_Admin – to install SharePoint

SP_Farm – for Farm Administrator

Both need to have dbcreator and securityadmin rights on SQL Server instance.

Reference: http://technet.microsoft.com/en-us/library/cc263445.aspx

Other best practices to SQL is to configure an Alias in SharePoint farm, this would help in case you need to change your SQL server, and your SQL Databases would be always up to date listed in SharePoint. But you have to remember that you configured an Alias by the cliconfg command. (see post https://sharepointrescue.wordpress.com/2015/10/20/create-a-sql-alias/ to know how to do it)

In addition to that, is it very recommended to change the initial size of the files of the mdf files, as well as the increments for the databases. If you leave the default sizes, if the database grows a lot, it could generate defragmention.

I’m sure that would be more recommendations, so I’ll try to keep updated this post 🙂

Create a SQL Alias

Moving / Migrating Microsoft SharePoint Server Databases from one server to another might be difficult as you need to reconfigure all the related DB(s) to point at the new server. For this reason having SharePoint connect to a SQL Alias instead of the NetBIOS name is always a good idea.

The main benefit is, if you ever have to switch the SQL Server or connect to a SQL Cluster VIP address, you just change the Alias to point to the SQL Cluster name on the SharePoint Server and restart the SharePoint Timer Service and you are good to go. This can save huge amount of time and headache.

Follow the steps below to create a SQL Alias on all your SharePoint Servers:

Go to Start >> Run >> Type CliConfg.exe and the below screen will appear:

Select the Alias Tab and click Add:

Select TCP/IP from the Network Libraries and Enter the following Values:

Server Alias: This is the Old SQL Server Network Name

Server Name: This is the New SQL Server Name Network Name

• In Case of SQL Server FCI then this could be the cluster name
• In Case of SQL Server AlwaysOn AG this could be the SQL Listener Name

Port Number (if needed)

Last step is validate SQL Alias

• Create a new text file on SharePoint Server and name it “TestDBConnection.udl”
• Double click to open the file and enter your SQL Server Alias name
• Use Windows Integrated Security
• You should be able to see all your SharePoint databases when you click on “Select the database on the Server”

And that’s its, you will see the new SQL Server Alias added as shown and you are ready to start using the new Database.

Hope it helps!

Migrate BBDD from SQL to another SQL

Hi!

Today I’ll post about a script for SQL, this script helps me to migrate all the BBDD from one SQL to another SQL server.

SET NOCOUNT ON;

Declare
–Rutas en el servidor de origen
@path_data_Files_source as nvarchar(500) = ‘C:Program FilesMicrosoft SQL ServerMSSQL11.MSSQLSERVERMSSQLDATA’,
@path_log_files_source as nvarchar(500) = ‘C:Program FilesMicrosoft SQL ServerMSSQL11.MSSQLSERVERMSSQLDATA’,

–Rutas en el servidor de destino
@path_data_Files_destination as nvarchar(500) = ‘U:SQL ServerMSSQL12.MSSQLSERVERMSSQLData’,
@path_log_files_destination as nvarchar(500) = ‘V:SQL ServerMSSQL12.MSSQLSERVERMSSQLData’

Select
‘EXEC sp_attach_db @dbname = N”[‘ + data_Files.database_name + ‘]”, @filename1 = N”’ + data_Files.file_path + ”’, @filename2 = N”’ + log_Files.file_path + ””
–data_Files.database_name, data_Files.file_path, log_files.file_path
From
(
select d.database_id, REPLACE(f.physical_name, @path_data_Files_source, @path_data_Files_destination) as file_path, d.name as database_name, physical_name
from sys.databases as d
inner join sys.master_files as f on ( d.database_id = f.database_id)
WHERE not d.name in ( ‘MSDB’,’MASTER’, ‘MODEL’,’TEMPDB’, ‘Distribution’)
and f.type = 0 –ROWS
) AS data_Files
Inner Join (
select d.database_id, REPLACE(f.physical_name, @path_log_files_source, @path_log_files_destination) as file_path, d.name as database_name, physical_name
from sys.databases as d
inner join sys.master_files as f on ( d.database_id = f.database_id)
WHERE not d.name in ( ‘MSDB’,’MASTER’, ‘MODEL’,’TEMPDB’,’Distribution’)
and f.type = 1 –LOG
) as log_files on (data_Files.database_id = log_files.database_id)
ORDER BY data_Files.database_name

Hope it helps someone