TFS Agent Setup

Troubleshooting

PowerShell ISE .\config.cmd gets stuck at Connect:

Documentation suggests that you can run .\config.cmd from elevated PowerShell ISE.
This appears to get stuck at Connect:
https://github.com/Microsoft/azure-pipelines-agent/issues/1266

Use Elevated VS Command prompt instead works.

 

Build: No agent found in pool Default which satisfies the specified demands:

msbuild
visualstudio
vstest
Agent.Verion -gtVersion 2.115.0

Solution: Install Visual Studio.
Then I used a VS command prompt elevated as administrator to reconfigure the agent by using:
.\config.cmd remove
.\config.cmd

 

Build Failed: Failed to retrieve information about ‘NETStandard.Library’ from remote source ‘\\<old-server>’

Resolution was not to install .Net Core SDK
It was that the domain had changed and NuGet could no longer see the old packages UNC
Short-term resolution:
Include the packages in source control explorer
Remove the unavailable location from nuget.config

Long-term resolution:
Would be to move the packages to a new accessible location

 

 

Advertisements

Power BI Notes

General

 

 

 

Power BI – Templates – Also transferring PBI without data

Thanks to SQL Bristol Meetup Tue-20-Nov

 

Power BI Design Strategies

  1. Templates as good part of design strategy

 

Transfer Power BI without data

Was looking for a method to transfer a Power BI model between remote computers.

From the above talk, saving a Power BI as a template, strips all the data away and the file becomes much smaller.

The template may then be transferred to another machine.
Also, have put the template in Git source control – as it no longer contains data.

When the template is opened, it creates a new document and immediately reconnects to data sources and loads the data. The file may then be saved locally

 

 

SQL Azure cross database queries – evaluation

Existing documentation to create external database seems fine. Links include:

https://docs.microsoft.com/en-us/azure/sql-database/sql-database-elastic-query-overview

https://ppolyzos.com/2016/07/30/cross-database-queries-in-azure-sql-databases/

Troubleshooting

Error: “The option ‘IDENTITY’ is not supported with external tables for sharded data”
Remove the IDENTITY from the CREATE EXTERNAL TABLE id column

 

Using a different name in target

If the target is already using the external name you may want to use a different name. See this article. Add following to the with statement.

SCHEMA_NAME = ‘Sales’, OBJECT_NAME = ‘Quote’

https://azure.microsoft.com/en-us/blog/querying-remote-databases-in-azure-sql-db/

One option is to create a view in the source with the desired name. Then in the target reference that. Works.

Performance

Slightly slower than using the source database, but pretty good – certainly usable in most cases.

Run SQL Server with Docker

 

Why

    1. Developer machines
      On my developer machines I have installed various version of SQL Server. These run services in the background and are difficult to stop and uninstall.Containers offer the opportunity to install then destroy and start again.
    2. Development and Testing
      Store images/containers and then recreate
    3. Future: create images which derive from base images and store and share in a repository possibly in Azure Container Registry

Get Started:

Install docker for windows

https://docs.docker.com/docker-for-windows/install/

Turn on virtualisation
Hyper-V
Docker

Quickstart: Run SQL Server container images with Docker

https://docs.microsoft.com/en-us/sql/linux/quickstart-install-connect-docker?view=sql-server-2017

Note on using PowerShell

Some docker commands do not work inside the ISE.
For those, or all commands use PowerShell console window

Connect from outside the container

From SqlCmd or SSMS

Use the IP address of the machine that hosts your container (probably your machine)
e.g. 10.0.0.21,1433

The function inspect gives information about the container, but not the host IP:
docker inspect

docker inspect –format ‘{{range .NetworkSettings.Networks}}{{.IPAddress}}{{end}}’ <container id>

 

 

Restore a database into the image

Now, we have SQL running in docker, it would be useful to restore a database into that.

  1. Outside Docker
    Backup database
    Tip: SSMS backup in UI, but then “Script action to new query window” and store it
  2. Copy file into the running image
    Use Bash
    docker exec -it sql1 “bash”Then Linux commands to make directory
    Exit Bash
    docker cp MyDatabase.bak sql1:\var\opt\mssql\backup
  3. Inside Docker
    Restore database – use script – may need to Move files

 

Bake files into an image for reuse

docker commit sql1 sqltestco:sql2017
docker images

Clean up with:
docker rmi sqltestco:sql2017

 

Use image to create docker images

Now we have an image with our database in it. We may want to use it once or many times.

Keep changing the name and the port
I found that –name needs to come first

