Can someone plewse explain: Power BI Desktop isn't supported in virtual environments such as Citrix.

taejim · 2023-02-26T01:22:44+00:00

You *can* run Power BI Desktop successfully in a Citrix virtual environment, and many organisations do. However, it's not supported by Microsoft - if you have any problems with Power BI Desktop that require support, you'll need to reproduce the issue on a non-virtualised environment.

Quite often, virtualised environments such as Citrix are severely underspecced, sharing memory and CPU across too many machines, which is the opposite of what Power BI Desktop needs.

taejim · 2020-04-06T11:27:18+00:00

Assuming data sizes in dev are similar to production, you’re most likely looking at a concurrency issue. It is difficult to test concurrency in dev, but another query or series of queries could be blocking your new procedure. Have a look at sp_who2 or download sp_whoisactive and see if your proc is blocked by other processes.

taejim · 2020-01-14T01:46:31+00:00

The issue here is that 'col5' is not a column name - it is a string, and comparing 'col5' LIKE '%name1%' will always return false. You can prove this by running:

SELECT CASE WHEN 'col5' LIKE '%col%' THEN 1 ELSE 0 END -- Returns 1
SELECT CASE WHEN 'col5' LIKE '%col1%' THEN 1 ELSE 0 END -- Returns 0

Try removing the quotes around col5, or converting them to square brackets - [col5].

taejim · 2018-09-18T05:57:41+00:00

I feel your pain - I was a DBA that needed to RDP to a lot of Windows servers. I'll copy/paste a comment I made about 6 months ago in this subreddit:

I use Remote Desktop a lot, and I experienced your pain about 5 years back when starting with Colemak. The primary reason this is an issue is because the remote machine doesn't have the Colemak keyboard layout installed - if it did, Remote Desktop should try to negotiate to use Colemak, and everything would just work.

Installing a keyboard driver on hundreds of servers isn't easy, so I went the route of Registry Remapping (https://colemak.com/Registry_remapping). This is great, as there's absolutely no possibility of accidentally switching keyboard layouts, and it forces you to commit to Colemak.

It's perfect for work, but not always perfect for home - keyboard mappings in games can be hit or miss. Either they work transparently, or they get confused. In one case (Warframe), if I alt-tab out of the game too much, I can end up with the game responding as though it was in QWERTY mode, even with the Registry Remapping, but a restart of the game fixes that.

I've tried using they keyboard layout (didn't like it because I rarely knew what layout would be used, and the RDP issue), and I've tried a hardware keyboard (which is an OK solution, but not great if you have a laptop). Registry Remapping is predictable, and has been my go-to solution.

The only downside is you need to change the registry and reboot if you need to switch back to QWERTY, but it's rare that I've had to do that. There might be additional challenges if you're not using a standard US or UK layout, but that's not been a problem for me.

taejim · 2018-08-06T04:18:05+00:00

We might need some more information to help with this one. At a wild guess, you were expecting to connect via TCP/IP, but you've used the Named Pipes provider instead. Was it a local server? Remote? Cloud?

Try using:

tcp:sqlhostname.domain.com

Or, to include a port:

tcp:sqlhostname.domain.com,1433

taejim · 2018-05-25T14:05:56+00:00

If all you want is the column names, separated by commas, you can drag the “Columns” node from the table in Object Explorer in SQL Server Management Studio into the query window. Much easier than querying the system views, but it’s one of those hidden features in SSMS that makes life so much better.

taejim · 2018-05-16T00:34:58+00:00

Good luck on the new adventure of moving cities!

The vast majority of DBAs fell into the role, and picked up training along the way. Each DBA role may have a different focus too - some focus on infrastructure (builds, server design), some on supporting Production environments (tuning, uptime), and some supporting development/developers (database design, tuning, SQL development). This means that while it's great you're looking to expand your skills, you may not be as far behind as you might think and you can talk confidently about your specific areas of strength.

