-
Post Installation



## This is a PS script to Perform the Post-Installation steps on 2012\14
## Developer


##
##


$ErrorActionPreference = "SilentlyContinue"
#$M = read-host -prompt "Please enter the machine name: "
$I = read-host -prompt "Please enter the full instance name eg., clwtd4000\SQL2008INS3: "
$yes = New-Object System.Management.Automation.Host.ChoiceDescription "&yes",""
$no = New-Object System.Management.Automation.Host.ChoiceDescription "&no",""
$choices = [System.Management.Automation.Host.ChoiceDescription[]]($yes,$no)

$caption = "Scom install"
$message = "is this server prod?"
$result = $Host.UI.PromptForChoice($caption,$message,$choices,0)

[System.Reflection.Assembly]::LoadWithPartialName(‘Microsoft.SqlServer.SMO’) | out-null

$login = New-Object -TypeName Microsoft.SqlServer.Management.Smo.Login  -ArgumentList $I, 'ad-ent\prv_edm_fa_srv_sqlserviceaccounts'
$login.LoginType = 'WindowsLogin'
$login.AddToRole('sysadmin')
$login.Alter()

$login = New-Object -TypeName Microsoft.SqlServer.Management.Smo.Login  -ArgumentList $I, 'AD-ENT\PRV_CAO_AS_SRV_SQLSECURITYADMINS'
$login.LoginType = 'WindowsLogin'
$login.AddToRole('sysadmin')
$login.Alter()

$login = New-Object -TypeName Microsoft.SqlServer.Management.Smo.Login  -ArgumentList $I, 'AD-ENT\SCAO_TIM_SQL'
$login.LoginType = 'WindowsLogin'
$login.AddToRole('sysadmin')
$login.Alter()

#givining the sql instance details to configure min and max values

$cn = new-object System.Data.SqlClient.SqlConnection "server=$I;database=master;Integrated Security=sspi"
$cn.Open()
$sql = $cn.CreateCommand()
$I
$sql.CommandText = "EXEC msdb.dbo.sp_set_sqlagent_properties @jobhistory_max_rows=999999,@jobhistory_max_rows_per_job=99999;"

$rdr = $sql.ExecuteNonQuery();
$cn.close()

$srv = new-object -TypeName Microsoft.SqlServer.Management.Smo.Server -ArgumentList $I
$database =new-object Microsoft.SqlServer.Management.Smo.database
$database=$srv.Databases.Item("master")
foreach ($db in $database)
{
    $svr
$db.name
    $fg = $db.FileGroups
foreach ($group in $fg)
    {
$group.Name
foreach ($file in $group.Files)
        {
$file.filename
            $file.size=102400
            $file.Growth = 102400
       $file.GrowthType = 'KB'
$file.MaxSize = -1  # Set to unlimited growth or replace with some value in KB
$file.Alter()
            #write-host "changed value for master database"
            $file.size
        }
     }
     foreach ($log in $db.LogFiles)
     {
$log.filename
        $log.size=102400
$log.Growth = 102400
$log.GrowthType = 'kb'
        $log.MaxSize = -1
        $log.Alter()
        $log.size
     
}
}


$database=$srv.Databases.Item("msdb")
foreach ($db in $database)
 {
$db.name
    $fg = $db.FileGroups
foreach ($group in $fg)
    {
$group.Name
foreach ($file in $group.Files)
        {
$file.filename
            $file.size=102400
            $file.Growth = 102400
       $file.GrowthType = 'KB'
$file.MaxSize = -1  # Set to unlimited growth or replace with some value in KB
            $file.Alter()
            #write-host "changed value for msdb database"
            $file.size
         }
     }
     foreach ($log in $db.LogFiles)
     {
$log.filename
        $log.size=102400
$log.Growth = 102400
$log.GrowthType = 'kb'
        $log.MaxSize = -1
        $log.Alter()
        $log.size
     
}
}


$database=$srv.Databases.Item("model")
foreach ($db in $database)
 {
$db.name
    $fg = $db.FileGroups
foreach ($group in $fg)
    {
$group.Name
foreach ($file in $group.Files)
        {
$file.filename
            $file.size=102400
            $file.Growth = 102400
       $file.GrowthType = 'KB'
$file.MaxSize = -1  # Set to unlimited growth or replace with some value in KB
            $file.Alter()
            $file.size
         }
     }
     foreach ($log in $db.LogFiles)
     {
$log.filename
        $log.size=102400
$log.Growth = 102400
$log.GrowthType = 'kb'
        $log.MaxSize = -1
        $log.Alter()
        $log.size
     
}
}


     
     