docker run –name sqltestco1 -d -p 1434:1433 sqltestco:sql2017
docker run –name sqltestco2 -d -p 1435:1433 sqltestco:sql2017

Now have 3 instances of SQL running.

As needed:

docker start
docker stop
docker rm

 

Push images to Docker Hub

Use:
docker images
docker tag <imageid>:<tagname> <dockerId>/<repository>:<tagname>
docker login (good idea to spell everything correctly)
docker push <dockerId>/<repository>:<tagname>

Took a few attempts – got there. Private repository.

 

 

Troubleshooting

Seem to get a conflict if other containers exist but are stopped. Possibly good practice to be aware of all containers with:

docker ps -a

Error response from daemon: … userland proxy

Error response from daemon: driver failed programming external connectivity on endpoint … Error starting userland proxy: mkdir /port…

Restart docker – fixed it for me.

 

See also

Pluralsight : Getting started with Docker on Windows

https://app.pluralsight.com/library/courses/docker-windows-getting-started/table-of-contents

 

 

 

Upgrade TFS 2010 to 2015 on-premises

Was concerned this was going to be hard work, but turned out better than expected.

Really brief notes:

Install

TFS Server 2015 will install SQL Server 2014 standard

 

Process template upgrades

If the TFS work items intranet console does not work then:

  1. https://msdn.microsoft.com/library/vs/alm/work/customize/add-features-manually#update-processconfig
  2. Download process template
  3. I created .bat files to practice with a less significant project, then moved to main projects. Sample script below. Must be ran as administrator. May be remote to the TFS Server.
    Had to repeat for various work item types

 

cd C:\
set root="%programfiles(x86)%\Microsoft Visual Studio 14.0\Common7\IDE"
cd %root%
pause
witadmin importwitd /collection:http://<server>:8080/tfs/DefaultCollection /p:"<project>" /f:"D:\Users\<user>\Documents\Visual Studio 2015\VS-TFS Process Templates\Agile\WorkItem Tracking\TypeDefinitions\UserStory.xml"
echo Done
pause

 

 

 

 

Create an ODBC using a reg edit file

With thanks to:

https://kencenerelli.wordpress.com/2012/07/26/export-an-odbc-data-source-from-the-registry/

Save following as a file e.g. MyODBCName.reg
Double-click to install into the registry


Windows Registry Editor Version 5.00

[HKEY_CURRENT_USER\Software\ODBC\ODBC.INI\ODBC Data Sources]
"MyODBCName"="SQL Server Native Client 11.0"

[HKEY_CURRENT_USER\SOFTWARE\ODBC\ODBC.INI\MyODBCName]
"Driver"="C:\\Windows\\SysWOW64\\sqlncli11.dll"
"Server"="MyServerName"
"Database"="MyDatabaseName"
"LastUser"="MyLastUserName"

Logging in .Net and My.Application.Log.WriteEntry does not write until flush

In app.config you need to remove the default listener:

<remove name=Default/>

Documentation on MSDN is ok, when testing you need to remember that the log will not write until either the application is closed, or the log is flushed. So:

My.Application.Log.DefaultFileLogWriter.AutoFlush = True
My.Application.Log.DefaultFileLogWriter.Flush()
My.Application.Log.TraceSource.Flush()

 

 

SQL Server Agent Job Verbose Logging

Please see useful page:

https://www.mssqltips.com/sqlservertip/1411/verbose-sql-server-agent-logging/

  1. In Job Step – Advanced – Output file
  2. Likely strategy is that on first step do not tick “Append output to existing file”
  3. On subsequent steps tick this and then you will get the full output of the series

 

 

Bug: Source not found, but some or all event logs could not be searched.

Bug:

The source was not found, but some or all event logs could not be searched. To create the source, you need permission to read all event logs to make sure that the new source name is unique. In accessible logs:Security

This can occur if a .Net application attempts to create the source at runtime, but not as an administrator. It is better to perhaps create the source ahead of time as an administrator in regedit, then just use the source in the application.

Private _eventLog As New EventLog(“Application”)
‘EventLog.CreateEventSource(SourceName, LogName, MachineName)

In registry at:
HKEY_LOCAL_MACHINE\SYSTEM\CurrentControlSet\Services\EventLog\Application\
Add permission to Authenticated Users = Full Control

Or add to the specific key.

With thanks to:
https://sandroaspbiztalkblog.wordpress.com/2012/02/16/the-source-was-not-found-but-some-or-all-event-logs-could-not-be-searched-inaccessible-logs-security/