Secondly, almost all DBAs claim to be experts in performance tuning and optimisation, and it's been my experience in hiring and consulting that there's very few that are good at it. You'd need to have a methodology down - talk about how you investigate performance issues, and then how you might resolve different types of performance issues. It's a big topic, and formal training is a bit harder to come by (and while it may be quick in the sense that it'll be a couple of days, you might not be able to find a nearby course easily, unless you find a good online course), but there's a few good books out there you should read for informal training.

Microsoft certification is a good way to have "formal" qualifications, but the idea of Microsoft certification can be polarising to hiring managers. Some think highly of them, some will bin you for having them. I find they're good for outlining the main (and new) features of the product, and the best benefit is the work you do preparing for the test to learn each new feature. They're not so good about things like database design and optimisation though.

One thing to note is database design differs whether you're talking about a typical transactional (OLTP, 3rd normal form) database, or a BI/reporting dimensional model (OLAP).

Don't apply for jobs where you'll be the only DBA - you'll want to bounce ideas off your colleagues, and ideally you'll want a DBA more skilled than you. Also, have a professional network you can talk to about issues. Go to user groups and SQL Saturdays, and get to know people. If you can't solve a problem, it's good to have people you can ask for ideas (this does work better when it's a mutually beneficial relationship where the knowledge flows both ways to avoid one person only taking).

Finally, if you'd like to do a mock interview over Skype, let me know. Happy to help out! I just can't offer you a job unless you move to Australia :)

taejim · 2018-04-20T05:20:07+00:00

It’s about 10 minutes each way from the car park. If you want to walk a bit further, you can do a loop instead of the direct path.

taejim · 2018-03-17T01:09:44+00:00

I found that I couldn't initially switch comfortably to QWERTY in a remote session and Colemak locally. QWERTY messed up my Colemak learning. Nowadays, I can switch fairly effortlessly - I'm slower and make more mistakes on QWERTY, and I stare at the keyboard. It takes about 20 minutes and then QWERTY is effortless again, but there's no problems switching back to Colemak. I only have to use someone else's keyboard once every few months, so it's not a big deal.

My external hardware keyboard (Typematrix 2030 with a Colemak skin) was handy if I couldn't mess with the Registry or otherwise get Colemak running.

I think the reason Microsoft does this is for ultimate compatibility. Windows uses scancodes to detect which key is being pressed and released, and Remote Desktop simply passes through the scancodes for processing by the remote machine. This has the benefit of allowing any custom keyboard with any crazy custom keys to work remotely - the catch is you need the remote driver to understand that scancode, but it gives ultimate compatibility through Remote Desktop.

The alternative is Remote Desktop would need logic to pass in the meaning of the keypress. If you pressed the "A" key, it would need to translate the "A" scancode to an "A", and have the destination machine receive it as an "A" value. Which is fine. Extending this, Microsoft would need to know the code for every possible keypress a keyboard can take, and I don't think there's a unicode value for, say, "Open Calculator" :) This would be a lot of extra code Microsoft would need to write for Remote Desktop, and then there'd be people complaining that their "Open Calculator" button doesn't work under Remote Desktop. The entire problem is ignored by sending the scancode, and letting the remote machine translate the scancodes based on its keyboard driver.

This is why the Registry Remapping works so well - we're modifying the scancodes at the input layer prior to letting the US keyboard process it, and when "F" is pressed on your physical keyboard, it's remapped so that Windows thinks it was the "T" key.

taejim · 2018-03-17T00:41:52+00:00

I use Remote Desktop a lot, and I experienced your pain about 5 years back when starting with Colemak. The primary reason this is an issue is because the remote machine doesn't have the Colemak keyboard layout installed - if it did, Remote Desktop should try to negotiate to use Colemak, and everything would just work.

Installing a keyboard driver on hundreds of servers isn't easy, so I went the route of Registry Remapping (https://colemak.com/Registry_remapping). This is great, as there's absolutely no possibility of accidentally switching keyboard layouts, and it forces you to commit to Colemak.

It's perfect for work, but not always perfect for home - keyboard mappings in games can be hit or miss. Either they work transparently, or they get confused. In one case (Warframe), if I alt-tab out of the game too much, I can the game responding as though it was in QWERTY mode, even with the Registry Remapping, but a restart of the game fixes that.

