Showing posts with label department. Show all posts
Showing posts with label department. Show all posts

Thursday, March 8, 2012

Continuous Merge Replication questions

Ok excuse my lack of knowledge in this department and hopefully someone
can help easily.
When using Merge replication with continuous updating subscribers, it
appears when there is a network outage (as everywhere will get sooner or
later) that it will not try to update until I manually push synchronize.
So that sort of makes it appear useless in my eyes.
Can someone tell me how I can avoid this problem. The only way I have
been able to get around is by setting the subscriber to update every
minute but this is not ideal.
Can the calls to publish and synchronize be coded? if so please help.
And the other query I have is what is the difference between continuous
updating subscribers and transactional replication? Am I missing something?
Cheers,
Tim
You could hardcode the jobsteps to work in a loop (step 3 -> step 2) to
have it automatically restart.
For subscriber updates, merge is similar to transactional replication with
queued updating subscribers. A few differences...
TR works on a transaction basis while merge works on changed data ie 1000
updates to a row will be 1000 stored proc calls for tr while 1 updated row
in merge.
Text and Image changes are treated differently.
Requirement for PK in TR.
More conflict resolvers in merge than queued updating subscribers.
Cheers,
Paul Ibison SQL Server MVP, www.replicationanswers.com
(recommended sql server 2000 replication book:
http://www.nwsu.com/0974973602p.html)

Wednesday, March 7, 2012

Continued DTS support with what SQL Server Version/Edition

We currently have access to another department's SQL server. However they are taking that server offline within a few months, and we will need to migrate our data and DTS packages to a new solution before then. We have about 30 databases and 20 somewhat simple DTS packages, but only a few users need access. Funding is very tight.

SQL 2005's support of DTS is somewhat of a mystery to me. Docs state with 2000 Enterprise client tools installed + Express edition, that I can run DTS packages on Express edition. But the docs do not state the procedure for doing so, and I cannot register the Express server in the Enterprise manager(states must be using 2005 version of Enterprise manager). It seems I can Design a DTS package that resides on the 2000 server and specify a connection to the Express server, but the DTS package would not run, stating always that the table was in use, and I haven't explored it any further. May have just been a quirk.

I'm trying to conquer this in two pieces:

    Cheapest solution for graphical design of DTS packages, or some techniology similar(I think I understand that this is called SSIS in 2005?). This would be needed by 1-4 users.

      SQL 2005 Developer Edition for each user, only about $50 per user.

      MSDE for each user, but can this house DTS packages and be connected to by enterprise manager?

    Cheapest solution for data access and ability to run DTS packages by 2-10 users.

      If SQL Express Edition is used to house data, can the DTS packages be distributed in some way that allows the users to run them? I.E. exported as VB code or some sort of file that can be loaded by an Access 2002 or VB.NET frontend.

      Can all users have Developer Edition or Desktop edition and run the DTS package from it, but have the DTS package connect to the Express Edition server where the data is housed? Or would this be a violation of what the Developer Edition is intended to be used for?

      Any other suggested scenarios are welcome.

Are we allowed to continue using the 2000 Enterprise manager after we no longer have access to a licensed SQL 2000 Enterprise server?

The feature comparison for the different 2005 editions doesn't mention DTS, but I know it works for some editions if the add on components are installed. I'm just not sure which versions support this.

1. For development, SQL 2005 dev edition includes sql server 2005 database engine. No msde is required. The server will persist DTS 2000 packages. You cannot connect via 2000 tools, but you can via included 2005 tools. For editing DTS 2000 packages, you need the DTS Designer download, located on msdn as part of the sql 2005 feature pack.

2. The DTS 2000 object model is free. It can be redistributed from the 2000 install disk, or is also available as part of the backward compatibility redist in the feature pack. Once installed on a machine, DTS packages may be run from the command line or from the agent, loading packages resident on 2000 or 2005 servers, or on the file system.

You don't have a need for 2000 enterprise manager here, as your 2005 dev edition will suffice for development work. Howvever, if you want to save your production packages on sql server you will need to attend to licensing for that server. I think msde or express will work, but am not sure. As an alternative, you can save your packages on the file system. There they can be edited by 2000 or 2005 tools.

Friday, February 10, 2012

consolidating member

Is it possible to add an extra member that consolidates all other members. For example, I'm looking at totals by department and account. I need an extra department that sums by account across all other departments. Like this:

Dept A

Account 1 $10

Account 2 $12

Dept B

Account 1 $15

Account 2 $11

Consolidated(A + B)

Account 1 $25

Account 2 $23

Assuming that Account and Department are separate dimensions, doesn't the "All" member, at the root of the Department hierarchy, fulfill this requirement - if not, could you explain why in some more detail?|||Yes, the "all" member does contain the data I need. My problem is in generating the final printed report. I guess I need to think about solving the problem with the reporting tool. I'm using reporting services. When I get to the end of a department, I need to display data from the "all" member which consolidates all sub departments. Then, when I get to the end of all departments, I need to display consolidating data for all departments.|||

Reporting Services does indeed present problems when dealing with an "All" member. This MSDN paper - though based on SQL Server 2000 - provides some guidance :

http://msdn.microsoft.com/library/default.asp?url=/library/en-us/dnsql2k/html/olapasandrs.asp?frame=true

>>

Integrating Analysis Services with Reporting Services

Sean Boon
Microsoft Corporation

June 2004

Applies to:
Microsoft SQL Server 2000

Summary: Create a compelling solution for your customer that defines and manages great-looking Analysis Services reports, and quickly answers analytical questions to improve traditional reporting scenarios.

...

Another behavior of the flattening algorithm is that the "[(ALL)]" level is not included in the dataset. This has a couple of implications. The first is that if you want to include data from the "All" member of a dimension, you'll need to create a calculated member to represent this member. This can be accomplished in a couple of different ways. The first method would be to create a calculated member on the Measures dimension and for the definition of the calculated member refer to the current member's name or unique name. There are several examples of this method represented in later sections of this whitepaper.

Note The "All" level of a dimension is not included in the field set that is returned to Reporting Services.

The second implication of the "All" level not being represented in the dataset is that calculated members, usually defined without a parent member, will need to change so that they do have a parent member. This only applies in cases where the calculated member does not belong to the Measures dimension. In many cases, when calculated members are defined on a non-Measures dimension, the parent member property is left blank. This can be changed in the calculated member dialog box as shown below

...

>>