Sql-server – Should dbatools be installed on all SQL Servers

dbatoolssql server

I'm looking to leverage dbatools powershell module for our SQL Server management needs. The install process does not lend itself for installation across a large number of servers. Is it really necessary or even a best practice to install dbatools across all SQL Servers in the environment or leverage a server we use for our database management as a central place to install to and manage from there? Seems like a lot of the commands I've looked at can be used remotely. Thank you.

Best Answer

In general you should be able to use most of the dbatools functionality from your laptop (or whatever host you use to connect to your server via SSMS). If you're the kind of admin who prefers to RDP onto your servers and use SSMS locally, then maybe try to break that bad habit; but in general you should be able to use dbatools alongside the rest of your administrative toolset.

If you've never used powershell at all in your environment before, then you may need to coordinate with your sysadmins or networking team to debug some PS Remoting growing pains or open up ports here and there. As well, if you want to use dbatools functions as a part of SQL Server Agent Jobs, you will need to install (and upgrade!) dbatools locally (or in a way that the module can be loaded by the agent). I recommend this guidance

And as always - for any specific issues, the SQL Community dbatools slack channel is the bee's knees.

Related Solutions

Sql-server – SMO v11 (SQL Server 2012) not scripting key definitions, indexes, and constraints

I'm unable to reproduce the issue you describe. Here's simplified script I used to test from 2012 and 2008 R2 to a 2008 R2 server. Comment/Uncomment the add-type section as needed. One thought is that using deprecated LoadWithPartialName could be causing issues if you have both 2008 and 2012 assemblies on same machine.

#SQL 2008 R2
#add-type -AssemblyName "Microsoft.SqlServer.ConnectionInfo, Version=10.0.0.0, Culture=neutral, PublicKeyToken=89845dcd8080cc91"
#add-type -AssemblyName "Microsoft.SqlServer.Smo, Version=10.0.0.0, Culture=neutral, PublicKeyToken=89845dcd8080cc91"
#add-type -AssemblyName "Microsoft.SqlServer.SMOExtended, Version=10.0.0.0, Culture=neutral, PublicKeyToken=89845dcd8080cc91"

#SQL 2012
add-type -AssemblyName "Microsoft.SqlServer.ConnectionInfo, Version=11.0.0.0, Culture=neutral, PublicKeyToken=89845dcd8080cc91"
add-type -AssemblyName "Microsoft.SqlServer.Smo, Version=11.0.0.0, Culture=neutral, PublicKeyToken=89845dcd8080cc91"
add-type -AssemblyName "Microsoft.SqlServer.SMOExtended, Version=11.0.0.0, Culture=neutral, PublicKeyToken=89845dcd8080cc91"

$InstanceName = "myservername"

$query = @"
CREATE TABLE dbo.authors(
    au_id int NOT NULL,
    au_lname varchar(40) NOT NULL,
    au_fname varchar(20) NOT NULL

 CONSTRAINT UPKCL_auidind PRIMARY KEY CLUSTERED 
(
    au_id ASC
)
) 
"@

$InstanceObject = New-Object "Microsoft.SqlServer.Management.SMO.Server" "$InstanceName"

$db = $InstanceObject.Databases['tempdb']
$db.ExecuteNonQuery($query)

$MyScripter = New-Object "Microsoft.SqlServer.Management.SMO.Scripter"

$MyScripter.Server = $InstanceObject

$MyScripter.Options.DriAll = $true
#$MyScripter.Options.DriPrimaryKey = $false
#$MyScripter.Options.DriClustered = $false

$MyScripter.Script($InstanceObject.Databases['tempdb'].Tables['authors'])

Sql-server – dbatools start-dbamigration running very slowly

You can look at the code for each Copy command used in the Start-DbaMigration command and try running them each individually (for the objects you want to migrate).

Overall the start command can take some time to run as it does a good bit of extra stuff in the command. Using each Copy command individually is the common method when you are not performing a full migration (including databases).

If you find any particular bugs or slowness you can get further assistance with issues by submitting an issue on our repository here.

Best Answer

Related Solutions

Sql-server – SMO v11 (SQL Server 2012) not scripting key definitions, indexes, and constraints

Sql-server – dbatools start-dbamigration running very slowly

Related Question