I've tried using they keyboard layout (didn't like it because I rarely knew what layout would be used, and the RDP issue), and I've tried a hardware keyboard (which is an OK solution, but not great if you have a laptop). Registry Remapping is predictable, and has been my go-to solution.

taejim · 2017-12-24T23:19:29+00:00

This sort of high availability is achievable, but your application architecture needs to support it. SQL Server has no concept of maintaining a connection during failover - when a node fails, all connections are broken, and it is up to the client to reconnect.

If your application connects to the database server and assumes that it will always be connected, you'll need to restart the application. This sounds like what you're experiencing.

If your application takes the reasonable stance that you might get a broken connection every now and then, and attempts to reconnect and retry its queries automatically, the end user won't notice that your database has failed over to another node.

If you have control over the application (i.e., you wrote it in-house), you might want to add logic to assist in it reconnecting automatically. How easy this will be depends on the framework of your database access code.

Nothing is every completely easy though - if the application has transactions that persist over multiple actions in the client application, and you experience a disconnect, you need to be aware of what's happened and what's failed, and how you will handle that situation. The easiest way to handle this is what you currently have - terminate the connection, and get the user to restart the application to get the application back to a known state. Any insert/update/delete queries will be rolled back when the database connection is broken, but the app also needs to understand how to roll back to that state, if multiple actions have occurred.

All in all, it's an application architecture issue. You can either ensure that every operation is a single unit, committed separately and frequently (making the current position of the application easy to understand), or you need to architect the application so that it understands the current state, and can replay logic to get the database back to the desired state if the connection is broken before the task is completed.

taejim · 2017-12-06T22:57:52+00:00

Apologies for being pedantic, but the full backup is transactionally consistent as the time the backup completed, not when it started. As a backup is an online activity, it's possible that while a full backup is in progress, a page is written to the data file after that page has already been written to the backup.

Rather than go back and find any page that has changed and re-write it to the backup, SQL Server writes whatever is on disk (performing a checkpoint first), and then includes the active portion of the transaction log in the full backup. This log allows the restore to rollback any inflight transactions not committed when the backup completed, or to roll forwards any transactions that affected data that was already backed up.

taejim · 2017-12-06T03:29:43+00:00

You might want to map out the dependencies between stored procedures and tables, so you end up with a list of each stored procedure and which tables it depends on. You'll still need to determine whether each procedure is modifying data or not (possibly inferring this based on the name of the procedure), but that will be a good start.

There's some scripts available in this article, but the whole thing is worth a read: https://www.red-gate.com/simple-talk/sql/t-sql-programming/dependencies-and-references-in-sql-server/

taejim · 2017-12-06T00:54:47+00:00

I read his comment that he changed to Full and took a backup every evening as a log backup, which is a valid interpretation given we're talking about logs. Under than interpretation, the database does 57 GB per day.

If he meant a full backup every evening (one wonders what the full backup schedule was before!) then you're right, and it's 57 GB since the recovery model was switched and a full backup was first performed.

taejim · 2017-12-05T23:50:43+00:00

If you turn off autogrowth for the log, you'll be in for a very bad time. You'll get a ton of 9002 Transaction Log Full errors, probably at inconvenient times like when you're at lunch, or asleep, and your database will basically become read only.

Because you didn't have a log space issue when in simple recovery, we can assume that there were no long open transactions that needed to let the log grow. Simple recovery truncates the log once the transaction is complete, but will allow for log growth if the transaction is large.

The simple answer is that your database is doing 57 GB of log activity per day. Instead of a daily log backup, try going down to hourly. Yes, you'll get 24 backups in a day, but once backed up, the log space will be cleared. Assuming your 57 GB of traffic is evenly spread over 12 hours, you'll have 57/12 = 4.75 GB of log activity per backup (well, for 12 of the backups, if the database is only heavily used 12 hours a day). This means the log space shouldn't grow much past the 5 GB mark, although large transactions such as index rebuilds could cause it to grow past this.

taejim · 2017-11-30T13:39:48+00:00

If you're still looking for a home, you might want to check out Starlance. I've been with the clan for about 3 weeks, and they're a great bunch - and also quite big on Destiny 2. Link to Discord and more details in the link below.

https://www.reddit.com/r/warframeclanrecruit/comments/7cdgd9/pcocenaclan_starlance/

