Posts Tagged ‘SQL’

SELECT d.name,
last_user_seek = MAX(last_user_seek),
last_user_scan = MAX(last_user_scan),
last_user_lookup = MAX(last_user_lookup),
last_user_update = MAX(last_user_update)
FROM sys.dm_db_index_usage_stats AS i
JOIN sys.databases AS d ON i.database_id=d.database_id
GROUP BY d.name

***Details in the DMV will be cleared and nulled whenever you restart SQL Server.

GD Star Rating
loading...
GD Star Rating
loading...

SQL Agent

Give the user the right role e.g. SQLAgentOperatorRole to msdb ( https://docs.microsoft.com/en-us/sql/ssms/agent/sql-server-agent-fixed-database-roles?view=sql-server-ver15

SSISDB ( Intergration Services Catalog ) 
 
 

GD Star Rating
loading...
GD Star Rating
loading...

SQL Licensing

Software Assurance

 License Mobility refers to the ability to move virtual instances from host to host and between server farms. SQL server needs Software Assurance for License Mobility.

Software Assurance can only be added after the fact if the above 90 day condition is met. Otherwise it must be purchased alongside the license itself. Software Assurance also cannot be added to existing License only volume licensing regardless of when it was purchased. 

SQL Server

Please note that there are 2 ways of licensing SQL Server Standard:

  • Per-Core Model
    • All physical cores must be licensed if run on the physical host or
    • All cores assigned to the virtual machine must be licensed if run in a virtual environment
    • A minimum of 4 cores must be licensed per virtual OSE or per physical processor (depending on how it will be deployed)
    • The core licenses come in packs of 2, so a minimum of 2 are required to be purchased.
    • No SQL Server CAL’s are required to access the server
  • Server / CAL Model
    • One license is needed to run one instance of SQL Server on the host
    • SQL Server User or Device CAL’s are required to connect to the Server either directly or indirectly

 

GD Star Rating
loading...
GD Star Rating
loading...

I got deployment failed when trying to change a SQL servers License from Express to Developer.

I checked the Server and it had developer license installed on SQL

Looking at the JSON

{
    “status”: “Failed”,
    “error”: {
        “code”: “ResourceDeploymentFailure”,
        “message”: “The resource operation completed with terminal provisioning state ‘Failed’.”,
        “details”: [
            {
                “code”: “MismatchSqlVmSku”,
                “message”: “The SQL sku provided is ‘Developer’ which does not match the sku installed in the virtual machine ‘Express’. Make sure to provide the correct sku type.”
            }
        ]
    }
}
 
Its because there was an old version of SQL express installed , removing this fixed the issue
GD Star Rating
loading...
GD Star Rating
loading...

If the SQL databases are in simple recovery

Backup

ALTER DATABASE dbname

SET READ_ONLY;

GO
 

Backup database dbname

to disk = 'C:\...\dbname.bak'

 
Restore


Restore database dbname

from disk =  'C:\...\dbname.bak'

with recovery, replace

 

ALTER DATABASE dbname

SET MULTI_USER;

GO

If the databases are in full recovery model

Backup

Backup database dbname

to disk = 'C:\...\dbname.bak'

Backup log dbname

to disk = 'C:\...\dbname.trn'

with norecovery

 
Restore

 
Restore database dbname

from disk = 'C:\...\dbname.bak'

with norecovery, replace

Restore log test2

from disk = 'C:\...\dbname.trn'

with recovery
 
GD Star Rating
loading...
GD Star Rating
loading...

Here are the steps to change the owner of Workspace/Folder in Worksite 10

–To change the owner of a Workspace/Folder, first we need to find the prj_id of the Workspace/Folder 

select * where prj_name LIKE ‘%FOLDER\WORKSPACE_NAME%’

–To change it to a different owner…

update mhgroup.projects set prj_owner = ‘NEW_USERID’ where prj_id = ‘WORKSPACE_ID’

After changing the owner of the workspace, the author in the docmaster table for this workspace needs to change as well. The owner is still shown as the old one, but after updating the author, the new owner should appear on the workspace profile.

Please run the following additional query:

– update mhgroup.docmaster set author =’new userID’ where docnum = ‘you can get this from the query below ‘

–also, to change the underlying Workspace/Folder document’s owner update mhgroup.docmaster set operator = ‘NEW_USERID’, author = ‘NEW_USERID’ where docnum = ‘DOCNUM’

Please test this against a backup copy of the database. Also make sure you backup the database before running this against production database.

To find the docnum of a workspace: select * from mhgroup.projects where prj_name = ‘Exact Name of Workspace’ and subtype = ‘work’

GD Star Rating
loading...
GD Star Rating
loading...

So you’ve found an issue in a Package in a Project for a DataSynchronization in a Integration Services Catalogs ( .dtsx file ) that you want to edit?

Install if its not already on it and open SQL Server Data Tools

Click on File and New Project

Choose this Project

Choose “Intergration Servies Catalog” and Open the project

You should see the .dstx files on the right under

GD Star Rating
loading...
GD Star Rating
loading...

Login to the Nable Administrator Report Manager ( http://%reportmanagerserver%/Interface/Pages/menu.aspx ) 

You will need to use the username : [email protected]m ( You can reset this password

Navigate to the following

  1. Select System Setup and Logs
  2. Select Data Retention settings

Depending on your retention modify the below per here

Open SQL Server Management Studio make sure you have maintenance plan set up on the ODS Database

  • Set the following in the Maintenance Plan Wizard of Microsoft SQL Server Management Studio:
  •  Set the plan properties Name to Report Manager and Daily Run out of hours 

    2. Select the following maintenance tasks for the ODS databases and the warehouse database
  • Check Database Integrity
  • Reorganize Index
  • Update Statistics

If you backup via another utility you can cancel the below : 

  • Back Up Database (Full)
  • Maintenance Cleanup Task

After the maintenance plan ran , we ran a Shrink on the database to reduce a 400GB Database file back down to 50GB for 3000 Devices

*SolarWinds MSP does not recommend shrinking your database.* however this was needed due to the ODS build up

GD Star Rating
loading...
GD Star Rating
loading...