#changing backup compression default;creating polkj11 with sysadmin;sa disable;revokeconnect from guest in modeldb
$sqlConnection = New-Object System.Data.SqlClient.SqlConnection
$sqlConnection.ConnectionString = "Server = $I; Integrated Security = True; Initial Catalog='master'"
$sqlConnection.Open()
$SqlCmd = New-Object System.Data.SqlClient.SqlCommand
$SqlCmd.CommandText = "sp_configure 'backup compression default','1';reconfigure with override"
$SqlCmd.Connection = $SqlConnection
$postinstallation = $SqlCmd.ExecuteScalar()
$SqlConnection.Close()


write-host "done with Enable backup compression and changing the file sizes of master,model and msdb"



Comments

Post a Comment

Popular posts from this blog

Netezza Commands

-
Table Size  select tablename, used_bytes/pow(1024,4) as used_TB   from _v_table_storage_stat  where tablename  in (  select tablename from _v_table where tablename not like  '_VT_%' and tablename not like '_T_%'); Objects owned by a user: select o.objname, d.database, oc.classname, u.usename from _t_object o, _t_user u, _t_object_classes oc, _v_database d where u.usename='username' and u.usesysid=o.objowner and o.objclass=oc.objclass and o.objdb=d.objid; Tip: Use NZ_HELP to get   syntax of all commands Like -    nz_help | grep grant [nz@dfw86inzb-04 ~]$ nz_help | grep grant nz_db_group_access_listing           To show what groups have been granted access to what databases. nz_db_user_access_listing            To show what users have been granted access to what databases. nz_ddl_grant_group    ...
Read more

MS SQL Server Tips

-
Image
Unable to Shrink the DataFile If you are unable to shrink the datafile then try the following Error :  File ID 3 of database ID 8 cannot be shrunk as it is either being shrunk by another process or is empty. DBCC execution completed. If DBCC printed error messages, contact your system administrator. Solution:   Add at least 2-3 MB to the data file and start shrinking again. Index Fragmentation Level SELECT distinct dbschemas.[name] as 'Schema', dbtables.[name] as 'Table',  dbindexes.[name] as 'Index', indexstats.avg_fragmentation_in_percent, indexstats.page_count, i.rowcnt as 'totalrows' FROM sys.dm_db_index_physical_stats (DB_ID(), NULL, NULL, NULL, NULL) AS indexstats INNER JOIN sys.tables dbtables on dbtables.[object_id] = indexstats.[object_id] INNER JOIN sys.schemas dbschemas on dbtables.[schema_id] = dbschemas.[schema_id] INNER JOIN sys.indexes AS dbindexes ON dbindexes.[object_id] = indexstats....
Read more
-
Maintenacne Plan creation USE [msdb] GO DECLARE @sqlInstance varchar(20) SET @sqlInstance = '$(instanceName)' DECLARE @BackUpSubPlan sysname SET @BackUpSubPlan = 'Back Up Database Task' DECLARE @BackUpJobName sysname SET @BackUpJobName = 'System databases.' + @BackUpSubPlan DECLARE @ChkDbSubPlan sysname SET @ChkDbSubPlan = 'Check Database Integrity Task' DECLARE @ChkDbJobName sysname SET @ChkDbJobName = 'System databases.' + @ChkDbSubPlan DECLARE @RebuildIndexSubPlan sysname SET @RebuildIndexSubPlan = 'Rebuild Index Task' DECLARE @RebuildIndexJobName sysname SET @RebuildIndexJobName = 'System databases.' + @RebuildIndexSubPlan DECLARE @UpdStatsSubPlan sysname SET @UpdStatsSubPlan = 'Update Statistics Task' DECLARE @UpdStatsJobName sysname SET @UpdStatsJobName = 'System databases.' + @UpdStatsSubPlan DECLARE @HistClnSubPlan sysname SET @HistClnSubPlan = 'History Cleanup Task' ...
Read more