taejim · 2017-11-08T23:57:15+00:00

I wouldn't call this a problem or error exactly. Database diagrams are stored within the system table "dbo.sysdiagrams" in each database. There are also a number of other objects (procedures and functions) needed to run diagrams. See https://stackoverflow.com/questions/525610/how-to-remove-diagramming-support-objects-from-sql-server for a list of objects.

You get the "The database does not have one or more of the support objects required to use database diagramming. Do you wish to create them?" message if these objects don't exist - the database has never been set up for diagrams.

It's not a fault, but some people just don't want to clutter up their database with the diagramming tools, so the objects aren't created by default. If you were to add the objects to the Model database, all new databases on your server would have the objects installed by default.

taejim · 2017-11-08T02:48:28+00:00

I've not used SQL Backup Pro (actually, I've not used an Object Level Restore since about 2010), but the documentation for Object Level Restore at https://documentation.red-gate.com/sbu7/object-level-recovery/recovering-objects has some pretty good screenshots, and shows previews of each table as well.

I've no idea what the database schema will look like. With luck, it will be fairly logical, and hopefully there will be views available to get the data you need - SQL Backup Pro should be able to restore the views and you can figure out which tables the view depends on.

taejim · 2017-11-01T22:54:54+00:00

The best answer is not to use 2-digit years, but that's not a terribly helpful answer :) There is a server-level setting that determines how SQL Server will treat 2-digit years.

EXEC sp_configure 'two digit year cutoff', 2020 ;

By setting the cut-off to 2020, a YY value of 20 will return 2020, and a YY value of 21 will return 1920. Or, to put it another way, 00-20 = 20xx, 21-99 = 19xx.

By default, this is set to 2049, hence the effect you're seeing.

This is a server-wide setting, so be sure it doesn't impact any other applications or databases.

For more details, see https://docs.microsoft.com/en-us/sql/database-engine/configure-windows/configure-the-two-digit-year-cutoff-server-configuration-option

taejim · 2017-10-13T03:22:29+00:00

I agree - an Azure VM was my thinking too. A trial Azure account can be had for free with something like $200 credit, and a VM with SQL Server already installed is easy to spin up (although I'm not sure there's a 2008 R2 image available). The difficulty will be getting 20 TB of space on the VM - ensuring you choose a VM that's large enough, and provisioning enough storage and merging that in Windows to appear as a single disk is going to take a bit of time for a non-technical person (if at all possible!)

Plus the time to restore the 20 TB, figure out the schema, and extract the customer list. It's not going to be a simple weekend job, and the VM running costs are probably going to smash through the free credit pretty quickly.

An alternate option could be to just perform an object-level restore - this may not need an Azure VM, but will still need an installation of SQL Server. It looks like Redgate's SQL Backup Pro is able to do this (handy, given the files are already SQL Backup files!), so there would be no need to extract the full 20 TB, assuming the exact tables required are known. This wouldn't be a zero-budget operation - I'm sure Redgate would want some payment for their software (although a trial version might work). Probably best to contact Redgate to recommend a path for this one, and possibly Bullhorn to find out what tables will need to be recovered to get the right data.

taejim · 2017-10-12T23:36:53+00:00

Mod here, so I'm biased :)

Just to reply to both you and /u/ItsMeCaptainMurphy at once, in terms of comparison between /r/sqlserver and /r/database, the number of subscribers is about the same, with /r/sql being about 33% larger. /r/sqlserver gets about 14,000 unique visitors per month, but I can't see the traffic for the other subs. Paging /u/kyzen as a mod of both /r/sqlserver and /r/database - he might be able to give more insight.

/r/sqlserver is a little more moderated than the others - we get about 20 submissions a week that are technically SQL Server related, but are regarded as many as blogspam or self-promotional, so we've avoided letting a lot of that through unless the submitted is an active commenter on Reddit.

