sql.ps1
|
Set-StrictMode -Version 2 function Test-SqlServer { <# .SYNOPSIS Tests if a SQL Server exists and can be connected to. Optonally checks for a specific database or table. .PARAMETER Server The SQL Server to test .PARAMETER Database The database to test exists on the SQL Server .PARAMETER Table The Table to test exists in the database being connected #> [CmdletBinding()] param( [parameter(Mandatory=$true)] [ValidateNotNullOrEmpty()] [string]$Server, [parameter(Mandatory=$false)] [ValidateNotNullOrEmpty()] [string]$Database, [parameter(Mandatory=$false)] [ValidateNotNullOrEmpty()] [string]$Table ) if ($Database) { $Database = Encode-SqlName $Database } $parts = $Server.Split('\'); $hostName = Encode-SqlName $parts[0]; $instance = if ($parts.Count -eq 1) {'DEFAULT'} else { Encode-SqlName $parts[1] } #Test-Path will only fail after a timeout. Reduce the timeout for the local scope to Set-Variable -Scope Local -Name SqlServerConnectionTimeout 5 $path = "SQLSERVER:\Sql\$hostName\$instance" if (!(Test-Path $path -EA SilentlyContinue)) { throw "Unable to connect to SQL Instance '$Server'" return } elseif ($Database) { $path = Join-Path $path "Databases\$Database" if (!(Test-Path $path -EA SilentlyContinue)) { throw "Database '$Database' does not exist on server '$Server'" return } elseif($Table) { $parts = $Table.Split('.'); if ($parts.Count -eq 1) { $Table = "dbo.$Table" } $path = Join-Path $path "Tables\$Table" if (!(Test-Path $path -EA SilentlyContinue)) { throw "Table '$Table' does not exist in database '$Database' does not exist on server '$Server'" return } } } $true } function New-TelligentDatabase { <# .SYNOPSIS Creates a new database for Telligent Community. .PARAMETER Server The SQL server to install the community to .PARAMETER Database The name of the database to install the community to .PARAMETER Package The installation package to create the community database from .PARAMETER WebDomain The domain the community is being hosted at .PARAMETER AdminPassword The password to create the admin user with. #> [CmdletBinding()] param( [parameter(Mandatory=$true)] [ValidateNotNullOrEmpty()] [ValidateScript({ Test-SqlServer $_ })] [alias('ServerInstance')] [alias('DataSource')] [alias('dbServer')] [string]$Server, [parameter(Mandatory=$true)] [ValidateNotNullOrEmpty()] [alias('dbName')] [alias('InitialCatalog')] [string]$Database, [parameter(Mandatory=$true)] [ValidateNotNullOrEmpty()] [ValidateScript({Test-Zip $_ })] [string]$Package, [parameter(Mandatory=$true)] [ValidateNotNullOrEmpty()] [string]$WebDomain, [parameter(Mandatory=$true)] [ValidateNotNullOrEmpty()] [string]$AdminPassword, [parameter(Mandatory=$true)] [int]$Version ) $connectionInfo = @{ ServerInstance = $Server Database = $database } Write-Progress "Database: $Database" 'Checking if database exists' if(!(Test-SqlServer -Server $Server -Database $Database -EA SilentlyContinue)) { Write-Progress "Database: $Database" 'Creating database' New-Database @connectionInfo } else { Write-Warning "Database $Database already exists." } Write-Progress "Database: $Database" 'Checking if schema exists' if(!(Test-SqlServer -Server $Server -Database $Database -Table 'cs_SchemaVersion' -ErrorAction SilentlyContinue)) { Write-Progress "Database: $database" 'Creating Schema' $tempDir = Join-Path ([System.IO.Path]::GetFullPath($env:TEMP)) ([guid]::NewGuid()) Expand-Zip -Path $package -Destination $tempDir -ZipDirectory SqlScripts $sqlScript = @('Install.sql', 'cs_CreateFullDatabase.sql') | ForEach-Object { Join-Path $tempDir $_ }| Where-Object { Test-Path $_} | Select-Object -First 1 Write-ProgressFromVerbose "Database: $database" 'Creating Schema' { Invoke-Sqlcmd @connectionInfo -InputFile $sqlScript -QueryTimeout 6000 -DisableVariables } Remove-Item $tempDir -Recurse -Force | out-null if($Version -lt 11) { $createCommunityQuery = @" EXECUTE [dbo].[cs_system_CreateCommunity] @SiteUrl = N'http://${WebDomain}/' , @ApplicationName = N'$Name' , @AdminEmail = N'notset@${WebDomain}' , @AdminUserName = N'admin' , @AdminPassword = N'$adminPassword' , @PasswordFormat = 0 , @CreateSamples = 0 "@ } elseif($Version -lt 12) { $createCommunityQuery = @" EXECUTE [dbo].[cs_system_CreateCommunity] @ApplicationName = N'$Name' , @AdminEmail = N'notset@${WebDomain}' , @AdminUserName = N'admin' , @AdminPassword = N'$adminPassword' , @PasswordFormat = 0 , @CreateSamples = 0 "@ } else { $createCommunityQuery = @" EXECUTE [dbo].[cs_system_CreateCommunity] @ApplicationName = N'$Name' , @AdminEmail = N'notset@${WebDomain}' , @AdminUserName = N'admin' , @AdminPassword = N'$adminPassword' , @PasswordFormat = 0 "@ } Write-ProgressFromVerbose "Database: $database" 'Creating Community' { Invoke-Sqlcmd @connectionInfo -query $createCommunityQuery -DisableVariables } } } function Update-TelligentDatabase { <# .SYNOPSIS Updates an existing Telligent Community database to upgrade it to the version in the package .PARAMETER Server The SQL server to install the community to .PARAMETER Database The name of the database to install the community to .PARAMETER Package The installation package to create the community database from #> [CmdletBinding()] param( [parameter(Mandatory=$true)] [ValidateNotNullOrEmpty()] [ValidateScript({ Test-SqlServer $_ })] [alias('ServerInstance')] [alias('DataSource')] [alias('dbServer')] [string]$Server, [parameter(Mandatory=$true)] [ValidateNotNullOrEmpty()] [alias('dbName')] [alias('InitialCatalog')] [string]$Database, [parameter(Mandatory=$true)] [ValidateNotNullOrEmpty()] [ValidateScript({Test-Zip $_ })] [string]$Package ) $connectionInfo = @{ Server = $Server Database = $Database } Write-Progress "Database: $Database" 'Checking if database can be upgraded' if(!(Test-SqlServer @connectionInfo -Table dbo.cs_schemaversion -EA SilentlyContinue)) { throw "Database '$Database' on Server '$Server' is not a valid Telligent Community database to be upgraded" } Write-Progress "Database: $database" 'Creating Schema' $tempDir = Join-Path ([System.IO.Path]::GetFullPath($env:TEMP)) ([guid]::NewGuid()) Expand-Zip -Path $package -Destination $tempDir -ZipDirectory SqlScripts $sqlScript = @('Upgrade.sql', 'cs_UpdateSchemaAndProcedures.sql') | ForEach-Object { Join-Path $tempDir $_ }| Where-Object { Test-Path $_} | Select-Object -First 1 Write-ProgressFromVerbose "Database: $Database" 'Upgrading Schema' { Invoke-Sqlcmd @connectionInfo -InputFile $sqlScript -QueryTimeout 6000 -DisableVariables } Remove-Item $tempDir -Recurse -Force | out-null } function Grant-TelligentDatabaseAccess { <# .SYNOPSIS Grants a user access to a Telligent Community database. If the user or login doesn't exist, in SQL server, they are created before being granted access to the database. .PARAMETER CommunityPath The path to the Telligent Community you're granting database access for .PARAMETER Username The name of the user to grant access to. If no password is specified, the user is assumed to be a Windows login. .PARAMETER Password The password for the SQL user .EXAMPLE Grant-TelligentDatabaseAccess (local)\SqlExpress SampleCommunity 'NT AUTHORITY\NETWORK SERVICE' Description ----------- This command grant access to the SampleCommunity database on the SqlExpress instance of the local SQL server for the Network Service Windows account .EXAMPLE Grant-TelligentDatabaseAccess ServerName SampleCommunity CommunityUser -password SqlPa$$w0rd Description ----------- This command grant access to the SampleCommunity database on the default instance of the ServerName SQL server for the CommunityUser SQL account. If this login does not exist, it gets created using the password SqlPa$$w0rd #> [CmdletBinding()] param( [Parameter(Mandatory=$true)] [ValidateNotNullOrEmpty()] [ValidateScript({ Test-TelligentPath $_ })] [string]$CommunityPath, [parameter(Mandatory=$true, Position = 2)] [ValidateNotNullOrEmpty()] [string]$Username, [string]$Password ) $info = Get-TelligentCommunity $CommunityPath #TODO: Sanatise inputs Write-Verbose "Granting database access to $Username" if ($Password) { $CreateLogin = "CREATE LOGIN [$Username] WITH PASSWORD=N'$Password', DEFAULT_DATABASE=[$($info.DatabaseName)];" } else { $CreateLogin = "CREATE LOGIN [$Username] FROM WINDOWS WITH DEFAULT_DATABASE=[$($info.DatabaseName)];" } $query = @" IF NOT EXISTS (SELECT 1 FROM master.sys.server_principals WHERE name = N'$Username') BEGIN $CreateLogin END; IF NOT EXISTS (SELECT 1 FROM sys.database_principals WHERE name = N'$Username') BEGIN CREATE USER [$Username] FOR LOGIN [$Username]; END; EXEC sp_addrolemember N'aspnet_Membership_FullAccess', N'$Username'; EXEC sp_addrolemember N'aspnet_Profile_FullAccess', N'$Username'; EXEC sp_addrolemember N'db_datareader', N'$Username'; EXEC sp_addrolemember N'db_datawriter', N'$Username'; EXEC sp_addrolemember N'db_ddladmin', N'$Username'; "@ Invoke-TelligentSqlCmd -WebsitePath $CommunityPath -Query $query } function Invoke-TelligentSqlCmd { <# .SYNOPSIS Executes a SQL Script agains the specified community's database. .PARAMETER WebsitePath The path of the Telligent Community website. If not specified, defaults to the current directory. .PARAMETER Query A bespoke query to run agains the community's database. .PARAMETER File A script in an external file run agains the community's database. .PARAMETER QueryTimeout The maximum length of time the query can run for #> param ( [Parameter(Mandatory=$true, Position=0)] [ValidateScript({ Test-TelligentPath $_ -Web })] [string]$WebsitePath, [Parameter(ParameterSetName='Query', Mandatory=$true)] [string]$Query, [Parameter(ParameterSetName='File', Mandatory=$true)] [ValidateScript({Test-Path $_ -PathType Leaf})] [string]$File, [int]$QueryTimeout ) $info = Get-TelligentCommunity $WebsitePath $sqlParams = @{ ServerInstance = $info.DatabaseServer Database = $info.DatabaseName } if ($Query) { $sqlParams.Query = $Query } else { $sqlParams.InputFile = $File } if ($QueryTimeout) { $sqlParams.QueryTimeout = $QueryTimeout } Invoke-Sqlcmd @sqlParams -DisableVariables } function New-Database { <# .SYNOPSIS Creates a new SQL Database .PARAMETER Database The name of the database to create .PARAMETER Server The server to create the database on #> [CmdletBinding()] param( [parameter(Mandatory=$true, Position=0, ValueFromPipeline=$true)] [ValidateNotNullOrEmpty()] [alias('Database')] [string]$Name, [ValidateNotNullOrEmpty()] [alias('ServerInstance')] [string]$Server = "." ) $query = "Create Database [$Name]"; Invoke-Sqlcmd -ServerInstance $Server -Query $query } function Remove-Database { <# .SYNOPSIS Drops a SQL Database .PARAMETER Database The name of the database to drop .PARAMETER Server The server to drop the database from #> [CmdletBinding()] param( [parameter(Mandatory=$true, Position=0, ValueFromPipeline=$true)] [ValidateNotNullOrEmpty()] [alias('dbName')] [string]$Database, [ValidateNotNullOrEmpty()] [alias('dbServer')] [string]$Server = "." ) $query = @" if DB_ID('$Database') is not null begin exec msdb.dbo.sp_delete_database_backuphistory @database_name = N'$Database' alter database [$Database] set single_user with rollback immediate drop database [$Database] end "@ Invoke-Sqlcmd -ServerInstance $Server -Query $query } |