clearmeasure / aliasql Goto Github PK

Scenario: Multiple databases being part of the same application and some of them having procedures/views that reference the other databases. Schema objects being in the update folder and procedures/views being in the RunAlways/Everytime folders.

Problem: Procedures/Views from one database might be getting executed before the schema objects exist that they depend on.

I can currently work around this by duplicating the folder structure for each database, but it would be nice if I could just execute the Update scripts for each db, then run all the RunAlways/Everytime scripts for each one Update has been ran on each one.

If you have any other suggestions(aside from only using one database, this is out of my control) I'm open to suggestions.

When we execute it the process remains in the server so we have to kill it manually through the SQL Management console

The diff script is not working with PowerShell v2. May be it is a good idea to fix it or just update the readme file with some requrements.

My application requires us to have "MultiSubnetFailover=True" in the connection string. Currently there is no way to accomplish this. If we could manually specify the connection string, or specify the MultiSubnetFailover property, then this would be resolved.

When running queries that take a long time (e.g. creating an index on a large table) we are getting timeouts:

Failure: The transaction associated with the current connection has completed but has not been disposed. The transaction must be disposed before the connection can be used to execute SQL statements.
Erroring script was run in a transaction and was rolled back.

When running the same query using --NOTRANSACTION it does not time out. This leads me to believe that the TransactionScope is timing out. Further reading revealed that TransactionScope has a hidden timeout setting of 1 minute (which corresponds to the timeouts that we are seeing).

I noticed that you increased the SqlCommand timeout to infinite in an earlier change. Would it be prudent to do the same for TransactionScope?

The following code path is hit when you have invalid arguments.
if ((args.Length != 4 && args.Length != 6) || requestedDatabaseAction==RequestedDatabaseAction.Default)
{
InvalidArguments();
return;
}

Installing AliaSQL.Core 1.4.2.0318.
Package AliaSQL.Core 1.4.2.318 is not compatible with netcoreapp1.1 (.NETCoreApp,Version=v1.1). Package AliaSQL.Core 1.4.2.318 supports: net45 (.NETFramework,Version=v4.5)
One or more packages are incompatible with .NETCoreApp,Version=v1.1.
Package restore failed. Rolling back package changes for 'Monli.STS.Data'.
Time Elapsed: 00:00:00.8883606
========== Finished ==========

Dealing with a migration for a table having several million rows at the moment and things seem to break down, it dies with this:

Failure: Timeout expired.  The timeout period elapsed prior to completion of the operation or the server is not responding.
Erroring script was run in a transaction and was rolled back.
Failure: The wait operation timed out

Is timeout something adjustable, or is there a recommended approach to dealing with this sort of case?

I'm currently having an issue with the Everytime scripts folder when I do a rebuild of the database. The scripts that get executed in order are:

1. Create
2. Everytime
3. Update
4. Everytime

My problem is that the Everytime scripts rely on schemas and tables that have been created in the Update scripts, so when calling rebuild, when the Everytime scripts are called after the Create scripts, they fail and therefore the rebuild fails.

I've had a look at the code and I think this is an easy fix. In DatabaseActionResolver, a Create request returns Create and Update, and a Rebuild request returns Drop, Create and Update. Therefore in DatabaseCreator it is unnecessary to execute the Everytime scripts as they will be executed in DatabaseUpdater. It would just be a case of deleting lines 30 and 31.

If you want me to submit a PR for this change then I'm happy to.

Sometimes you need to do migration tasks that are really hard to do in SQL.

I started out thinking I would write a totally new tool that would work like AliaSQL, but uses powershell scripts instead of SQL. But then I decided that would be silly and I should just add powershell support to the existing tool instead.

End result is the folders would contain a mixture of both .sql and .ps1 files

For my particular use case of this is I need to make a lot of Active Directory queries to populate some newly added db columns. I know you can technically do AD stuff with pure SQL, but it's not the friendliest. I don't have permissions to get the linked servers and other configuration stuff to make it work regardless. And Corp isn't keen on giving it to us either.

So I wrote up my migration as a console app, but now I have to manually execute it after AliaSQL. This totally messes with my one click deploy zen. Plus I have to write instructions to IT to perform the new step, etc. It'd be super handy if AliaSQL would just execute it for me like any other migration.

Would you guys accept such a pull request? Assuming the quality it there of course. I'm asking about the idea in general.

Firstly, great job everyone with this amazing package.

When running Rebuild against an Azure SQL database Aliasql fails due to Master.dbo.SYSPROCESSES being an invalid object on Azure databases.

"Dropping connections for database xyz
Failure: Invalid object name 'Master.dbo.SYSPROCESSES'.
Erroring script was not run in a transaction and may be partially committed."

Looking through the source code it seems that DatabaseConnectionDropper runs the DropConnections.sql file which contains the SQL statements Azure does not like.

What is everyone's thoughts on this?

When we execute TestData command, it runs scripts of TestData folder fine. However, it is not logging into database table: [usd_AppliedDatabaseTestDataScript].
This functionality was working fine for AliaSQL.exe v1.4.0.1208

Create action executes scripts from Update folder. According to the documentation this should not happen.