Home » sql

Tag: sql

Script a custom AWS AppStream image

Scenario

Lets script a custom aws appstream image

A customer wanted to setup AWS AppStream 2.0 Image Automation for their AppStream image creation especially as there were situations where two or more images were potentially required including monthly updates (minimum) so they needed a repeatable, consistent solution for this.

 

Solution

You can only automate this so far within the current AppStream 2.0 limitations.

Creation of the image builders, the builder image itself, creation or the fleets and stacks then based on this image.

Short of joining your image builder to a domain that launches a script at computer startup – there is no immediate way to call a ‘zero touch build’ for AppStream images, and no current way to automate the Image Builder test and optimize wizard (the wizard you run to seal and snapshot the image)

Things to Consider Scripting / Adding

  • IEES Disable for all users
  • Local Timezone and Regional Settings (particularly if outside the US and your regional settings are not available for selection from the End User interface) (for example UK English and Timezone)
Set-WinSystemLocale en-gb
Set-Culture en-GB
Set-WinSystemLocale en-GB
Set-Timezone "GMT Standard Time"
  • If your images wont be domain joined then
    • Create a login script to apply user settings at ‘login’
    • If you manipulate local Group Policy (gpedit.msc) use the microsoft tool  LGPO.exe to backup and restore the settings easily
    • You can publish Windows Explorer in the Image Assistanc via a batchfile with content
cd %userprofile%\my files\temporary files -Force
start .

Examples

You can automate the image builder application injection using sqlite.exe per below

example.sql file to pass into C:\ProgramData\Amazon\Photon\PhotonAppCatalog.sqlite

INSERT INTO Applications (Name, AbsolutePath, DisplayName, IconFilePath, LaunchParameters) VALUES (“My Intranet Website”, “C:\Program Files (x86)\internet explorer\iexplorer.exe”, “Intranet”, “C:\ProgramData\Amazon\Photon\AppCatalogHelper\AppIcons\ie.png”, “https://www.myintranet.org.uk”)

Any questions or comments get in touch using the social media links at the top of the website and we will do out best to help! 😉

 

SQL Server starts but SQL Service Agent does not with 'Access Denied'

We had an instance where the SQL Server would start but the SQL Server Agent using the same account was unable to start with an Access Denied error.

STEPS
1) check that the service account is not locked out in AD
2) check that the ‘log on as a service’ rights are still valid and assigned to the service account (it hasnt been lost or revoked) with gpedit.msc
3) try trunning the command of the service directly in the command prompt (so you can see the error message reported back)
General tab of service > Path to executable – copy and paste the entire path into a cmd window.

We received the error
‘SQL Server Agent and StartServiceCtrlDispatcher (error 6)’

This lead to the service not having rights on the logs directory for the SQLAGENT.OUT file.

Check the registry for the for the log location related to the SQLINSTANCE – listed as
HKLMsoftwaremicrosoftmicrosoft sql servermssql.xxsqlserveragentErrorLogFile

Check the permissions on this listed directory, or one of its parents, and that the ‘service’ account has the rights to modify, read write, edit etc etc..

We browsed to a parent directory and just gave full control, then replace existing inheritable permissions to flow down. (i.e x:datamssqlinstance)

(as this service account was used for everything but was just not a local admin of the SQL box)

Service started successfully.

Edgesight 5.4 report subscription error

Situation: When creating a new subscription report in edgesight you may get

Error Message:

edgesight_ssrs_reports_subscriptionerror

An error occurred creating the   subscription: An attempt has been made to use a delivery extension that is   not registered for this report server. —> Microsoft.ReportingServices.Diagnostics.Utilities.DeliveryExtensionNotFoundException:   An attempt has been made to use a delivery extension that is not registered   for this report server.

Solution:

Open the SQL Server Reporting services configuration manager for the edgesight database instance and edit the configure the ‘Email settings’ section.

In our instance the below settings below were blank

 

Appsense Events not deleting

Problem: Appsense database is growing over time. The EVENTS table is huge.

Solution: (at the time of writing this article) Appsense do not have an event management / retention period. It simply records all events indefinitely. Run the following script on the database server.

—–Begin SQL script, select and copy into a text file and rename it to .sql extension; run on SQL server that holds AppSense database
— Set the following to the database that this script operates on USE [DatabaseName]
— This represents the number of days to preserve events for
— i.e. ‘7’ will delete events older than one week DECLARE @ageOfEvents INT SET @ageOfEvents = 7
— Delete the events
DECLARE @eventCount INT
DECLARE @oldestEventTime DATETIME
SET @oldestEventTime = DATEADD (DD, -@ageOfEvents, GETUTCDATE()) PRINT N’Deleting events older than ‘ + CONVERT(NVARCHAR(32),
@oldestEventTime)
EXEC Event_DeleteFromTime NULL, @oldestEventTime, 0, @eventCount OUTPUT PRINT CONVERT(NVARCHAR(12), @eventCount) + N’ events deleted’
—–End SQL script