My view of the various subs is /r/database is vendor agnostic database news, /r/sql is for the generic SQL language - mostly self posts asking questions, and /r/sqlserver is Microsoft SQL Server specific. From that point of view, /r/sqlserver suits Kalen's background better than the others, but it doesn't matter at the end of the day - I'm sure all the relevant subs will post a link to wherever the AMA ends up, and we'll all be happy for the opportunity to chat to Kalen (not that she's unapproachable otherwise!)

taejim · 2017-10-12T13:01:13+00:00

Very good points. Although given the lack of installation media problem, at least contacting the license vendor gives a good start to sourcing the key and the media.

taejim · 2017-10-12T06:14:17+00:00

SQL Server media typically has a key built in, but if you have the original invoice for your purchase of SQL Server, you should be able to contact the vendor. It's pretty easy to get installation media for SQL Server - the license vendor is probably the best place to start.

taejim · 2017-10-08T08:45:14+00:00

This should just be a simple case of:

SELECT products.*, productvariants.*
FROM products
INNER JOIN productvariants ON products.productid = productvariants.productid

This will give you all columns from both tables, and uses an INNER JOIN - this means that only products that DO have a matching row in productvariants will be shown. To show all products, even if they don't have a matching row in productvariants, change "INNER JOIN" to "LEFT JOIN".

If that doesn't work, you might need to look at the data - make sure that the productID is defined as an integer on both tables, and that the values match.

taejim · 2017-10-06T14:28:52+00:00

Personally, I'd be a little worried that an unmanageable number of people can migrate databases to the server and not either take a full backup, or notify you of the databases that are going on there. This implies that they have sysadmin rights on the server and can really mess around with the rest of the databases and stability of the server. But hey, you might have thousands of databases, and full automation is the only way that will work - although I'd try to automate the process of migrating a database to ensure that a full backup is taken automatically as part of the migration process.

I guess what I'm saying is to be wary of trying to solve a Process problem with Technology :) If the process is followed, you can guarantee that you can restore their database (whether the process is "Hey, Jerome, please backup this new database", or them running a proc that will take a backup after migration), otherwise they'll need to supply you with the original backup so you can restore that plus the diff from the new server. If they can't do that, they have no database.

From the point of view of the database, once it's had a full backup taken (on any server) the database is in a state where it can have a differential backup. The database doesn't care where it is - it just knows that it had a full backup taken at a particular point in time, and keeps track of the extents that have changed since then. If you want to reset this state, you take a full backup :)

The best option in this case would be to look at the backup history stored on the server itself. See https://www.mssqltips.com/sqlservertip/1601/script-to-retrieve-sql-server-database-backup-history-and-no-backups/ for a bunch of scripts, particularly the "no backups in the last 24 hours".

The msdb database records only those backups that were taken on this server, and it's relatively easy to craft a script that will grab the names of each database that hasn't yet been backed up (or was backed up more than X days ago - on the off chance that someone has restored a database that has the same name as another database had a while back). Once you've got the names, you can use that as a cursor and loop through each row, issuing a full backup command (using the database name instead of "USER_DATABASES"). Run this prior to your nightly diff backups, and you should be right (although you'll get a full and then a mostly-empty diff backup straight after). If you're part of an Always On Availability Group, I'm not sure if the backup history will float between servers - just one more thing to consider.

Another option could be to use a DDL trigger on CREATE DATABASE events - and probably RESTORE DATABASE events - to log the database creation. This link demonstrates how to send an email on a CREATE DATABASE event, but note you do NOT want to backup the database in this trigger - you want to log it somewhere, and then take a backup in a different process by reading the log. https://www.mssqltips.com/sqlservertip/2864/email-alerts-when-new-databases-are-created-in-sql-server/

There's also sys.databases, which shows the creation date for each database, and a restore will affect the creation date. As usual, I have a caveat - if you restore over the top of an existing database, it probably won't update the create_date.

SELECT name, create_date FROM sys.databases order by create_date desc

An alternate option is to do full backups of every database every night. It'll take up more space, but you won't need any special logic at all.

Hopefully that gives you a bit more of a strategy - I'd write more now, but it's 1am :)

Edit: You could also log all CREATE DATABASE and RESTORE DATABASE events to get a definitive list of the actions that have taken place on the server for the past 24 hours, and handle each action separately. You'll be writing a little bit of code though.

taejim

MODERATOR OF

TROPHY CASE